VBA: selecting sheets and copy them to a workbook

  • Thread starter Thread starter Stephan Otto
  • Start date Start date
S

Stephan Otto

Hello everybody,

I would like to select some sheets and copy them to a different workbook. If
I use the following it works fine:

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

But the problem is, that I must have the array in a different way. The
sheets, that must be copied, are always different. I need something like:


Dim mysheets As ???
....
mysheets(0) = "Sheet1"
mysheets(1) = "Sheet2"
mysheets(2) = "Sheet3"
....
Sheets(mysheets).Select
Sheets(mysheets).Copy
....

But I don't get it. What kind of variable must I use? Variant doesn't work.
Neither does Dim mysheets() As String.

Thanks in advance,
Stephan
 
How do you decide which sheets

Variant does work

Sub copysheets()
Dim list As Variant
ReDim list(0 To 2)
list(0) = "Sheet1"
list(1) = "Sheet2"
list(2) = "Sheet3"
Sheets(list).Copy

End Sub

worked fine for me. (also, no need to select them first).

If you are copying all sheets, then you can do

worksheets.copy
 
Tom said:
How do you decide which sheets
Click to expand...

By reading a specific column. All sheets, that are mentioned in that column,
should be copied...
Variant does work

Sub copysheets()
Dim list As Variant
ReDim list(0 To 2)
list(0) = "Sheet1"
list(1) = "Sheet2"
list(2) = "Sheet3"
Sheets(list).Copy

End Sub

worked fine for me. (also, no need to select them first).
Click to expand...

Thanks, I will try it this evening. This computer runs Linux...
 
As long as there will be at least two adjacent cells with sheet names:

Sub Copysheets1()
Dim varr As Variant
Dim rng As Range
Set rng = Range(Cells(1, 1), Cells(1, 1).End(xlDown))
varr = Application.Transpose(rng)
Debug.Print LBound(varr, 1), UBound(varr, 1)
Sheets(varr).Copy
End Sub
 
Hi Tom,

this one didn't work. But I used the other one, which you've posted before
This one works very well...

Thanks, Stephan
 
Back
Top