Excel VBA copy all sheets to new workbook

Hi!
In Excel VBA, I want to loop through all worksheets in one workbook, and copy then to another.

This is what I have so far; when I run it I get an error.

Dim FName As String
Dim MacroBook As Workbook
Dim OutputBook As Workbook

Set MacroBook = ActiveWorkbook
FName = Sheets("INSTRUCTIONS").Range("C2").Value & " " & Sheets("INSTRUCTIONS").Range("C3").Value
If FName = " " Then MsgBox ("Enter the Vendor Name and Product Line"): Exit Sub
Set OutputBook = Workbooks.Add
Application.DisplayAlerts = False
OutputBook.SaveAs Filename:="F:\buying\catalog production\2013\" & FName & ".xls"

MacroBook.Sheets(2).Copy OutputBook(After:=wbOpen.Sheets("Sheet1"))

Open in new window

LVL 10
etech0Asked:
Who is Participating?
 
NorieConnect With a Mentor VBA ExpertCommented:
Which workbooks do you want to exclude?

This code will loop through the worksheets and only copy certain sheets

Dim ws As Worksheet

Dim arrWS()
Dim I As Long

For Each ws In ThisWorkbook.Worksheets
       If ws.Name <> "Sheet3" Then
            ReDim Preserve arrWS(I)
            arrWS(I) = ws.Name

            I = I + 1
       End If
       
Next ws

ThisWorkbook.Worksheets(arrWS).Copy

Set OutputBook = ActiveWorkbook

OutputBook.SaveAs Filename:="F:\buying\catalog production\2013\" & FName & ".xls"

Open in new window

0
 
NorieVBA ExpertCommented:
If you want to copy all the sheets of a workbook you can use this.
ThisWorkbook.Sheets.Copy

Open in new window


The newly created workbook is now the active workbook so you can add this after the copy.
Set OutpotBook = ActiveWorkbook

OutputBook.SaveAs Filename:="F:\buying\catalog production\2013\" & FName & ".xls

Open in new window

0
 
Anthony BerenguelCommented:
Because you want all of the tabs to go into a new workbook, I'm thinking another way to do this is to save the workbook as a new workbook.

Sub saveAsNewWB()

    ActiveWorkbook.SaveAs Filename:= _
        "yourNewWorkbooksFullFilePathGoesHere", _
        FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

Open in new window

0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Anthony BerenguelCommented:
Sorry for submitting a different solution. imnorie's solution wasn't visible at the time I tried to submit my solution. If I would have seen his solution I wouldn't have offered mine.
0
 
kgerbChief EngineerCommented:
Try this.  I think it will do what you want.
Sub CopyAllWorksheets()
Dim FName As String
Dim MacroBook As Workbook
Dim OutputBook As Workbook
Set MacroBook = ThisWorkbook
'FName = Sheets("INSTRUCTIONS").Range("C2").Value & " " & Sheets("INSTRUCTIONS").Range("C3").Value
'If FName = " " Then MsgBox ("Enter the Vendor Name and Product Line"): Exit Sub
Worksheets.Copy
Set OutputBook = ActiveWorkbook
OutputBook.SaveAs Filename:="F:\buying\catalog production\2013\" & FName, FileFormat:=xlWorkbookNormal
MacroBook.Activate
End Sub

Open in new window

Kyle
0
 
etech0Author Commented:
The reason I need a loop is because in certain cases, I want the macro to skip a sheet.

@aebea: another reason your code wouldn't work here is because I need to keep both files open for the duration of the macro, which will be much longer when it's finished.
0
 
kgerbConnect With a Mentor Chief EngineerCommented:
This one loops.
Sub CopyAllWorksheets()
Dim FName As String, ws As Worksheet
Dim MacroBook As Workbook
Dim OutputBook As Workbook
Set MacroBook = ThisWorkbook
FName = Sheets("INSTRUCTIONS").Range("C2").Value & " " & Sheets("INSTRUCTIONS").Range("C3").Value
If FName = " " Then MsgBox ("Enter the Vendor Name and Product Line"): Exit Sub
Application.SheetsInNewWorkbook = 1
Set OutputBook = Workbooks.Add
Application.SheetsInNewWorkbook = 3
With OutputBook
    For Each ws In MacroBook.Worksheets
        ws.Copy After:=.Sheets(.Sheets.Count)
    Next ws
    Application.DisplayAlerts = False
    .Sheets(1).Delete
    Application.DisplayAlerts = True
End With
OutputBook.SaveAs Filename:="F:\buying\catalog production\2013\" & FName, FileFormat:=xlWorkbookNormal
MacroBook.Activate
End Sub

Open in new window

Kyle
0
 
etech0Author Commented:
Thanks!

imnorie: your code did the trick.

some points to kgerb as well; your code seems almost the same.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.