Copy and paste same sheet from multiple workbooks to different sheets on master workbook

Excel_Newbie4980

New Member
Joined
May 6, 2021
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
Hello Everyone,

Currently, I have a report where I have to copy and paste the same worksheet name "CENTER DATA" from 10 different workbooks into a master workbook. On the master workbook, there are 10 different sheets (One named for each of the individual workbooks). All I need to do is copy the entire worksheet from each individual workbook onto their designated worksheet in the master. Is there a VBA macro that can do this automatically by utilizing a module?

Example for clarity:

Master Workbook
-Sheet 1 (Center 1)
-Sheet 2 (Center 2)
-Sheet 3 (Center 3)
and so on until Center 10.

Each individual workbook: (All workbooks are located in one folder, "C:\Users\maw04\Desktop\AM-PM REPORT
-Workbook 1 (Center 1)
-Workbook 2 (Center 2)
-Workbook 3 (Center 3)
etc...

Each workbook has the same sheet that I am copying and pasting to Master, worksheet "CENTER DATA"

Please help as this is very time consuming!
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,384
Office Version
  1. 2016
Platform
  1. Windows
A bit confused here. Each source workbook has same sheet name "CENTER DATA" or "Center 1", "Center 2" and so on?

In Master workbook each sheet is named "Center 1", "Center 2" and so on?
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,384
Office Version
  1. 2016
Platform
  1. Windows
I think I understood it now.
The Master workbook has 10 sheets named Center 1, Center 2, Center 3 until Center 10
The data source workbook is named Center 1, Center 2, Center 3 until Center 10 (It would be Center 1.xlsx and so on)
In each data source workbook, the data is on a sheet named CENTER DATA.

If that is true then the VBA code is
VBA Code:
Sub GetDataFromFilesInAFolder()

Dim FPath As String
Dim FName As Variant
Dim ws As Worksheet, wsS As Worksheet
Dim wb As Workbook, wbMaster As Workbook

Set wbMaster = ActiveWorkbook
Application.ScreenUpdating = False

FPath = "C:\Users\maw04\Desktop\AM-PM REPORT\"        ' Set your folder path here
FName = Dir(FPath)

While FName <> ""
    Set wb = Workbooks.Open(fileName:=FPath & FName, UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
    For Each ws In wb.Sheets
        If ws.Name = "CENTER DATA" Then
            ws.Cells.Copy wbMaster.Sheets(Split(FName, ".")(0)).Range("A1")
        End If
    Next
    'Close wb without saving
    wb.Close False
    'Set the fileName to the next file
    FName = Dir
Wend

End Sub
 

Oddball2020

Board Regular
Joined
May 6, 2021
Messages
69
Office Version
  1. 365
Platform
  1. Windows
Hello Everyone,

Currently, I have a report where I have to copy and paste the same worksheet name "CENTER DATA" from 10 different workbooks into a master workbook. On the master workbook, there are 10 different sheets (One named for each of the individual workbooks). All I need to do is copy the entire worksheet from each individual workbook onto their designated worksheet in the master. Is there a VBA macro that can do this automatically by utilizing a module?

Example for clarity:

Master Workbook
-Sheet 1 (Center 1)
-Sheet 2 (Center 2)
-Sheet 3 (Center 3)
and so on until Center 10.

Each individual workbook: (All workbooks are located in one folder, "C:\Users\maw04\Desktop\AM-PM REPORT
-Workbook 1 (Center 1)
-Workbook 2 (Center 2)
-Workbook 3 (Center 3)
etc...

Each workbook has the same sheet that I am copying and pasting to Master, worksheet "CENTER DATA"

Please help as this is very time consuming!
I've done something very similar in the past. I think the easiest approach is to set it up as a loop, because you're essentially doing the same thing each time.

In your master workbook, write a list of your centers somewhere:

CENTER 1
CENTER 2
CENTER 3
.....
CENTER 10

Then code the VBA as a loop to go down this list, and reference each CENTER X as the name to use in the file open, as well as the sheet name to copy into the master workbook.

No need to code a "open all files in a folder" or some such as it sounds like your CENTER workbooks are static, and won't change next to to include up to CENTER 20 or something.

Sorry I don't have the time to code it to show you at the moment, but reply if you need more help and what I said is beyond you.
 

Excel_Newbie4980

New Member
Joined
May 6, 2021
Messages
9
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

I think I understood it now.
The Master workbook has 10 sheets named Center 1, Center 2, Center 3 until Center 10
The data source workbook is named Center 1, Center 2, Center 3 until Center 10 (It would be Center 1.xlsx and so on)
In each data source workbook, the data is on a sheet named CENTER DATA.

If that is true then the VBA code is
VBA Code:
Sub GetDataFromFilesInAFolder()

Dim FPath As String
Dim FName As Variant
Dim ws As Worksheet, wsS As Worksheet
Dim wb As Workbook, wbMaster As Workbook

Set wbMaster = ActiveWorkbook
Application.ScreenUpdating = False

FPath = "C:\Users\maw04\Desktop\AM-PM REPORT\"        ' Set your folder path here
FName = Dir(FPath)

While FName <> ""
    Set wb = Workbooks.Open(fileName:=FPath & FName, UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
    For Each ws In wb.Sheets
        If ws.Name = "CENTER DATA" Then
            ws.Cells.Copy wbMaster.Sheets(Split(FName, ".")(0)).Range("A1")
        End If
    Next
    'Close wb without saving
    wb.Close False
    'Set the fileName to the next file
    FName = Dir
Wend

End Sub
Hello Zot,

Thank you for your reply! I believe your understanding of my problem is correct. I am receiving a "Run-time error'9': Subscript out of range" message when inputting the code you typed above.

I hope this will simplify by give you exact details of my my folders as I believe I am the one causing the error by not understanding exactly what to do.

Master Workbook name = 5-Mid Atlantic MASTER May.xlsm
Worksheets (in order):
 

Excel_Newbie4980

New Member
Joined
May 6, 2021
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
Hello Zot,

Thank you for your reply! I believe your understanding of my problem is correct. I am receiving a "Run-time error'9': Subscript out of range" message when inputting the code you typed above.

I hope this will simplify by give you exact details of my my folders as I believe I am the one causing the error by not understanding exactly what to do.

Master Workbook name = 5-Mid Atlantic MASTER May.xlsm
Worksheets (in order):
- MA Monthly Snapshot
- MA Daily Snapshot
- AC Donelson
- Komfort Air
- Climate Control
- Memphis
- Comfortech
- Midland
- Norrell
- Pardee
- R&M
- Raleigh

Workbooks I am copying and pasting data from "C:\Users\maw04\Desktop\AM-PM REPORT
Workbook 1 = AC Donelson
Workbook 2 = Komfort Air
Workbook 3 = Climate Control
Workbook 4 = Memphis
Workbook 5 = Comfortech
Workbook 6 = Midland
Workbook 7 = Norrell
Workbook 8 = Pardee
Workbook 9 = R&M
Workbook 10 = Raleigh

Each workbook has a worksheet named "CENTER DATA" that I am copying the entire worksheet and pasting into the master workbook under the corresponding center name. This worksheet is updated twice a day by each of my individual centers and I would like to have a module that pulls all of the information from each of the workbooks to fill the master.

I hope this creates some clarity.

Thanks for your help!
 

Excel_Newbie4980

New Member
Joined
May 6, 2021
Messages
9
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

I've done something very similar in the past. I think the easiest approach is to set it up as a loop, because you're essentially doing the same thing each time.

In your master workbook, write a list of your centers somewhere:

CENTER 1
CENTER 2
CENTER 3
.....
CENTER 10

Then code the VBA as a loop to go down this list, and reference each CENTER X as the name to use in the file open, as well as the sheet name to copy into the master workbook.

No need to code a "open all files in a folder" or some such as it sounds like your CENTER workbooks are static, and won't change next to to include up to CENTER 20 or something.

Sorry I don't have the time to code it to show you at the moment, but reply if you need more help and what I said is beyond you.
Hello Oddball!

Thank you for your response! Unfortunately, I do not know how to create a VBA loop. However, I believe you are correct as the information does not change through each day except for a few cells within the worksheet template that is designed for each center. I am simply just copying and pasting the same "report" twice a day to fill in an existing worksheet on the master workbook to feed another worksheet that compiles all of the data for each center.
 

Oddball2020

Board Regular
Joined
May 6, 2021
Messages
69
Office Version
  1. 365
Platform
  1. Windows
Hello Oddball!

Thank you for your response! Unfortunately, I do not know how to create a VBA loop. However, I believe you are correct as the information does not change through each day except for a few cells within the worksheet template that is designed for each center. I am simply just copying and pasting the same "report" twice a day to fill in an existing worksheet on the master workbook to feed another worksheet that compiles all of the data for each center.
What you described is a prime candidate for a loop using VBA, especially because you're doing it twice a day!

First though, a question about what you previously stated:
"Each workbook has a worksheet named "CENTER DATA" that I am copying the entire worksheet and pasting into the master workbook under the corresponding center name. This worksheet is updated twice a day by each of my individual centers"

Will the paste under the corresponding center name occur over the top of the previously existing data, or added below it in that CENTER's section?

Are all the CENTER sections in a list, such that for example I could search down Column A and find CENTER 1, CENTER 2 etc....

Each copy/pasted section is the same range size, or will it differ in any way from week to week?
 

Excel_Newbie4980

New Member
Joined
May 6, 2021
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
What you described is a prime candidate for a loop using VBA, especially because you're doing it twice a day!

First though, a question about what you previously stated:
"Each workbook has a worksheet named "CENTER DATA" that I am copying the entire worksheet and pasting into the master workbook under the corresponding center name. This worksheet is updated twice a day by each of my individual centers"

Will the paste under the corresponding center name occur over the top of the previously existing data, or added below it in that CENTER's section?

Are all the CENTER sections in a list, such that for example I could search down Column A and find CENTER 1, CENTER 2 etc....

Each copy/pasted section is the same range size, or will it differ in any way from week to week?
There is not currently a list of centers, but I can create one. Each copy/pasted section is exactly the same range size and will never change.
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,384
Office Version
  1. 2016
Platform
  1. Windows
Hello Zot,

Thank you for your reply! I believe your understanding of my problem is correct. I am receiving a "Run-time error'9': Subscript out of range" message when inputting the code you typed above.

I hope this will simplify by give you exact details of my my folders as I believe I am the one causing the error by not understanding exactly what to do.

Master Workbook name = 5-Mid Atlantic MASTER May.xlsm
Worksheets (in order):
The code is supposed to be installed in Master workbook in normal module.

I have tested the code with mock up 3 mock-up workbooks with name equal to 3 sheets in Master workbook and it ran just fine. What no clear to me is what you want to copy from each CENTER DATA sheet. My code was just copying whole sheet. Therefore. the whole sheet get replaced each time you run the code. You never mentioned what row or column to copy and how to handle new data coming in.

Right now is bed time for me 😁 and my sample work is in office. NOt sure what cause the error right now but if I have time during weekend I will try to find the cause.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,976
Messages
5,656,189
Members
418,288
Latest member
reba557

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
Top