Copy/Paste from one workbook to another 2 workbooks with range

[Patriot2879]

Hi please can you help me I have a command button in Book17 where once clicked I want to copy range A1:E2 and paste into 2 other workbooks which are Book16 and Book18, I have the code below but it doesn't seem to work and the lines come up in red, please can you help me?

[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Private Sub CommandButton1_Click()[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Workbooks("C:\Users\s21375\Desktop\test2\Book17.xlsx").Sheets("Sheet1").Range("A1:E2").Copy[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Workbooks("C:\Users\s21375\Desktop\test\Book16.xlsx").Sheets("Sheet1").Range("A1:E2").Paste[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Workbooks("C:\Users\s21375\Desktop\test\Book18.xlsx").Sheets("Sheet1").Range("A1:E2").Paste[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]End Sub[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/CO[/COLOR][/SIZE][/FONT]DE]

 

[JackDanIce]

Is the destination sheet named Sheet1 in the workbook that is open?

If this gives the same error, without your files difficult to diagnose:

Private Sub CommandButton1_Click()
            
    Dim r As Range: Set r = ThisWorkbook.Sheets("Sheet1").Range("A1:E2")
    
    UpdateWorkbook "C:\Users\s21375\Desktop\test\Book16.xlsx", r
    UpdateWorkbook "C:\Users\s21375\Desktop\test3\Book18.xlsx", r
    
    Set r = Nothing
    
End Sub

Private Sub UpdateWorkbook(ByRef sBook As String, ByRef r As Range)

    Workbooks.Open sBook
    With ActiveWorkbook
        .Sheets("Sheet1").Cells(1, 1).Resize(r.Rows, r.Columns).Value = r.Value
        .Close True
    End With
    
End Sub

 

[Patriot2879]

hi yes the sheet name for book 16 and 18 is called Sheet1, I have just tried the code again with all the workbooks open then closed and still getting the same error. hope you can help.

 

[Patriot2879]

Hi I have got the code below to work but for only book18 how do I add the other book? book16? please

Private Sub CommandButton1_Click()
 
    Dim srcWB As Workbook
    Dim destWB As Workbook
    Dim fName As String
    Dim lastRow As Long
 
'   Capture current workbook as source workbook
    Set srcWB = ActiveWorkbook
 
'   Open destination workbook and capture it as destination workbook
    Workbooks.Open "C:\Users\s21375\Desktop\test3\Book18.xlsx"
    Set destWB = ActiveWorkbook
  
  
'   Copy data from source workbook to destination workbook
srcWB.Sheets("Sheet1").Range("A1:E2").Copy
    destWB.Sheets("Sheet1").Range("A1").PasteSpecial xlPasteValues
'   Save changes and close destination workbook
    destWB.Close SaveChanges:=True
End Sub

 

[JackDanIce]

If this gives the same error, without your files difficult to diagnose

It works for me when I mock some test files up. Without your files, I can't determine the cause of error.

 

[Patriot2879]

Hi I have managed to go get the code working in a test mock up as well, but when Iuse the code in my actual button I keep getting errors, on the sheet3, and thisis correct and all the file links are correct as well and the sheet name ‘Sheet3’but its not working I am so confused, I don’t know f it crashes with the othercoding I have? I have attached the file Idon’t know if you can see what I am missing? Test33 isthe main one with the button and I want to paste into test11 and test22.

https://www.dropbox.com/s/1habya3x1ujdiu5/test33.xlsm?dl=0


https://www.dropbox.com/s/9al2wq2tb7hd6wn/test11.xlsm?dl=0

https://www.dropbox.com/s/re4wl3eyb5zzxph/test22.xlsm?dl=0

 

[Patriot2879]

Hi I hope you can help please? I am really confused to why it is not working.

 

[JackDanIce]

The VBA project is locked so can't determine what test33.xlsm is doing nor can interact with the code to see why it doesn't open files.