Sheets.Add is targeting the wrong workbook

[sjesiono]

I'm trying to update code that will copy data on sheets from one workbook to another if they don't exist. The listed Sub will do that when I run it on its own. However, when I try to use it indirectly, by calling it with another macro, it adds the new sheet to the end of Target.Workbook instead of the Destination.Workbook. It then gives me an error saying that the name is already in use, because it's trying to rename the sheet to the one that it's supposed to be copying. Any suggestions as to where I'm going wrong on this?

Sub WSMove()
'   Copy over any tabs, except the Instruction tab, that aren't in the new workbook already
    Dim ws As Worksheet
    Dim nm As name
    Dim found
    Dim name
    For Each ws In Workbooks("Target.Workbook.xlsm").Worksheets
    found = False
    
    name = ws.name
    If name <> "Instructions" Then
        For Each nws In Workbooks("Destination.Workbook.xlsm").Worksheets
            If name = nws.name Then
                found = True
            End If
        Next nws
        
        If found = False Then
            Sheets.Add(After:=Workbooks("Destination.Workbook.xlsm").Sheets(Workbooks("Destination.Workbook.xlsm").Sheets.count)).name = name
            Workbooks("Target.Workbook.xlsm").Sheets(name).Cells.Copy
            Workbooks("Destination.Workbook.xlsm").Worksheets(name).Range("A1").PasteSpecial xlPasteValues
            Workbooks("Destination.Workbook.xlsm").Worksheets(name).Range("A1").PasteSpecial xlPasteColumnWidths
            Workbooks("Destination.Workbook.xlsm").Worksheets(name).Range("A1").PasteSpecial xlPasteFormats
            Workbooks("Destination.Workbook.xlsm").Worksheets(name).Protect Password:="workbook"
        End If
    End If
    Next ws
End Sub

 

[rollis13]

What if you change this line:

Sheets.Add(After:=Workbooks("Destination.Workbook.xlsm").Sheets(Workbooks("Destination.Workbook.xlsm").Sheets.Count)).name = name

to:

Workbooks("Destination.Workbook.xlsm").Sheets.Add(After:=Workbooks("Destination.Workbook.xlsm").Sheets(Workbooks("Destination.Workbook.xlsm").Sheets.Count)).name = name

 

[sjesiono]

That solved it. Thank you!

 

[rollis13]

So, just some trimming:

If found = False Then
    With Workbooks("Destination.Workbook.xlsm")
        .Sheets.Add(After:=.Sheets(.Sheets.Count)).name = name
        Workbooks("Target.Workbook.xlsm").Sheets(name).Cells.Copy
        .Worksheets(name).Range("A1").PasteSpecial xlPasteValues
        .Worksheets(name).Range("A1").PasteSpecial xlPasteColumnWidths
        .Worksheets(name).Range("A1").PasteSpecial xlPasteFormats
        .Worksheets(name).Protect Password:="workbook"
    End With
End If

 

[Jon Peltier]

Would this be easier?

Workbooks("Target.Workbook.xlsm").Sheets(name).Copy After:=Workbooks("Destination.Workbook.xlsm").Sheets(Workbooks("Destination.Workbook.xlsm").Sheets.Count)