Copying Worksheets from another Workbook To existing Worksheets in Destination Workbook

[sspatriots]

Good morning,

I have this code that will copy a worksheet from a source workbook to an existing worksheet my destination workbook. Looking for some assistance on making this same code work to copy several worksheets from the source workbook to existing worksheets in my destination workbook without having to close the source workbook each time between copying each worksheet over to the destination workbook.

This is the code that I have now for copying just the one worksheet over:

Sub Update_AllWSData()

Dim WB As Workbook
Dim DestSht As Worksheet
Dim SourceSht As Worksheet

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set DestSht = ThisWorkbook.Worksheets("WIP")
Set WB = Workbooks.Open(Filename:="G:\CompanyName\Manufacturing Detail Schedule1.xlsx")  'Selected File full path
Set SourceSht = WB.Worksheets("WIP")

    lastRow = SourceSht.Cells(Rows.Count, "B").End(xlUp).Row
    
    SourceSht.Range("B1:V" & lastRow).Copy
    
    WB.Close

    Worksheets("WIP").Select
    
    DestSht.Range("A1").Select
    ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
        DisplayAsIcon:=False, NoHTMLFormatting:=True
    Range("A2").Select

    Worksheets("Jobs").Select

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

 

[rlv01]

Perhaps something like this.

Sub Update_AllWSData()
    
    Dim WB As Workbook
    Dim DestSht As Worksheet
    Dim SourceSht As Worksheet
    Dim lastRow As Long
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Set WB = Workbooks.Open(Filename:="G:\CompanyName\Manufacturing Detail Schedule1.xlsx")  'Selected File full path
    ThisWorkbook.Activate
    For Each SourceSht In WB.Worksheets
        Select Case SourceSht.Name
            Case "WIP", "WIP_AUG", "WIP_SEP", "WIP_OCT"  '<-names of worksheets you want to copy data from.
                With SourceSht
                    lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
                    SourceSht.Range("B1:V" & lastRow).Copy
                End With
                
                Set DestSht = ThisWorkbook.Worksheets(SourceSht.Name)
                With DestSht
                    .Activate
                    .Range("A1").Select
                    .PasteSpecial Format:="Unicode Text", Link:=False, DisplayAsIcon:=False, NoHTMLFormatting:=True
                End With
        End Select
    Next SourceSht
    ThisWorkbook.Worksheets("Jobs").Select
    WB.Close False
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

 

[sspatriots]

Perhaps something like this.

Sub Update_AllWSData()
   
    Dim WB As Workbook
    Dim DestSht As Worksheet
    Dim SourceSht As Worksheet
    Dim lastRow As Long
   
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
   
    Set WB = Workbooks.Open(Filename:="G:\CompanyName\Manufacturing Detail Schedule1.xlsx")  'Selected File full path
    ThisWorkbook.Activate
    For Each SourceSht In WB.Worksheets
        Select Case SourceSht.Name
            Case "WIP", "WIP_AUG", "WIP_SEP", "WIP_OCT"  '<-names of worksheets you want to copy data from.
                With SourceSht
                    lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
                    SourceSht.Range("B1:V" & lastRow).Copy
                End With
               
                Set DestSht = ThisWorkbook.Worksheets(SourceSht.Name)
                With DestSht
                    .Activate
                    .Range("A1").Select
                    .PasteSpecial Format:="Unicode Text", Link:=False, DisplayAsIcon:=False, NoHTMLFormatting:=True
                End With
        End Select
    Next SourceSht
    ThisWorkbook.Worksheets("Jobs").Select
    WB.Close False
   
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

Thank you, I will give this a go and let you know how it works out. Much appreciated.

 

[sspatriots]

Perhaps something like this.

Sub Update_AllWSData()
   
    Dim WB As Workbook
    Dim DestSht As Worksheet
    Dim SourceSht As Worksheet
    Dim lastRow As Long
   
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
   
    Set WB = Workbooks.Open(Filename:="G:\CompanyName\Manufacturing Detail Schedule1.xlsx")  'Selected File full path
    ThisWorkbook.Activate
    For Each SourceSht In WB.Worksheets
        Select Case SourceSht.Name
            Case "WIP", "WIP_AUG", "WIP_SEP", "WIP_OCT"  '<-names of worksheets you want to copy data from.
                With SourceSht
                    lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
                    SourceSht.Range("B1:V" & lastRow).Copy
                End With
               
                Set DestSht = ThisWorkbook.Worksheets(SourceSht.Name)
                With DestSht
                    .Activate
                    .Range("A1").Select
                    .PasteSpecial Format:="Unicode Text", Link:=False, DisplayAsIcon:=False, NoHTMLFormatting:=True
                End With
        End Select
    Next SourceSht
    ThisWorkbook.Worksheets("Jobs").Select
    WB.Close False
   
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

I seem to be getting an error message at the line that says ".PasteSpecial Format:=.....". The message is "Method 'PasteSpecial' of object '_Worksheet' failed". Is it not capturing what was copied in the Source workbook maybe?

 

[sspatriots]

I just changed the following:

                    .Range("A1").Select
                    .PasteSpecial Format:="Unicode Text", Link:=False, DisplayAsIcon:=False, NoHTMLFormatting:=True

To this:

                    .Range("A1").PasteSpecial Paste:=xlPasteValues

This seems to be working very well. Thanks so much for your help.