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

[Excel_Newbie4980]

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!

 

[Oddball2020]

I did...see uploaded image for what pops up.

What version of Excel are you running?

Forgot to mention this as well... need to load a couple things... in the VBA window, click tools then References and scroll and mark all of these (otherwise your workbook won't recognize that VBA code):

 

[Zot]

- 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!

I have done another simulation. I think the error you got was probably due to one of the sheet did not have a sheet named CENTER DATA.

I see no need to loop through each sheet in source workbook looking for sheet CENTER DATA. I just need to get the sheet and if not found give msg.

So, I simplified code a bit and add error handling if sheet not found

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)
    Set ws = wb.Sheets("CENTER DATA")
    On Error GoTo ErrHandler
    ws.Cells.Copy wbMaster.Sheets(Split(FName, ".")(0)).Range("A1")
SkipCopy:
    'Close wb without saving
    wb.Close False
    'Set the fileName to the next file
    FName = Dir
Wend

ErrHandler:
If Err.Number = 9 Then
    MsgBox "No CENTER DATA sheet found in " & wb.Name
    Err.Clear
    Resume SkipCopy
End If

End Sub

 

[Marc L]

​

Can be done without any error handler in the main procedure like in post #12 just ignoring any workbook without the expected sheet …​

 

[Oddball2020]

​

Can be done without any error handler in the main procedure like in post #12 just ignoring any workbook without the expected sheet …​

True.

Wearing an analyst hat though (thinking out loud, nothing against your post), the thing though I like about not using error handlers is that it will force me to deal with possible inconsistencies in source data files from other people's workbooks or data exports from databases etc that I have no control over. You can then spot where someone changed the name of a worksheet, or header etc, and make the adjustments. If you error-handle to skip over not finding an exact match (macro was looking for "Workbook1" but it got renamed "Workbook 1" etc, the user could be completely oblivious that they never incorporated the source data into their master workbook like they thought the macro was doing afte the macro is done running. At least having an error handler message to notify them of an inconsistencies, and exit the sub helps ID areas to correct before a re-run.

 

[Marc L]

​

You are right, my thought was based upon post #22 if the expected sheet does not exist in a workbook, no need to stop the VBA procedure …​

 

[Zot]

​

Can be done without any error handler in the main procedure like in post #12 just ignoring any workbook without the expected sheet …​

For me error trapping here is more on acknowledge data from which workbook not being updated. Not expecting VBA being stopped on every single loop ?

 

[Marc L]

​

My bad as I did not see the Resume codeline ! Let say post #12 avoids this kind of error trapping …​