CODEWITHSUNDEEP
vbaexcel
a.powell
a.powell

Reputation: 1722

Replace Excel Worksheet with Sheet from another file with VBA

Fairly new to VBA. I am attempting to pull one sheet out of another excel file and place it in the active workbook. I have done so, but I would like to have it set up so that every time I run the code it replaces the old sheet with the new sheet rather than adding an infinite amount of sheets as the program is continued to be used. Any help?

Here is the code I am currently using:

Private Sub CommandButton2_Click()


    Dim sh As Worksheet, wb As Workbook

    Set wb = Workbooks("QI VBA.xlsm")
    For Each sh In Workbooks("Example.xlsx").Worksheets
        If sh.Name = "Sheet1" Then
        sh.Copy After:=wb.Sheets(wb.Sheets.Count)

    End If
Next sh

End Sub

Upvotes: 1

Views: 3961

Answers (1)

Scott Holtzman
Scott Holtzman

Reputation: 27259

I took out your loop, because it seemed superfluous as written.

Private Sub CommandButton2_Click()

    Dim wb as Workbook
    Set wb = Workbooks("QI VBA.xlsm")

    If WorksheetExists(wb, "Sheet1") Then

        Application.DisplayAlerts = False
        wb.Worksheets("Sheet1").Delete
        Application.DisplayAlerts = True

    End If

    Workbooks("Example.xlsx").Worksheets("Sheet1").Copy After:= wb.Sheets (wb.Sheets.Count)

End Sub

Function WorksheetExists(wb As Workbook, sWSName As String) As Boolean

'=================================================================================================================================
'== Name: WorksheetExists
'== Purpose: To check if a worksheet exists in a given workbook
'== Notes: On Error Resume Next only used to make this a quicker process ...
'               try to name of sheet passed, if it fails, sheet doesn't exist
'=================================================================================================================================

On Error Resume Next

Dim ws As Worksheet
Set ws = wb.Worksheets(sWSName)

WorksheetExists = Err.Number = 0

On Error GoTo 0

End Function

Upvotes: 1

Related Questions