Copy Worksheets into already existing Workbooks with VBA

[mrsleepy95]

I have a Workbook that has 10 different Worksheets in it, all with their individual names. Then I have 10 separate Workbooks, all with names corresponding to the Worksheets names. I am looking to copy each worksheet in the original Workbook into its relative individual workbook.

 

[mumps]

What is the full path to the folder containing the separate workbooks? Are the ten files the only files in that folder? Can you list 3 or 4 worksheet names with their corresponding workbook names including their extension (xls, xlsx, xlsm)?

 

[mrsleepy95]

What is the full path to the folder containing the separate workbooks? Are the ten files the only files in that folder? Can you list 3 or 4 worksheet names with their corresponding workbook names including their extension (xls, xlsx, xlsm)?

The full path to the folder containing the separate workbooks is F:\Master Workbooks\Calgary Change Variables 1971-2006\Prop

I used 10 files as an example, but there are actually files in that folder. A few examples of the worksheet names are "HH Gross Rent 30 Perc Inc LQ", "HH Owners Pay 30 Perc Inc LQ", and Avg HH Inc LQ". Their corresponding workbook names are exactly the same. The extensions are .xlsb.

 

[mrsleepy95]

The full path to the folder containing the separate workbooks is F:\Master Workbooks\Calgary Change Variables 1971-2006\Prop

I used 10 files as an example, but there are actually files in that folder. A few examples of the worksheet names are "HH Gross Rent 30 Perc Inc LQ", "HH Owners Pay 30 Perc Inc LQ", and Avg HH Inc LQ". Their corresponding workbook names are exactly the same. The extensions are .xlsb.

Sorry, I meant that there are 69 files in that folder.

 

[mumps]

Try:

Sub CopySheet()
    Application.ScreenUpdating = False
    Dim wkbDest As Workbook
    Dim wkbSource As Workbook
    Set wkbSource = ThisWorkbook
    Dim ws As Worksheet
    For Each ws In wkbSource.Sheets
        Set wkbDest = Workbooks.Open("F:\Master Workbooks\Calgary Change Variables 1971-2006\Prop\" & ws.Name & ".xlsb")
        ws.Copy after:=Sheets(Sheets.Count)
        wkbDest.Close True
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[mrsleepy95]

Try:

Sub CopySheet()
    Application.ScreenUpdating = False
    Dim wkbDest As Workbook
    Dim wkbSource As Workbook
    Set wkbSource = ThisWorkbook
    Dim ws As Worksheet
    For Each ws In wkbSource.Sheets
        Set wkbDest = Workbooks.Open("F:\Master Workbooks\Calgary Change Variables 1971-2006\Prop\" & ws.Name & ".xlsb")
        ws.Copy after:=Sheets(Sheets.Count)
        wkbDest.Close True
    Next ws
    Application.ScreenUpdating = True
End Sub

This worked perfectly, thank you so much!

 

[Fluff]

Cross posted https://www.excelforum.com/excel-pr...into-already-existing-workbooks-with-vba.html

Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[mumps]

You are very welcome.