Move a copy of worksheets

[primfran]

I am looking for a piece of code to copy an array of worksheets to a new workbook.

Thanks.

[mvidas]

Hi primfran,

Try:

Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Copy

Just enclose the sheet names in the Array(), and using .Copy by itself on the Sheets will create a new workbook for them.

Matt

[Jacob Hilderbrand]

Try this.

Option Explicit

Sub CopySheets()
Dim WkbOld          As Workbook
Dim WkbNew          As Workbook
Dim SheetArray()    As String
Dim i               As Long
Application.ScreenUpdating = False
    Application.DisplayAlerts = False
ReDim SheetArray(1 To 3)
    SheetArray(1) = "Sheet1"
    SheetArray(2) = "Sheet2"
    SheetArray(3) = "Sheet3"
Set WkbOld = ActiveWorkbook
    Set WkbNew = Workbooks.Add
For i = 1 To WkbNew.Sheets.Count - 1
        WkbNew.Sheets(2).Delete
    Next
    WkbNew.Sheets(1).Name = "DelMe"
For i = 1 To UBound(SheetArray)
        WkbOld.Sheets(SheetArray(i)).Copy After:= _
        WkbNew.Sheets(WkbNew.Sheets.Count)
    Next i
WkbNew.Sheets("DelMe").Delete
Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

[primfran]

Perfect. Thanks.

How do you select a range of columns over an array of worksheets if the starting column changes?

[Jacob Hilderbrand]

How do you determine what the starting column is?

[primfran]

It is entered into an input box, then I use the find function to locate the column with that cell in it.

[Zack Barresse]

Can you post all relevant code? If we can see the whole picture it will be much easier to ascertain a valid and working solution for you.

[primfran]

Today = Format(InputBox("Enter the report date", "Enter Date"), "mm/dd/yyyy")
Cells.Find(Today).Activate
Col = ActiveCell.Column

I then want to hide all columns after the column with the cell that has the value in it.

[Jacob Hilderbrand]

Try this.

Sheets("Sheet1").Range(Cells(1, Col), _
Cells(65536, 256)).EntireColumn.Hidden = True

[primfran]

That worked. Thanks.

[Jacob Hilderbrand]

You're Welcome

Take Care