Macro copy data from multi sheets to other workbook

ste33uka

Active Member
Joined
Jan 31, 2020
Messages
316
Office Version
  1. 2019
Platform
  1. Windows
Would you have a macro that would copy data from cells copy cells A70 to F130
from workbook1 sheets1,2,3,4,5,6,7,8,9,10
to workbook2 sheets 1,2,3,4,5,6,7,8,9,10.
So date from 1 goes to 1 , 2 goes to 2 etc.
My sheets are just called 1,2,3,4,5,6,7,8,9,10
Thanks
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

VBA Code:
Sub COPY_BOOK_1_TO_2()
    Application.ScreenUpdating = False
    With ActiveWorkbook
        For MY_SHEETS = 1 To 10
            With Sheets(MY_SHEETS)
                .Range("A70:F130").Copy
                Workbooks("Book4").Sheets(MY_SHEETS).Range("A70").PasteSpecial (xlPasteAll)
            End With
        Next MY_SHEETS
    End With
    Application.ScreenUpdating = True
End Sub

you will need to change 'Book4' to your destination spreadsheet name.

Have made some assumptions, both workbooks are open and sheet/tab name 1 is the left most tab and they go in order.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,770
Office Version
  1. 2013
Platform
  1. Windows
You can substiture the workbook names for the index numbers.
VBA Code:
Sub t()
For i = 1 To 10
  Workbooks(1).Sheets(CStr(i)).Range("A70:F130"),Copy Workbooks(2).Sheets(Cstr(i)).Range("A1")
Next
End Sub

If you want to copy to the same range of cells, why not just do a saveas to duplicate the source workbook?
 
Last edited:

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,770
Office Version
  1. 2013
Platform
  1. Windows
Happy to assist,
regards, JLG
 

Watch MrExcel Video

Forum statistics

Threads
1,119,106
Messages
5,576,144
Members
412,700
Latest member
IIII
Top