VBA to save hidden sheet in new file

[kcmuppet]

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

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

 

[shknbk2]

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.

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

 

[kcmuppet]

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]

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]

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]

I don't think it matters whether it is in the Sheet-to-be-copied code or in a Module.