Copying range from every sheet in 1 workbook to another?

[JumboCactuar]

Hi,
I have 2 very similar workbooks with same tab names.

I'm wanting to copy range (A1:S100) from every sheet in workbookA and paste at U1 in every relevant sheet in workbookB

Can do it with VBA one at a time but looking to see if it can be done in one go with a loop etc.

Any help appreciated

 

[alansidman]

The worksheet names in A and B are the same?

 

[JumboCactuar]

The worksheet names in A and B are the same?

yes identical, though each workbook has around 100 sheets

 

[JLGWhiz]

Assumes both workbooks are open at run time. Also assumes code will run from workbookA

Sub t()
Dim wb1 As Workbook, wb2 As Workbook, sh As Worksheet, rng As Range
Set wb1 = Workbooks("WorkbookA.xlsm") 'Edit file name
Set wb2 = Workbooks("WorkbookB.xlsx") 'Edit file name
    For Each sh In wb1.Sheets
        Set rng = sh.Range("A1:S100")
        rng.Copy wb2.Sheets(sh.Name).Range("U1")
    Next
End Sub

Be sure to save your host workbook as a macroenabled workbook (.xlsm) to preserve the code. Note the comments for the workbook names.

 

[JumboCactuar]

Assumes both workbooks are open at run time. Also assumes code will run from workbookA

Sub t()
Dim wb1 As Workbook, wb2 As Workbook, sh As Worksheet, rng As Range
Set wb1 = Workbooks("WorkbookA.xlsm") 'Edit file name
Set wb2 = Workbooks("WorkbookB.xlsx") 'Edit file name
    For Each sh In wb1.Sheets
        Set rng = sh.Range("A1:S100")
        rng.Copy wb2.Sheets(sh.Name).Range("U1")
    Next
End Sub

Be sure to save your host workbook as a macroenabled workbook (.xlsm) to preserve the code. Note the comments for the workbook names.

perfect thankyou it worked

 

[JLGWhiz]

perfect thankyou it worked

You're welcome,
Regards, JLG