Create a Copy of Sheets from existing (unknown named) workbook into a new (unknown named) workbook

Question

I have an existing workbook that will be used by multiple users (who will name the workbook uniquely - I can set one Workbook Codename if needed though, though don't know how to do this?).

I need to create a macro that opens a new workbook (which presumably I won't know the name of? as it could be 'Book1', 'Book2', 'Book3' etc?), then returns to the original workbook where the macro is stored, and copies several (can do one at a time if needed) sheets (that I DO know the names of these sheets) and pastes them as new sheets into the new workbook that I created at the start. The macro does not need to Save the file (in fact it's preferable that it doesn't as I want the user to save the new workbook wherever is most convenient for the user).

I have attempted to show what the macro would do, showing the obvious problem that I do not know the names of the workbooks I am creating/copying from/pasting into.

Any help, much appreciated!

Sub CopySheetintoNewWorkbook()

'Macro opens new / blank workbook (name unknown?)'
    Workbooks.Add

'Macro goes back to original workbook where macro is saved (of which the name is unknown to the macro - i.e., users can and will change it)'
    Windows("UnknownWorkbookName-1").Activate

'Macro goes to a sheet which can be named and will be known, so this is no problem'
    Sheets("KnownSheet").Select
'Macro creates a copy of the sheet and pastes it as a new sheet within the new, unknown named workbook'
    Application.CutCopyMode = False
    Sheets("KnownSheet").Copy Before:=Workbooks("UnknownWorkbookName-2").Sheets(1)

End Sub

You can use thisworkbook.name to get the name of the workbook you are running the macro from and use that to reference later. similarly you can force assign a name after workbook.add — Slubee
Right after Workbooks.Add, do something like Dim newWB as Workbook, then Set newWB = ThisWorkbook. Then you can use newWB.Sheets("Sheet1").Range("A1:A2").Value or whatever. Or, Dim newWB as String, newWB = Thisworkbook.name, then you would refer to it like Workbooks(newWB).Sheets("Sheet1")... — BruceWayne

Gary's Student Gary's Student · Accepted Answer · 2016-02-19T17:28:43

We want to copy Sheet1 and Sheet2.

This relies on a tiny trick:

Sub qwerty()
    Dim wb1 As Workbook, wbNEW As Workbook
    Set wb1 = ActiveWorkbook
    Sheets("Sheet1").Copy
    Set wbNEW = ActiveWorkbook
    wb1.Sheets("Sheet2").Copy after:=wbNEW.Sheets(1)
End Sub

When the first .Copy is performed, a new workbook is created and it becomes the ActiveWorkbook ........the rest is easy.

EDIT#1:

If we have a group of sheets to be copied, then we can create an array of sheet names and loop through the array, copying one sheet at a time:

Sub qwerty()
    Dim wb1 As Workbook, wbNEW As Workbook
    Dim ary() As String, s As String, i As Long

    s = "Larry,Moe,Curly"
    ary = Split(s, ",")
    Set wb1 = ActiveWorkbook
    i = 1

    For Each a In ary
        If i = 1 Then
            Sheets(a).Copy
            Set wbNEW = ActiveWorkbook
        Else
            wb1.Sheets(a).Copy after:=wbNEW.Sheets(1)
        End If
        i = 2
    Next a

    wbNEW.Activate
End Sub

Create a Copy of Sheets from existing (unknown named) workbook into a new (unknown named) workbook

1 Answers