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!
 

Oddball2020

Board Regular
Joined
May 6, 2021
Messages
69
Office Version
  1. 365
Platform
  1. Windows
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):
 

Attachments

  • Scripts.JPG
    Scripts.JPG
    97.7 KB · Views: 6

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,384
Office Version
  1. 2016
Platform
  1. Windows
- 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
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)
    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

Well-known Member
Joined
Apr 5, 2021
Messages
937
Office Version
  1. 2010
Platform
  1. Windows
Can be done without any error handler in the main procedure like in post #12 just ignoring any workbook without the expected sheet …​
 

Oddball2020

Board Regular
Joined
May 6, 2021
Messages
69
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Apr 5, 2021
Messages
937
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Nov 26, 2020
Messages
1,384
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Apr 5, 2021
Messages
937
Office Version
  1. 2010
Platform
  1. Windows
My bad as I did not see the Resume codeline ! Let say post #12 avoids this kind of error trapping …​
 

Watch MrExcel Video

Forum statistics

Threads
1,133,271
Messages
5,657,764
Members
418,411
Latest member
Excellency

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