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

[Djro69]

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.

 

[Djro69]

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

 

[mumps]

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]

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:

 

[Djro69]

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]

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

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

 

[Djro69]

Legendary - many many thanks!!! Only been trying to do this for like 5 years.

 

[mumps]

You are very welcome.