worksheet tab switching

[dunno in dallas]

Would love some help with a macro that automatically selects and copies all worksheets in a workbook and copies them to another workbook. Recording only yields something like worksheet("sheet1") and I want a macro that works regardless of the name and number of worksheets in the workbook to be copied. Thx. DiD

[Ivan F Moala]

On 2002-04-25 20:45, dunno in dallas wrote:
Would love some help with a macro that automatically selects and copies all worksheets in a workbook and copies them to another workbook. Recording only yields something like worksheet("sheet1") and I want a macro that works regardless of the name and number of worksheets in the workbook to be copied. Thx. DiD

Try something along this line;

Dim Wsht As Worksheet



For Each Wsht In ThisWorkbook.Sheets

    Wsht.Activate

    Cells.Copy

    Windows("yourworkbookname").Activate

    Sheets.Add

    ActiveSheet.Paste

    ThisWorkbook.Activate

Next

[Colo]

Hi. Pls Try this.

Sub CopyAllSheets()
Sheets.Copy
End Sub

[dunno in dallas]

Thanks Colo & Ivan. Will try in the morning.

On 2002-04-25 20:45, dunno in dallas wrote:
Would love some help with a macro that automatically selects and copies all worksheets in a workbook and copies them to another workbook. Recording only yields something like worksheet("sheet1") and I want a macro that works regardless of the name and number of worksheets in the workbook to be copied. Thx. DiD
[/quote]

[don't know in dallas]

Thanks guys. Here's what I ended up using. This macro combines all worksheets for all open workbooks onto one new workbook and also closes all of the other workbooks:

Sub Macro1()

Dim AWb As String
Dim macroWb As String
Dim copyWb As String

Application.DisplayAlerts = False
macroWb = ThisWorkbook.Name

'create new wkbk that has only 1 wksht. (named "Sheet1")
Workbooks.Add
Sheets.Add
copyWb = ActiveWorkbook.Name
For Each Worksheet In ActiveWorkbook.Sheets
Worksheet.Activate
If Worksheet.Name <> "Sheet1" Then
ActiveWindow.SelectedSheets.Delete
End If
Next

'copy all wksht's for all wkbk's (except macro wkbk & new wkbk)
'onto new wkbk. Also, close copied wrkbk.
For Each Workbook In Workbooks
AWb = ActiveWorkbook.Name
If AWb <> macroWb Then
If AWb <> copyWb Then
Sheets.Copy Before:=Workbooks(copyWb).Sheets(1)
Workbooks(AWb).Activate
ActiveWindow.Close
End If
End If
ActiveWindow.ActivateNext
Next Workbook

'run same code again because one wkbk doesn't copy for some reason.
For Each Workbook In Workbooks
AWb = ActiveWorkbook.Name
If AWb <> macroWb Then
If AWb <> copyWb Then
Sheets.Copy Before:=Workbooks(copyWb).Sheets(1)
Workbooks(AWb).Activate
ActiveWindow.Close
End If
End If
ActiveWindow.ActivateNext
Next Workbook

'In new wrkbk, delete wksht "sheet1" leaving only copied wrksht's.
Workbooks(copyWb).Activate
For Each Worksheet In ActiveWorkbook.Sheets
Worksheet.Activate
If Worksheet.Name = "Sheet1" Then
ActiveWindow.SelectedSheets.Delete
End If
Next

'close macro wkbk, leaving only the new wrkbk open.
Workbooks(macroWb).Activate
ActiveWindow.Close

End Sub