Is it possible to copy values from a worksheet to another worksheet using vba?

[wedd]

Hi experts,


is it possible to write vba code to copy and paste values from one worksheet into another worksheet? If so, how can this be done? Would you have any sample code or websites were this has been done? Also Include VBA code to add a pop up message box to confirm completion of the data transfer? I am a beginner/intermediate vba coder, so this will add to my learning...



Thanks for your contributions:friends:

[Tommy]

2 ways shown below. Keep in mind that the ActiveWorkBook is the current open workbook.


Sub CopyOneCellToAnotherOnAnotherSheet()
Dim Fws As Worksheet, Sws As Worksheet
ActiveWorkbook.Worksheets("Sheet1").Cells(1, 1) = ActiveWorkbook.Worksheets("Sheet2").Cells(2, 2)
'the same thing - different way
Set Fws = ActiveWorkbook.Worksheets("Sheet1")
Set Sws = ActiveWorkbook.Worksheets("Sheet2")
Fws.Cells(1, 1) = Sws.Cells(2, 2)
MsgBox "Finished with what I was doing!"
End Sub

[wedd]

Thanks, Tommy! I've learnt something new.

[wedd]

It works! Great! Thanks.

[CatDaddy]

The macro recorder is a great resource when you come up against something that you do not know how to code as well! For future reference

[wedd]

Hi Tommy, sorry the pop up message appeared, but the data on worksheet1 didn't copy to worksheet 2. Is their a reason why the code didn't execute the copy and paste function? Thanks, I appreciate the help!

[frank_m]

Hi wedd,

If you wish to copy paste everything on sheet1, to sheet2 try this:

Sub CopyFirstSheetToSecond()
Dim FirstRow As Long, Lastrow As Long, LastCol As Integer
Dim rng As Range, ShtOne As Worksheet, ShtTwo As Worksheet

Set ShtOne = Worksheets("Sheet1")

Set ShtTwo = Worksheets("Sheet2")

Lastrow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
LastCol = Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

FirstRow = 1 ' headings

With ShtOne
Set rng = .Range(.Cells(FirstRow, 1), .Cells(Lastrow, LastCol))
End With

rng.Copy ShtTwo.Cells(FirstRow, 1)

End Sub

[Tommy]

wedd,

I copied one cell on 1 sheet to 1 cell on another sheet, I did not copy the whole sheet as frank_m is showing.

[wedd]

Ok, Tommy! Frank_M there is an error runtime-error '91' Object variable or With Block variable not set displaying. Why is it displaying this error message and how can it be rectified? Thanks guys for your help. I am learning something new about Excel VBA. I am a beginner learning this programming language.

[frank_m]

Sorry I may have confused you by using ShtOne and ShtTwo, whenTommy used the names Fws and Sws.
I believe if you make sure you use one or the other spellings consistantly through out the code, you should eliminate that error.