Referencing temporary, unsaved workbook when copying worksheets

1
votes

I have worksheets I want to copy to a new, temporary workbook -- without saving it.

Worksheet.Copy copies the worksheet to a new, unnamed'ish (Book1, Book2, Book3, etc) workbook. I want all sheets to be copied to the same workbook.

For all worksheets after the first, I have tried using Worksheet.Copy After:=xlWb.Sheets(1), but I do not know how to reference the newly created workbook when setting the xlWb workbook-object. I keep receiving

run-time error 9, 'Subscript out of range'.

Dim xlApp As Excel.Application
Dim xlWb As Excel.Workbook
Dim xlWbOld As String
Dim xlWs As Excel.Worksheet
Dim xlWbNew As Excel.Workbook
Dim xlWsOld As Excel.Worksheet
Dim xlRng As Excel.Range
Dim xlRngOld As Excel.Range

xlWbOld = ActiveWorkbook.Name

Set xlApp = New Excel.Application
xlApp.Visible = True                                '*** Quite Important to set Excel.Visible,_
                                                    'Otherwise user wouldn't see the application's running _
                                                    'even though it would run as background

xlApp.Application.ScreenUpdating = False
Set xlWb = xlApp.Workbooks.Add                      'Create a new Workbook
Set xlWs = xlWb.Worksheets.Add

And this is where the worksheets should be copied:

Select Case strRptType
    Case "DAILY"
        xlWs.Name = "1-Daily Price"
        'Check the last column and the last row
        lLastRow = oBasic.GetLast(, "DailyRpt", False, "A")
        iLastCol = oBasic.GetLast(, "DailyRpt", True, 4)
        Set xlRngOld = wksDailyRpt.Range(wksDailyRpt.Cells(4, 1), wksDailyRpt.Cells(lLastRow, iLastCol))
        Application.ScreenUpdating = True
        xlRngOld.Copy

        Set xlRng = xlWs.Cells(1, 1)
        xlRng.PasteSpecial Paste:=xlPasteValues
        xlWs.Columns.AutoFit

        For Each xlWsOld In ActiveWorkbook.Worksheets
            If xlWsOld.Name = "ForwardPrices" Or xlWsOld.Name = "ForwardVolatilities" _
              Or xlWsOld.Name = "ForwardReturns" Or xlWsOld.Name = "ForwardCorrelations" Then

                Sheets(xlWsOld.Name).Copy After:=Workbooks(xlWb).Sheets(1)

            End If
        Next xlWsOld
End Select
3
excelvbaobjectcopy
Have you tried macro-recorder? You may also need Workbooks collection members to obtain newly created workbook name. – Peter L.
Show us how you are creating the Workbook. – RBarryYoung
I added some code snippets showing how the Workbook is created and how I would like to copy the worksheets. – KHH
For a start, change Sheets(xlWsOld.Name).Copy After:=Workbooks(xlWb).Sheets(1) to xlWsOld.Copy After:=xlWb.Sheets(1) - sorry, don't have time right now to investigate further. – chris neilsen

3 Answers

1
votes

This little macro goes through all the sheets of all the open workbooks, and copies them after the current sheet.

Sub GatherAllSheets()
  Dim Wb As Workbook, Sh As Worksheet
  For Each Wb In Workbooks
    If Not Wb Is ThisWorkbook Then
      For Each Sh In Wb.Worksheets
        Sh.Copy after:=ActiveSheet
      Next Sh
    End If
  Next Wb
End Sub

Is this what you need?
Or you need to copy the content of the sheets on the same sheet?

0
votes

I solved it with the following:

            For Each xlWsOld In ActiveWorkbook.Worksheets
                If xlWsOld.Name = "ForwardPrices" Or xlWsOld.Name = "ForwardVolatilities" _
                Or xlWsOld.Name = "ForwardReturns" Or xlWsOld.Name = "ForwardCorrelations" Then

                    Set xlRngOld = Nothing
                    Set xlWsForwards = xlWb.Worksheets.Add

                    lLastRow = oBasic.GetLast(, xlWsOld.Name, False, "A")
                    iLastCol = oBasic.GetLast(, xlWsOld.Name, True, 1)

                    Set xlRngOld = xlWsOld.Range(xlWsOld.Cells(1, 1), xlWsOld.Cells(lLastRow, iLastCol))

                    xlWsForwards.Name = xlWsOld.Name
                    xlRngOld.Copy
                    Set xlRngForwards = xlWsForwards.Cells(1, 1)
                    xlRngForwards.PasteSpecial Paste:=xlPasteValues
                    xlWsForwards.Columns.AutoFit

                    xlWsForwards.Cells(1, 1).Select

                    Set xlWsForwards = Nothing

                End If
            Next xlWsOld
0
votes

skip creating the new workbook, and copy all the sheets in one go.

xlWbOld.Sheets(Array("ForwardPrices", "ForwardVolatilities", "ForwardReturns", "ForwardCorrelations")).Copy
Set xlWb = ActiveWorkbook

this way copying creates the new workbook which becomes ActiveWorkbook. then you can assign it to a workbook object, and reference it by that name later on