Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy all sheets to a new workbook | Setting up and Configuration of Excel | |||
Copy several sheets from one workbook into another at the same tim | Excel Discussion (Misc queries) | |||
selecting a single sheet from a volume of sheets in a workbook | Excel Worksheet Functions | |||
copy workbook sheets? | Setting up and Configuration of Excel | |||
copy a workbook from other workbook with lot of sheets wit... | Excel Discussion (Misc queries) |