Copying worksheets from other workbooks

[MOORED]

Hi All,

i have about 50 workbooks all in the same folder. In each workbook tab 3 is named "commercial"

is there an easy way to extract just this tab/worksheet from each in to another workbook.

Kind regards

DM

 

[John_w]

Try this, changing the folderPath string containing the 50 workbooks. Alternatively, Application.FileDialog(msoFileDialogFolderPicker) could be used, allowing you to browse and select the folder.

Public Sub Copy_Commercial_Sheet_From_All_Workbooks_In_Folder()

    Dim folderPath As String, fileName As String
    Dim destinationWorkbook As Workbook, sourceWorkbook As Workbook
    
    Set destinationWorkbook = ActiveWorkbook
    
    folderPath = "C:\path\to\folder\"

    folderPath = Trim(folderPath)
    If Right(folderPath, 1) <> "\" Then folderPath = folderPath & "\"
    
    Application.ScreenUpdating = False
    
    fileName = Dir(folderPath & "*.xlsx")   'NOTE - .xlsx workbooks
    While fileName <> vbNullString
        Set sourceWorkbook = Workbooks.Open(folderPath & fileName)
        With destinationWorkbook
            sourceWorkbook.Worksheets("Commercial").Copy After:=.Worksheets(.Worksheets.Count)
        End With
        sourceWorkbook.Close savechanges:=False
        fileName = Dir
    Wend
    
    Application.ScreenUpdating = True
    
    MsgBox "Done"
    
End Sub

 

[MOORED]

brilliant thanks for you quick response, I will try this afternoon but following what you written looks spot on many thanks

DM