Copy worksheet from active workbook to another workbook in a different location

Question

Out of a big script which does the processing of data in my current active workbook, and then copies the data into a new sheet. I have introduced a button to copy same sheet from current workbook to another workbook.

Here D6 contains the actual path of destination file. It is correct and opening when I try to open the link using Windows + Run option

J12 contains name of the new sheet.

"Total" represents first sheet in the destination workbook after which the new workbook is supposed to be inserted.

Private Sub CopyToDest_Click()

        Dim destWbk As Workbook
        Dim sourceWbk As Workbook
        Dim shName As Variant
        Dim filePath As Variant

        Application.ScreenUpdating = False
        Application.DisplayAlerts = False

    'Set source and dest sheets
        Set sourceWbk = ThisWorkbook
        filePath = Sheet29.Range("D6").Value
        Set destWbk = Workbooks.Open(filePath)
        shName = Sheet29.Range("J12").Value

    'Copy sheet from source file to destination sheet
        sourceWbk.Sheets(shName).Copy After:=destWbk.Sheets("Total")
        destWbk.Save
        destWbk.Close

     'Delete copied sheet from source file
        Sheets(shName).Select
        ActiveWindow.SelectedSheets.Delete

        Application.ScreenUpdating = True
        Application.DisplayAlerts = True

        MsgBox "Sheet Copied Successfully", vbInformation, "Success"

End Sub

sourceWbk.Sheets(shName).Copy After:=destWbk.Sheets("Total") - This code sometimes gives me "Run-time error '9': Subscript out of range error, whereas sometimes it shows success msgbox. Really confusing scenario. Can someone please help?

Thanks in advance.

Answer 1

Finally normal macro recording helped in solving the issue. I just recorded a macro, and replaced the static values with dynamic ones. Even added an extra option for user to move the file in destination file after a particular sheet.

Private Sub CopyToDest_Click()

        Dim destWbk As Workbook
        Dim wbkName As String
        Dim shName As String, _
            dstShName As String
        Dim filePath As Variant, _
            dstFileName As Variant


        Application.ScreenUpdating = False
        Application.DisplayAlerts = False

        'check neccessary cells have values
        If Sheet29.Range("R12").Value = "" Then
                MsgBox "Destination worksheet name has to be selected in Cell R12"
                Exit Sub
            End If

    'Set source and dest sheets
        wbkName = ThisWorkbook.Name ' Storing name of macro workbook
        filePath = Sheet29.Range("D6").Value 'D6 holds the file location of destination sheet
        dstFileName = Sheet29.Range("D7").Value 'D7 holds the file name extracted from D6
        Set destWbk = Workbooks.Open(filePath) 'Open destination sheet
        shName = Sheet29.Range("J12").Value 'Contains sheetname in macro workbook which has to be moved
        dstShName = Sheet29.Range("R12").Value ' Contains sheetname of destination workbook after which the sheet has to be placed.

    'Copy sheet from macro file to destination sheet          
            Windows(wbkName).Activate
            Sheets(shName).Select
            Sheets(shName).Move After:=Workbooks(dstFileName).Sheets(dstShName)
            destWbk.Save
            destWbk.Close
            Windows(wbkName).Activate
            ActiveWorkbook.Save

        Application.ScreenUpdating = True
        Application.DisplayAlerts = True

        MsgBox "Sheet Copied Successfully", vbInformation, "Success"

End Sub

Answer 2

That error means that the sheet of that name doesn't exist. Either "Total" or whatever is inside shName is invalid. Remember that the name here is the name of the Tab in Excel and not the name of the sheet object in VBA. A worksheet has 2 names.