Copy worksheets to Other Workbooks in the Same folder based on 1) File Names & 2) Worksheet Names

Djro69

New Member
Joined
Nov 2, 2020
Messages
8
Office Version
  1. 2019
Platform
  1. Windows
Has anyone got a VBA handy that performs this or similar?

I've got the following:
A) Master workbook contains Summary sheet with list of Other workbooks (within same folder) in column L. Next column, column M contains the list of the other worksheets within Master workbook (in B below) that need copied from Master workbook to Other Workbooks

B) Master workbook also contains worksheets: Sheet1000, Sheet1001, Sheet1002..... Sheet 1030

C) The list within the Summary sheet (A above) shows which Workbooks to copy data to e.g.
Col L, Col M
Workbook2, Worksheet1000
Workbook3, Worksheet1001
Workbook4, Worksheet1002

Note - all the other workbooks are in the same folder as the Master workbook and already contain the worksheet with the same name which needs updated from the Master workbook

So after running the VBA, Workbook2 which has a worksheet called Worksheet1000 now has updated data in Worksheet 1000 copied over from the Master workbook, WB3 updated worksheet1001 etc.

Hope that makes sense but I've probably missed something so please shout.

Many thanks in advance.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Djro69

New Member
Joined
Nov 2, 2020
Messages
8
Office Version
  1. 2019
Platform
  1. Windows
Anyone? Here is a little diagram below. need a VBA code to update the tabs in Workbook1/2/3 with the data in the Master Data Workbook

1604665578388.png
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,301
It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).
 

Djro69

New Member
Joined
Nov 2, 2020
Messages
8
Office Version
  1. 2019
Platform
  1. Windows
Thanks for the suggestion mumps. To be clear - this isn't a screenshot of my sheet that I need a vba for. It is a separate diagram to show what the vba would do visually if that makes sense. So, if you can see the picture ok, that's all there is to take from it.

I've tried describing what I want in the first post but this diagram captures it pretty well. However, if this makes it easier, here is all 4 worksheets with full visility of what is going where.

Workbook A transfers data from various tabs to other workbooks based on list in columns L & M as follows:

1604934620286.png
 

Djro69

New Member
Joined
Nov 2, 2020
Messages
8
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

In case this helps, here is a basic VBA to copy all selected worksheets to new workbooks. I want to also add in a layer of complexity, that it copies each worksheet to a workbook based on a list which details where each worksheet should go. So sheet1001 goes to Workbook1, Sheet1002 goes to Workbook2 etc.

Sub CopySelectedSheetsToNewWorkbooks()
'Dimension variable and declare data types
Dim AW As Window
'The SET statement saves the active window as an object reference to variable AW
Set AW = ActiveWindow
'Iterate through selected worksheets based on active window
For Each SHT In AW.SelectedSheets
'The NewWindow method creates a new window based on the active window using an object reference saved to variable TempWindow
Set TempWindow = AW.NewWindow
'Copy worksheet to a new workbook
SHT.Copy
'Close the newly created window
TempWindow.Close
'Continue with next worksheet
Next
'Stop macro
End Sub
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,301
Place this macro in the Master workbook. You have to make sure that the workbook names in column L include the extension (xlsx, xlsm) so that they are all in this format: WorkbookName.xlsx
VBA Code:
Sub CopyData()
    Application.ScreenUpdating = False
    Dim LastRow As Long, wsSum As Worksheet, WB As Range, srcWB As Workbook, desWB As Workbook
    Set srcWB = ThisWorkbook
    Set wsSum = ThisWorkbook.Sheets("Summary")
    LastRow = wsSum.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    With wsSum
        For Each WB In .Range("L2:L" & LastRow)
            Set desWB = Workbooks.Open(ThisWorkbook.Path & "\" & WB)
            srcWB.Sheets(WB.Offset(, 1).Value).UsedRange.Copy Sheets(WB.Offset(, 1).Value).Range("A1")
            desWB.Close True
        Next WB
    End With
    Application.ScreenUpdating = True
End Sub
 
Solution

Djro69

New Member
Joined
Nov 2, 2020
Messages
8
Office Version
  1. 2019
Platform
  1. Windows
Legendary - many many thanks!!! Only been trying to do this for like 5 years.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,478
Messages
5,636,566
Members
416,924
Latest member
cmlacerna

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