Array Ignorance 1

[RonRepp]

Hi all:

In Excel, I want to programmatically group and move all the sheets in a workbook to a new book; however, my ability to comprehend arrays is minimal in VB.

This is what the code would look like (using the macro recorder).

Sheets(Array("62321MAIL", "Template")).Select
    Sheets("Template").Activate
    Sheets(Array("62321MAIL", "Template")).Copy

The number of sheets will vary, and I don't know how to concatenate an array.

Any help would be greatly appreciated.

Thanks,



Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

 

[SkipVought]

Hi,

Rather than that, just copy each sheet to the new workbook. Procedure CopySheets is in a module in the workbook containing the sheets you want to move.

Sub CopySheets()
    Dim wbNew As Workbook, ws As Worksheet
    
    Set wbNew = Workbooks.Add
    
    For Each ws In ThisWorkbook.Worksheets
        ws.Copy Before:=wbNew.Sheets(1)
    Next
End Sub

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[RonRepp]

Skip:

I had tried something similar, but it kept copying them to new workbooks.

Thanks a star.

Ron



Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.