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

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,227
Office Version
  1. 2010
Platform
  1. Windows
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?
Code:
[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]
 
Is the destination sheet named Sheet1 in the workbook that is open?

If this gives the same error, without your files difficult to diagnose:
Rich (BB code):
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
 
Last edited:
Upvote 0

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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.
 
Upvote 0
Hi I have got the code below to work but for only book18 how do I add the other book? book16? please
Code:
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
 
Upvote 0
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.
 
Upvote 0
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









 
Upvote 0
Hi I hope you can help please? I am really confused to why it is not working.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

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