Copy worksheets into another workbook without duplicating worksheets

Question

I'm new at VBA so bear with me. I'm trying to copy worksheets from a specified file location into a master consolidated workbook. I would like to prevent copying duplicate worksheets into the consolidated workbook. For example, if sheet 1 has been copied into the Master consolidated workbook, I don't want to recopy it when the command has been ran. Below is the code I have so far.

Private Sub CommandButton1_Click()

Dim directory As String
Dim fileName As String
Dim sheet As Worksheet
Dim total As Integer

Application.ScreenUpdating = False
Application.DisplayAlerts = False

directory = "c:\test\"
' edit directory

fileName = Dir(directory & "*.xl??")

Do While fileName <> ""
Workbooks.Open (directory & fileName)

For Each sheet In Workbooks(fileName).Worksheets
    total = Workbooks("test import.xlsm").Worksheets.Count
    Workbooks(fileName).Worksheets(sheet.Name).Copy _
    after:=Workbooks("test import.xlsm").Worksheets(total)
Next sheet

Workbooks(fileName).Close

fileName = Dir()

Loop


Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

user3598756 user3598756 · Accepted Answer · 2017-03-16T08:48:33

you could use a helper function like the following

Function IsSheetFree(wb As Workbook, shtName As String) As Boolean
    Dim sht As Worksheet

    On Error Resume Next '<--| prevent any subsequent error to stop the function
    Set sht = wb.Worksheets(shtName) '<--| try setting 'sht' worksheet object to the one with passed name in the passed workbook
    IsSheetFree = sht Is Nothing '<--| return 'True' if 'sht' has not been successfully set
End Function

and exploit it as follows:

Private Sub CommandButton1_Click()

    Dim directory As String
    Dim fileName As String
    Dim sht As Worksheet

    Dim totalWb As Workbook

    Set totalWb = Workbooks("test import.xlsm")

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    directory = "c:\test\"
    ' edit directory

    fileName = Dir(directory & "*.xl??")

    Do While fileName <> ""
        Workbooks.Open directory & fileName '<--| this makes the just opened workbook the "active" one

        For Each sht In Worksheets '<--| loop through currently active workbook worksheets
            If IsSheetFree(totalWb, sht.Name) Then sht.Copy after:=totalWb.Worksheets(totalWb.Worksheets.Count)
        Next sht

        ActiveWorkbook.Close

        fileName = Dir()
    Loop

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

End Sub

Copy worksheets into another workbook without duplicating worksheets

2 Answers