Excel Copy Paste Special with selected sheets not working

Question

This first function produces static pages in a new spreadsheet and runs through all the sheets without issue.

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

The following code doesn't work when I attempt it with a few sheets selected; I get a variety of errors depending on what I've tried for curDynSheet.select and curDynSheet.activate. The Paste Special function of Range class failed error 1004 is returned by the code below. Though I'm not sure why, I assume that the copy approach was the first to fail.

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 experimented with a variety of approaches to handling ActiveWindow.SelectedSheets. The SheetArray appears to be filling out, and the initial new sheet tab is generated appropriately with the proper name; the only thing that isn't working properly is the cut and paste special things.

Answer 1

  Application.DisplayStatusBar = True

Is cancelling your copy command, so there is nothing to paste, therefore the paste error.