Copy a range from each sheet in one workbook to another

yinkajewole

Active Member
Joined
Nov 23, 2018
Messages
281
I would like to copy the data from B1 to B5 of each sheet of a workbook to each sheet of another workbook maintaining the same B1 to B5
 
Last edited:

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Try this:
You did not provide WorkBook names so you will need to modify them to your needs

I'm assuming WorkBook2 has at least as many sheets as WorkBook1

Both Workbooks must be open

Run this script from WorkBook1

Code:
Sub Copy_Range()
'Modified  6/23/2019  11:30:12 PM  EDT
Application.ScreenUpdating = False
Dim i As Long
For i = 1 To Workbooks("Book1").Sheets.Count
    Workbooks("Book1").Sheets(i).Range("B1:B5").Copy Workbooks("Book2").Sheets(i).Range("B1")
Next
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Try this:
You did not provide WorkBook names so you will need to modify them to your needs

I'm assuming WorkBook2 has at least as many sheets as WorkBook1

Both Workbooks must be open

Run this script from WorkBook1

Code:
Sub Copy_Range()
'Modified  6/23/2019  11:30:12 PM  EDT
Application.ScreenUpdating = False
Dim i As Long
For i = 1 To Workbooks("Book1").Sheets.Count
    Workbooks("Book1").Sheets(i).Range("B1:B5").Copy Workbooks("Book2").Sheets(i).Range("B1")
Next
Application.ScreenUpdating = True
End Sub

When I substituted Book1 and Book2 to the names of my opened workbooks. It brings error "Copy method of Range class failed"
 
Upvote 0
I do not worry about things like this.
You need to enter something like this: Alpha.xlsm or something like that. So what sort of work around did you find?
You may not worry, I've got a workround for it
It's always easier to provide help when you provide specific details like WorkBook names.
 
Upvote 0
(Untested) You could change the .copy to = but the size of ranges may need to be the same or you could adjust the size of the target while copying to the same size as the source
 
Last edited:
Upvote 0
Found this on stack overflow (untested) will paste to cell A1

Code:
Set F_W=thisworkbook.worksheets(Name of sheet where picture is found)

Set S_W=Workbooks("Second Workbook Name").worksheets("Target Sheet Name")

Set Pic=F_W.pictures("name of picture found in the selection pane in First workbook")

Pic.copy 

S_W.range("A1").Select

S_W.paste
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,537
Messages
6,114,216
Members
448,554
Latest member
Gleisner2

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