VBA: selecting sheets and copy them to a workbook
 

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


--
--
http://www.reparco.com - Die kostenlose, persönliche Startseite

selecting sheets and copy them to a workbook
 

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

--
Regards,
Tom Ogilvy



"Stephan Otto" wrote in message
...
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


--
--
http://www.reparco.com - Die kostenlose, persönliche Startseite

selecting sheets and copy them to a workbook
 

Tom Ogilvy schrieb:

How do you decide which sheets


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).


Thanks, I will try it this evening. This computer runs Linux...

--
--
http://www.reparco.com - Die kostenlose, persönliche Startseite

selecting sheets and copy them to a workbook
 

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

--
Regards,
Tom Ogilvy

"Stephan Otto" wrote in message
...
Tom Ogilvy schrieb:

How do you decide which sheets


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).


Thanks, I will try it this evening. This computer runs Linux...

--
--
http://www.reparco.com - Die kostenlose, persönliche Startseite

selecting sheets and copy them to a workbook
 

Thank you. As I told you, I will try it this evening...


Tom Ogilvy schrieb:

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

--
Regards,
Tom Ogilvy

"Stephan Otto" wrote in message
...
Tom Ogilvy schrieb:

How do you decide which sheets


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).


Thanks, I will try it this evening. This computer runs Linux...

--
--
http://www.reparco.com - Die kostenlose, persönliche Startseite

--
--
http://www.reparco.com - Die kostenlose, persönliche Startseite

selecting sheets and copy them to a workbook
 

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


Tom Ogilvy schrieb:

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

--
Regards,
Tom Ogilvy

"Stephan Otto" wrote in message
...
Tom Ogilvy schrieb:

How do you decide which sheets


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).


Thanks, I will try it this evening. This computer runs Linux...

--
--
http://www.reparco.com - Die kostenlose, persönliche Startseite

--
--
http://www.reparco.com - Die kostenlose, persönliche Startseite