Excel VBA - Copying all Worksheets from a specific workbook into an active workbok

Question

I'm trying to copy all worksheets from a file saved on a network drive into my current active workbook. After they are copied I would like to hide them.

The tricky part, which I have yet to been able to find, is every time the macro is re-run I would like those worksheets that were previously copied over to be overwritten or deleted and replaced by the new worksheets from the existing file I am copying from.

Currently, I have my code set up to just copy over a specific worksheet depending on the string of a hyperlink. Below is what I've started but its not quite the direction I want to head.

Note the below is the edited script:

Sub ImportWorksheets()

Dim wb As Workbook, ws As Worksheet, wbTarget As Workbook, wsTarget As Worksheet

Application.ScreenUpdating = False

Dim pth As String
    pth = wb.Path

Dim titleDetailPth As String
    titleDetailPth = Left(pth, InStrRev(pth, "\") - 1)

Dim filePthName As String
    filePthName = titleDetailPth & "\New Release Templates\" & "Key New Release Accounts Details.xlsx"

Set wb = ActiveWorkbook 'Your workbook

Set wbTarget = Workbooks.Open(filePthName, UpdateLinks:=False, ReadOnly:=True) 'The drive workbook

For Each wsTarget In wbTarget.Worksheets 'a loop for each worksheet on the drive workbook
    For Each ws In wb.Worksheets ' a loop for each worksheet on your workbook
        If wsTarget.Name = ws.Name Then 'if the sheet you are trying to import exist, it will delete it
            Application.DisplayAlerts = False
            ws.Delete
            Application.DisplayAlerts = True
        End If
    Next ws
    wsTarget.Copy After:=wb.Sheets(wb.Sheets.Count) 'this will copy it into the last sheet
    wb.Sheets(wb.Sheets.Count).Visible = 0 'this will hide it
Next wsTarget
wbTarget.Close SaveChanges:=False
Application.ScreenUpdating = True

End Sub

What do you want excel to do with the duplicated tabs? delete them and replace from the drive file or keep them and change the name of the new tab? — Damian
delete them and replace them from the active workbook. The drive file will be manually maintained. This will control for changes or additions on the drive file. Essentially the only place I want the files that are copied over to be deleted is in the active workbook when the macro is re-run @Damian — Ranger9

Damian Damian · Accepted Answer · 2018-09-27T21:55:43

Then this should do the work for you:

Sub ImportWorksheets()

    Dim wb As Workbook, ws As Worksheet, wbTarget As Workbook, wsTarget As Worksheet

    Application.ScreenUpdating = False


    Set wb = ThisWorkbook 'Your workbook

    Set wbTarget = Workbooks.Open("wherever your drive file is", UpdateLinks:=False, ReadOnly:=True) 'The drive workbook

    For Each wsTarget In wbTarget.Worksheets 'a loop for each worksheet on the drive workbook
        For Each ws In wb.Worksheets ' a loop for each worksheet on your workbook
            If wsTarget.Name = ws.Name Then 'if the sheet you are trying to import exist, it will delete it
                Application.DisplayAlerts = False
                ws.Delete
                Application.DisplayAlerts = True
            End If
        Next ws
        wsTarget.Copy After:=wb.Sheets(wb.Sheets.Count) 'this will copy it into the last sheet
        wb.Sheets(wb.Sheets.Count).Visible = 0 'this will hide it
    Next wsTarget
    wbTarget.Close SaveChanges:=False
    Application.ScreenUpdating = True

End Sub

Excel VBA - Copying all Worksheets from a specific workbook into an active workbok

1 Answers