Copy data from one workbook to another

[Shadkng]

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

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

 

[Dan_W]

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:

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

Hope that helps.

 

[Zot]

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.

 

[Shadkng]

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:

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:

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.

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