Excel Copy Paste Special with selected sheets not working

0 votes

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.

Feb 5, 2023 in Others by Kithuzzz
• 38,010 points
337 views

1 answer to this question.

0 votes
  Application.DisplayStatusBar = True

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

answered Feb 5, 2023 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
0 answers

SEOPress plugin not working with Wordpress 5.7

An error of type E_ERROR was caused ...READ MORE

Feb 14, 2022 in Others by Kichu
• 19,050 points
709 views
0 votes
0 answers

Android facebook login not working with installed Facebook app

Session.openActiveSession(this, true, new Session.StatusCallback() { ...READ MORE

Jun 22, 2022 in Others by nisha
• 2,210 points
400 views
0 votes
1 answer

Excel create an Index on the first sheet with links to subsequent sheets

This seems to be a potential duplicate ...READ MORE

answered Oct 22, 2022 in Others by narikkadan
• 63,420 points
850 views
0 votes
1 answer

Tab Delimited Text Copy Paste To Excel

By switching the element my text was ...READ MORE

answered Oct 29, 2022 in Others by narikkadan
• 63,420 points
353 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
877 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,183 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
481 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
731 views
0 votes
1 answer
0 votes
1 answer

Force download excel file not working on web server

Change the content type to Content-Type: application/vnd.ms-excel Or try ...READ MORE

answered Oct 11, 2022 in Others by narikkadan
• 63,420 points
1,760 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP