VB excel worksheet not copying to new workbook-CodePudding

struggling to copy a worksheet from source book to destination book. I've tried 4 different codes found on SO, but running into different errors all the time. Either: "Copy method failed", "No such interface found", "Exception"- at the copy function.

I know that there are a lot of links and websites referring to the copy method, but i've tried them all and still no luck.

Option Strict = Off Option Explicit = On

Excel 2016 VS 2019

Sourceworkbook has formatting in and merged cells. Needing the formatting included in the copy method, because I'll be using the new workbooks as back-ups or copies for printing. The sourceworkbook has a template on one of the sheets named "TempPage".

Code:

    xlApp1 = New Excel.Application
    xlWorkBook1 = xlApp1.Workbooks.Add
    'xlWorkSheet1 = CType(xlWorkBook1.Sheets.Add(), Excel.Worksheet)



    xlWorkSheet = CType(xlWorkBook.Sheets("TempPage"), Excel.Worksheet) 'Source
    xlWorkSheet1 = CType(xlWorkBook1.Sheets("Sheet1"), Excel.Worksheet) 'Destination

    'Tried this code
    'Dim rngSource As Excel.Range, rngTarget As Excel.Range, targetRow As Long
    'rngSource = xlWorkBook.Sheets("TempPage").UsedRange
    'With xlWorkBook.Sheets("TempPage")
    'targetRow = .UsedRange.SpecialCells(XlCellType.xlCellTypeLastCell).Row   1
    'rngTarget = .cells(targetRow, rngSource.Column)
    'End With
    'rngSource.Copy(rngTarget)


    'Tried this code
    'Dim sourceWorkSheet As Excel.Worksheet
    'sourceWorkSheet = xlWorkBook.Sheets("TempPage")
    '//Copies the source worksheet to the destination workbook, places it after the last
    '//sheet in the destination workbook.
    'sourceWorkSheet.Copy(, xlWorkBook1.Sheets(xlWorkBook1.Sheets.Count))

    'Tried this
    'xlWorkSheet.Copy(, xlWorkBook1.Sheets(xlWorkBook1.Sheets.Count))

    'tried this
    'xlWorkSheet1.Range("A1:I46").Value = xlWorkSheet.Range("A1:I46").Value
    'xlWorkSheet.Application.Goto(xlWorkSheet.Range("A1:I46"), True)
    'xlWorkSheet.Range("A1:I46").Select()
    'xlWorkSheet.Range("A1:I46").Copy()
    'xlWorkSheet1.PasteSpecial(Excel.XlPasteType.xlPasteAll, 
    'Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, False, False)

    'Tried this
    'xlWorkSheet.Range("A1:I46").Copy(xlWorkSheet1.Range("A1:I46"))
    'xlWorkSheet1.PasteSpecial(Excel.XlPasteType.xlPasteFormats)

    xlWorkBook1.SaveAs(BTPath & "\" & xlWorkSheet.Range("B3").Value & ".xls", Excel.XlFileFormat.xlExcel5) 'save the receipt as the ticket number
    If RadioButton3.Checked = True Then
        'unpaid - send copy to unpaid folder
        xlWorkBook1.SaveAs(UnpaidPath & "\" & xlWorkSheet.Range("B3").Value & ".xls", Excel.XlFileFormat.xlExcel5)
    ElseIf RadioButton4.Checked = True Then

    End If

I need help with the copying method please.

CodePudding user response：

This is another approach.

I don't know if it's required, but I have always opened the workbook (in the background) and then perform the copy of the worksheet. Then I close the workbooks that isn't any longer needed.

I assume that ThisWorkbook is the same workbook as the code is stored in. If xlWorkSheet1 in your case is another workbook than where the code is executed from, (so you have 3 workbooks, 1 with code and 2 where you copy and paste) I would open that work book too and then perform the copy/paste actions.

'#### COPY DATA FROM LAST WORKBOOK
Dim DataSourceWorkbook As Workbook
Dim sFileName As String

sFileName = "Workbook.xlsx"
    
Workbooks.Open FileName:="G:\Till" & "\" & sFileName 'Open path   workbookfilename
Set DataSourceWorkbook = Workbooks(sFileName) 'Set "DataSourceWorkbook" to the same filename as latest modified file
DataSourceWorkbook.Sheets("TempPage").Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count) 'Copy Sheet "TempPage" to the last worksheet in "ThisWorkbook"
Workbooks(sFileName).Close 'Close the "DataSourceWorkbook"

Slightly different modification:

I assume that xlWorkBook = ThisWorkbook and that xlWorkBook is opened in some code above the ones I mention.

Sub CopyShtInOtherWb()
 Dim xlWorkBook1 As Workbook
 Dim xlWorkBook As Workbook

 Set xlWorkBook1 = Workbooks.Open(ThisWorkbook.Path & "\Destination.xlsm")
 xlWorkBook.Sheets("TempPage").Copy After:=xlWorkBook1.Sheets("Sheet1")
 xlWorkBook1.Close SaveChanges:=True
End Sub

CodePudding user response：

After a bit more research, found a way to save the worksheet from the source book. Closing the book and reopening the source book for continued usage. Only problem i'm now running into, is that the formulas are still being copied as well and some cells aren't in the same format(bold, merged, size) but found a link on SO - Save values (not formulae) from a sheet to a new workbook?

New code:

    'save first
    xlWorkBook.Save() 'Save the workbook

    Dim newpath As String = BTPath & "\" & xlWorkSheet.Range("B3").Value & ".xls"

    xlWorkSheet = CType(xlWorkBook.Sheets("TempPage"), Excel.Worksheet)
    xlWorkSheet.Copy()
    xlWorkSheet.SaveAs(newpath, Excel.XlFileFormat.xlExcel5)

    If RadioButton3.Checked = True Then
        'unpaid - send copy to unpaid folder
        xlWorkBook.SaveAs(BTPath & "\" & xlWorkSheet.Range("B3").Value & ".xls")
    End If

    'Close the file and reopen the database file
    xlWorkBook.Save() 'Save the workbook
    xlWorkBook.Close() 'Close workbook
    If xlApp Is Nothing Then
        'do nothing
    Else
        xlApp.Quit() 'Quit the application
    End If


    GC.Collect()
    GC.WaitForPendingFinalizers()
    System.Threading.Thread.Sleep(500)
    GC.Collect()
    GC.WaitForPendingFinalizers()
    System.Threading.Thread.Sleep(500)

    'reopen
    xlApp = New Excel.Application
    xlWorkBook = xlApp.Workbooks.Open(filepath)

    'clear the sheet
    xlWorkSheet = CType(xlWorkBook.Sheets("TempPage"), Excel.Worksheet)
    xlWorkSheet.Range("D45").Value = ""
    xlWorkSheet.Range("B3").Value = ""
    xlWorkSheet.Range("B10").Value = ""
    xlWorkSheet.Range("F10").Value = ""
    xlWorkSheet.Range("F12").Value = ""
    xlWorkSheet.Range("B11").Value = ""
    xlWorkSheet.Range("F11").Value = ""
    xlWorkSheet.Range("I10").Value = ""
    xlWorkSheet.Range("A14:H10").Value = ""

    'save but don't close
    xlWorkBook.Save() 'Save the workbook