• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 835
  • Last Modified:

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

0
etech0
Asked:
etech0
  • 2
  • 2
  • 2
  • +1
2 Solutions
 
NorieCommented:
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
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
NorieCommented:
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
 
kgerbChief 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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now