Code help - macro to copy a worksheet to multiple workbooks without opening

[ferocious12]

Hi All,


I have one worksheet that need to be copied to 10+ workbooks saved in a folder. I don't want to open the target workbooks for copying purposes. I have the following macro to copy the activesheet and can only select one workbook. Can you help me to increase the selection to multiple workbooks to copy the activesheet to all the workbooks in a folder.

Thanks

Public Sub CopySheetToClosedWorkbook()
    Dim fileName
    Dim closedBook As Workbook
    Dim currentSheet As Worksheet
 
    fileName = Application.GetOpenFilename("Excel Files (*.xlsx), *.xlsx")
 
    If fileName <> False Then
        Application.ScreenUpdating = False
 
        Set currentSheet = Application.ActiveSheet
        Set closedBook = Workbooks.Open(fileName)
        currentSheet.Copy After:=closedBook.Sheets(closedBook.Worksheets.Count)
        closedBook.Close (True)
 
        Application.ScreenUpdating = True
    End If
End Sub

 

[Chihiro]

But you are opening the destination workbook in your code...

At any rate, instead of using Application.GetOpenFilename, use Application.FileDialog(msoFileDialogFolderPicker).

Then you can iterate over files using Dir function. You can find many sample codes in the forum.
Ex:
In Folder , 2 or more File Name's First 8 Char Should Not Same, How to Chek? | Chandoo.org Excel Forums - Become Awesome in Excel

 

[ferocious12]

But you are opening the destination workbook in your code...

At any rate, instead of using Application.GetOpenFilename, use Application.FileDialog(msoFileDialogFolderPicker).

Then you can iterate over files using Dir function. You can find many sample codes in the forum.
Ex:
In Folder , 2 or more File Name's First 8 Char Should Not Same, How to Chek? | Chandoo.org Excel Forums - Become Awesome in Excel

Many thanks. I will give it a try

 

[ferocious12]

I tried but couldn't do it. Any help?

 

[jolivanes]

Not without opening the Workbooks where the sheet needs to be copied into.

Copies the first sheet from all workbooks, except from this workbook which has the code in it, in the same folder where this workbook has been saved.

Sub Copy_Sheet1_Into_Master()
Dim mydir As String, myfile As String, mybook As Workbook, j As Long
    j = 0
    mydir = ThisWorkbook.path & "\"
    myfile = Dir(mydir & "*.xl*")
    Application.ScreenUpdating = False
    Do While myfile <> ""
        If myfile <> ThisWorkbook.Name Then
        Set mybook = Workbooks.Open(mydir & myfile)
            j = j + 1
            mybook.Sheets(1).Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)    '<--- Change the 1 to the sheet number (from the left) that needs copying into Master
                ThisWorkbook.Sheets(Sheets.Count).Name = "New Sheet " & j
        mybook.Close False
        End If
        myfile = Dir()
    Loop
    Application.ScreenUpdating = True
End Sub

 

[Marc L]

I don't want to open the target workbooks for copying purposes.

Hi, it may be done without opening workbooks under Excel but as it depends on the attachment you forgot again to share …​

​