View Full Version : Is it possible to copy values from a worksheet to another worksheet using vba?
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
02-20-2012, 01:50 PM
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
Thanks, Tommy! I've learnt something new.
CatDaddy
02-21-2012, 12:49 PM
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
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
02-23-2012, 11:25 PM
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
02-24-2012, 06:34 AM
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.
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
02-27-2012, 02:17 AM
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.