CODEWITHSUNDEEP
vbaexcel
Ranger9
Ranger9

Reputation: 25

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

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

Upvotes: 0

Views: 222

Answers (1)

Damian
Damian

Reputation: 5174

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

Upvotes: 0

Related Questions