How to Copy Sheet Names from a Source Workbook

[ExcelPupper]

Hi. I have here a code that I need to do some tweaking. My problem is what additional code is appropriate wherein I could copy the Sheet Names where the data came from (srcWB) into the destination file (desWS).

Here's a sample of the desired output, also see below code for reference. Thanks!

Private Sub CopyEachPrcs()
    Application.ScreenUpdating = False
    Dim lastRow As Long, srcWB As Workbook, desWS As Worksheet
    Set srcWB = ThisWorkbook
    Set desWS = Workbooks("final data.xlsm").Sheets(1)
    With srcWB
        With .Sheets(1)
            lastRow = .Range("A" & .Rows.Count).End(xlUp).Row
            .Range("A2:C" & lastRow).Copy
            desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial xlPasteValues
        End With
        With .Sheets(2)
            lastRow = .Range("A" & .Rows.Count).End(xlUp).Row
            .Range("B2:B" & lastRow).Copy
            desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial xlPasteValues
            .Range("A2:A" & lastRow).Copy
            desWS.Cells(desWS.Rows.Count, "B").End(xlUp).Offset(1).PasteSpecial xlPasteValues
            .Range("C2:C" & lastRow).Copy
            desWS.Cells(desWS.Rows.Count, "C").End(xlUp).Offset(1).PasteSpecial xlPasteValues
        End With
    End With
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

 

[gallen]

Hello

You don't say where in the destination you'd like it copying to. But the sheet name of srcWB would be

srcWb.Sheets(1).Name

 

[ExcelPupper]

Hello

You don't say where in the destination you'd like it copying to. But the sheet name of srcWB would be

srcWb.Sheets(1).Name

Im sorry I haven't added them, the ones with apostrophe are the trial code I am tweaking. I need them to be copied on column D of destination file.

Private Sub CopyEachPrcs()
    Application.ScreenUpdating = False
    Dim lastRow As Long, srcWB As Workbook, desWS As Worksheet
    Set srcWB = ThisWorkbook
    Set desWS = Workbooks("final data.xlsm").Sheets(1)
    With srcWB
        With .Sheets(1)
            lastRow = .Range("A" & .Rows.Count).End(xlUp).Row
            .Range("A2:C" & lastRow).Copy
            desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial xlPasteValues
            'desWS.Cells(desWS.Rows.Count, "D").End(xlUp).Offset(1).Value = srcWB.Sheets(1).Name
        End With
        With .Sheets(2)
            lastRow = .Range("A" & .Rows.Count).End(xlUp).Row
            .Range("B2:B" & lastRow).Copy
            desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial xlPasteValues
            .Range("A2:A" & lastRow).Copy
            desWS.Cells(desWS.Rows.Count, "B").End(xlUp).Offset(1).PasteSpecial xlPasteValues
            .Range("C2:C" & lastRow).Copy
            desWS.Cells(desWS.Rows.Count, "C").End(xlUp).Offset(1).PasteSpecial xlPasteValues
           'desWS.Cells(desWS.Rows.Count, "D").End(xlUp).Offset(1).Value = srcWB.Sheets(2).Name
        End With
    End With
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

 

[ExcelPupper]

Hi anyone who could assist to correct this code? I think this one contains the logic I need to do however it does not run properly.
Any help would be much appreciated. Thanks!

desWS.Range("D & Range(""D"" & Rows.Count).End(xlUp).Offset(1):D & lastRow").Value = "Sheets(1).Name"