Copy data from one workbook to another

Shadkng

Active Member
Joined
Oct 11, 2018
Messages
365
Hi, I found the below code online. My goal is to copy data from from one workbook to another without specifying the specific filenames. In this code, it seems I have to have my cursor on the target sheet when running the macro, otherwise it copies the data from the target onto itself. Is there a better way to set up the source and target method?

I also need to change it to "paste values" but I don't know how to modify.

Any help is appreciated. Thanks

VBA Code:
Sub copybook2()

Dim sourceBook As Workbook
Dim targetBook As Workbook
Dim sourceSheet As Worksheet
Dim targetSheet As Worksheet

' Must have exactly 2 workbooks open
If Workbooks.Count <> 2 Then
    MsgBox "There must be exactly 2 workbooks open to run the macro", vbCritical + vbOKOnly, "Copy Columns From Source To Target"
    Exit Sub
End If

' Set the source and target workbooks
Set targetBook = ActiveWorkbook
If Workbooks(1).Name = targetBook.Name Then
    Set sourceBook = Workbooks(2)
Else
    Set sourceBook = Workbooks(1)
End If

' Set up the sheets
Set sourceSheet = sourceBook.ActiveSheet
Set targetSheet = targetBook.ActiveSheet

' Copy the columns
sourceSheet.Range("C24:I28").Copy Destination:=targetSheet.Range("C24")

End Sub
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
In this code, it seems I have to have my cursor on the target sheet when running the macro, otherwise it copies the data from the target onto itself.
That doesn't appear to be what's happening from the code though. It defines the Targetbook/Targetsheet as the workbook/sheet on which you 'currently' have your cursor (the ActiveWorkbook), and the SourceBook/SourceSheet being the the other workbook. And seeing as it only ever copies from the sourceSheet to the targetsheet, it's not clear how it can "copy the data from the target onto itself".

As for the pastevalues question, if all you want to do is transfer the values from the source sheet to the corresponding location in the target sheet, it is quicker and easier to just assign it to that area. So instead of having to rely on the clipboard, you could just change the last line of code to:

VBA Code:
targetSheet.Range("C24:I28").Value = sourceSheet.Range("C24:I28").Value

Hope that helps.
 
Upvote 0
The code you provided needs to have both workbook opened. So it is not what you want in your statement:
copy data from from one workbook to another without specifying the specific filenames

You can have a workbook with macro that once run it will ask you to select another workbook. However, the next task would be once the target workbook opened, you want to be able to select which sheet on target workbook (unless there is only one target sheet). So, you need to have something like a pop-up to ask you to select target sheet before continue. You can do this with UserForm.
 
Upvote 0
That doesn't appear to be what's happening from the code though. It defines the Targetbook/Targetsheet as the workbook/sheet on which you 'currently' have your cursor (the ActiveWorkbook), and the SourceBook/SourceSheet being the the other workbook. And seeing as it only ever copies from the sourceSheet to the targetsheet, it's not clear how it can "copy the data from the target onto itself".

As for the pastevalues question, if all you want to do is transfer the values from the source sheet to the corresponding location in the target sheet, it is quicker and easier to just assign it to that area. So instead of having to rely on the clipboard, you could just change the last line of code to:

VBA Code:
targetSheet.Range("C24:I28").Value = sourceSheet.Range("C24:I28").Value

Hope that helps.

That doesn't appear to be what's happening from the code though. It defines the Targetbook/Targetsheet as the workbook/sheet on which you 'currently' have your cursor (the ActiveWorkbook), and the SourceBook/SourceSheet being the the other workbook. And seeing as it only ever copies from the sourceSheet to the targetsheet, it's not clear how it can "copy the data from the target onto itself".

As for the pastevalues question, if all you want to do is transfer the values from the source sheet to the corresponding location in the target sheet, it is quicker and easier to just assign it to that area. So instead of having to rely on the clipboard, you could just change the last line of code to:

VBA Code:
targetSheet.Range("C24:I28").Value = sourceSheet.Range("C24:I28").Value

Hope that helps.
I got mixed up with the cursor thing. So I switched the sourcesheet and targetsheet in that line and it worked OK. The code below works when I specify the range, but when I tried to incorporate last row, it doesn't work. It doesn't error out, it just does nothing. Is there a way to use last row of one column and also copy a range like C24:I28? Or do I need to copy each column separately. Thanks

Below is the code and I nullified the LR lines.

VBA Code:
Sub copybook3()

Dim sourceBook As Workbook
Dim targetBook As Workbook
Dim sourceSheet As Worksheet
Dim targetSheet As Worksheet

'Dim LR As Long

' Must have exactly 2 workbooks open
If Workbooks.Count <> 2 Then
    MsgBox "There must be exactly 2 workbooks open to run the macro", vbCritical + vbOKOnly, "Copy Columns From Source To Target"
    Exit Sub
End If

' Set the source and target workbooks
Set targetBook = ActiveWorkbook
If Workbooks(1).Name = targetBook.Name Then
    Set sourceBook = Workbooks(2)
Else
    Set sourceBook = Workbooks(1)
End If

' Set up the sheets
Set sourceSheet = sourceBook.ActiveSheet
Set targetSheet = targetBook.ActiveSheet

' Copy the columns
'LR = Cells(Rows.Count, "C").End(xlUp).Row
'sourceSheet.Range("C24" & LR).Value = targetSheet.Range("C24").Value
sourceSheet.Range("C24:I28").Value = targetSheet.Range("C24:I28").Value

End Sub
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,710
Members
448,293
Latest member
jin kazuya

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top