This first routine works fine to run through all of the sheets, and creates static pages in an new spreadsheet
Sub StaticSheets()
Dim wbStatic As Workbook, wbDynamic As Workbook, DynamicName As Variant, _
DynamicPath As Variant, StaticName As Variant, curSheetName As String, curStaticSheet As Worksheet, curDynSheet As Worksheet
Dim wbs As Workbooks
Application.ScreenUpdating = False
Set wbDynamic = ActiveWorkbook
Set fso = CreateObject("Scripting.FileSystemObject")
Set wbs = Workbooks()
DynamicPath = wbDynamic.Path
DynamicName = fso.GetBaseName(wbDynamic.Name)
StaticName = DynamicName & "-Static"
Set wbStatic = Workbooks.Add
For Each curDynSheet In wbDynamic.Sheets
curSheetName = curDynSheet.Name
wbStatic.Sheets.Add(After:=wbStatic.Sheets(wbStatic.Sheets.Count)).Name = curSheetName
curDynSheet.Activate
Range("A1:AZ400").SpecialCells(12).Copy 'Copy visible Cells only
With wbStatic.Worksheets(curSheetName).Range("A1")
.PasteSpecial xlPasteColumnWidths
.PasteSpecial xlPasteValuesAndNumberFormats
.PasteSpecial xlFormats
Application.CutCopyMode = False
End With
Next
wbStatic.SaveAs Filename:=DynamicPath & "\" & StaticName
Application.ScreenUpdating = True
End Sub
When I try the following code with just a few sheets selected, I get various errors depending on what I've tried for curDynSheet.select and curDynSheet.activate, but nothing is working. The code below is giving a 1004 Paste Special method of Range class failed. I'm guessing that the copy method failed first, but I'm not sure why.
Sub StaticSelectedSheets()
Dim wbStatic As Workbook, wbDynamic As Workbook, DynamicName As Variant, _
DynamicPath As Variant, StaticName As Variant, curSheetName As String, curStaticSheet As Worksheet, curDynSheet As Worksheet
Dim wbs As Workbooks
Dim sheetArray As Variant
Set wbDynamic = ActiveWorkbook
Set sheetArray = ActiveWindow.SelectedSheets
Set fso = CreateObject("Scripting.FileSystemObject")
Set wbs = Workbooks()
DynamicPath = wbDynamic.Path
DynamicName = fso.GetBaseName(wbDynamic.Name)
StaticName = DynamicName & "-Static"
Set wbStatic = Workbooks.Add
For Each curDynSheet In sheetArray
curSheetName = curDynSheet.Name
wbStatic.Sheets.Add(After:=wbStatic.Sheets(wbStatic.Sheets.Count)).Name = curSheetName
curDynSheet.Activate
curDynSheet.Select
curDynSheet.Range("A1:AZ400").SpecialCells(12).Copy 'Copy visible Cells only
Application.DisplayStatusBar = True
With wbStatic.Worksheets(curSheetName).Range("A1")
.PasteSpecial xlPasteColumnWidths
.PasteSpecial xlPasteValuesAndNumberFormats
.PasteSpecial xlFormats
Application.CutCopyMode = False
End With
Next
wbStatic.SaveAs Filename:=DynamicPath & "\" & StaticName
End Sub
I've tried multiple options with how ActiveWindow.SelectedSheets is handled. The SheetArray seems to be filling out, and I get the first new sheet tab created correctly with the right name...it's just the cut and paste special stuff that isn't working right.
