How to save specific hidden sheets to a new workbook?

[phyxius117]

Hello all,

I have made the following VBA codes to save specific sheets of a workbook to a new workbook. However when I hide the sheets it no longer works. I want to keep those sheets hidden in the current workbook but not in the new workbook.

Here is the code:

Sub Export()

Dim DCname As String
    DCname = ActiveWorkbook.Sheets("Reference").Range("R2").Value

Dim FiscalYear As String
    FiscalYear = ActiveWorkbook.Sheets("Reference").Range("R8").Value

Dim Period As String
    Period = ActiveWorkbook.Sheets("Reference").Range("R11").Value

Dim FileName As String
    FileName = DCname & "_SQL Data_FY" & FiscalYear & "_P" & Period

Worksheets(Array("Route Totals", "Stops")).Copy

    With ActiveWorkbook.Worksheets("Route Totals")
    .UsedRange.Value = .UsedRange.Value
    End With
    
    With ActiveWorkbook.Worksheets("Stops")
    .UsedRange.Value = .UsedRange.Value
    End With
    
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & FileName, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

End Sub

Thank you all for any help I can get!!

 

[Jeffrey Mahoney]

How about this?

Sub Export()

  Dim DCname As String
    DCname = ActiveWorkbook.Sheets("Reference").Range("R2").Value

  Dim FiscalYear As String
    FiscalYear = ActiveWorkbook.Sheets("Reference").Range("R8").Value

  Dim Period As String
    Period = ActiveWorkbook.Sheets("Reference").Range("R11").Value

  Dim FileName As String
    FileName = DCname & "_SQL Data_FY" & FiscalYear & "_P" & Period

  Dim Sht As Worksheet
  Worksheets(Array("Route Totals", "Stops")).Copy
  For Each Sht In ActiveWorkbook.Worksheets
    Sht.Visible = xlSheetVisible
  Next Sht

  With ActiveWorkbook.Worksheets("Route Totals")
    .UsedRange.Value = .UsedRange.Value
  End With
    
  With ActiveWorkbook.Worksheets("Stops")
    .UsedRange.Value = .UsedRange.Value
  End With
    
  ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & FileName, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

End Sub