VBA to save hidden sheet in new file

kcmuppet

Active Member
Joined
Nov 2, 2005
Messages
316
Office Version
  1. 365
  2. 2010
Hi,

I'm trying to save a hidden sheet as a new file but struggling to get my code working - could somebody possibly help me understand why this is generating an error: 1004

VBA Code:
Sub SaveMyToFile2()
    Dim newFName As String
    Dim newWSName As String
 

    newFName = Application.GetSaveAsFilename(InitialFileName:=Worksheets("My Data").Range("B3"), filefilter:=" Excel Macro Free Workbook (*.xlsx), *.xlsx,")
           
    newWSName = Right(Worksheets("My Data").Range("B3"), 22)

    With Worksheets("My Data")
        .Visible = xlSheetVisible
        .Range("A1:H41").Select
        .Selection.Copy
    End With
    
    Workbooks.Add
    Application.ActiveSheet.Name = newWSName
    Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme
    Selection.PasteSpecial Paste:=xlPasteColumnWidths
    ActiveSheet.Cells.EntireRow.AutoFit
    ActiveWorkbook.SaveAs Filename:=newFName, FileFormat:=51, CreateBackup:=False
            
    Worksheets("My Data").Visible = xlSheetHidden
        
End Sub

Many thanks
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

shknbk2

Active Member
Joined
Mar 5, 2016
Messages
386
Office Version
  1. 365
Platform
  1. Windows
Try this:

Added the '.Select' to get rid of the 1004 error.
Added 'Application' to the copy line since it isn't a worksheet method.
Added 'ActiveWorkbook.Close' to close the new workbook since an error will generate with that workbook not having the My Data sheet. If you don't want to close the new workbook, you will have to switch back to the workbook with My Data worksheet.

VBA Code:
Sub SaveMyToFile2()
    Dim newFName As String
    Dim newWSName As String

    newFName = Application.GetSaveAsFilename(InitialFileName:=Worksheets("My Data").Range("B3"), filefilter:=" Excel Macro Free Workbook (*.xlsx), *.xlsx,")
           
    newWSName = Right(Worksheets("My Data").Range("B3"), 22)

    With Worksheets("My Data")
        .Visible = xlSheetVisible
        .Select
        .Range("A1:H41").Select
        Application.Selection.Copy
    End With
    
    Workbooks.Add
    Application.ActiveSheet.Name = newWSName
    Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme
    Selection.PasteSpecial Paste:=xlPasteColumnWidths
    ActiveSheet.Cells.EntireRow.AutoFit
    ActiveWorkbook.SaveAs Filename:=newFName, FileFormat:=51, CreateBackup:=False
    ActiveWorkbook.Close
    
    Worksheets("My Data").Visible = xlSheetHidden
        
End Sub
 
Solution

kcmuppet

Active Member
Joined
Nov 2, 2005
Messages
316
Office Version
  1. 365
  2. 2010
Many thanks. Those changes make it work. Why do I need the .Select before the .Range().Select ? Doesn't the latter do the selecting?
 

shknbk2

Active Member
Joined
Mar 5, 2016
Messages
386
Office Version
  1. 365
Platform
  1. Windows
I'm not sure of the mechanics of Excel, but you just can't. For example, if Sheet1 and Sheet2 are both visible and Sheet1 is active, the code throws an error if you try Worksheets("Sheet2").Range("A1").Select. However, if Sheet2 is active, this same code works just fine.
 

kcmuppet

Active Member
Joined
Nov 2, 2005
Messages
316
Office Version
  1. 365
  2. 2010
Thanks that's very helpful. Does it make any difference where the code is? (Specifically, the sheet to be copied, or 'workbook', or as a module?
 

shknbk2

Active Member
Joined
Mar 5, 2016
Messages
386
Office Version
  1. 365
Platform
  1. Windows
I don't think it matters whether it is in the Sheet-to-be-copied code or in a Module.
 

Forum statistics

Threads
1,141,777
Messages
5,708,461
Members
421,571
Latest member
ChaosPup

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top