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 :beerchug:

Take Care