Problem with Macro

[RccScln]

Hello everyone.
I built few months ago this macro


Sub aggiornacella()


a = Sheets("Lista").Range("F3").Value
b = Sheets("Lista").Range("F4").Value
c = ""


For i = a To b

ThisWorkbook.Sheets("Lista").Cells(3, 3).Value = i
Application.CalculateFull
c = Sheets("Lista").Range("C4").Value

ThisWorkbook.Sheets("A3 ONE PAGER").Copy


ChDir "C:\Users\cge01522\Desktop\small56down"


'ActiveWorkbook.SaveAs Filename:=c, FileFormat:=51

ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
i & c _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False


ActiveWorkbook.SaveAs Filename:=i & c, FileFormat:=51

ActiveWorkbook.Close
Next
End Sub

If I try to run the macro an alert reports this error:
File not found:
'C:\Users\cg08644\AppData\local\temp\VB7DB1.tmp


Then MVB says:
Run Time error 1004
Copy method of Worksheet class failed:

If I debug the code underlined in yellow is:

ThisWorkbook.Sheets("A3 ONE PAGER").Copy


Why and what I should change?

Thank you so much in advance

Rocco

 

[rlv01]

How many iterations does your For i = a to b loop do? You might be doing to many SaveAs operations. See this Microsoft support article for details

https://support.microsoft.com/en-us...matically-causes-run-time-error-1004-in-excel

 

[RccScln]

How many iterations does your For i = a to b loop do? You might be doing to many SaveAs operations. See this Microsoft support article for details

https://support.microsoft.com/en-us...matically-causes-run-time-error-1004-in-excel

Sorry but I am not very handy with Macro. Could you please be more specific?

Thank you so much

 

[RccScln]

Tho the interval does 20 interactions I think
The thing is that it used to work. Don't know why it does not right now

 

[rlv01]

Does it help if you replace this

ActiveWorkbook.SaveAs Filename:=I & c, FileFormat:=51

with this?

ActiveWorkbook.SaveCopyAs Filename:=I & c & ".xlsx"

Sub aggiornacella()
    Dim a, b, c
    Dim I As Long

    a = Sheets("Lista").Range("F3").Value
    b = Sheets("Lista").Range("F4").Value
    c = ""

    For I = a To b
        ThisWorkbook.Sheets("Lista").Cells(3, 3).Value = I
        Application.CalculateFull
        c = Sheets("Lista").Range("C4").Value

        ThisWorkbook.Sheets("A3 ONE PAGER").Copy

        ChDir "C:\Users\cge01522\Desktop\small56down"

        ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=I & c _
                                         , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

        'ActiveWorkbook.SaveAs Filename:=I & c, FileFormat:=51
        ActiveWorkbook.SaveCopyAs Filename:=I & c & ".xlsx"


        'ActiveWorkbook.Close
        ActiveWorkbook.Close SaveChanges:=False
    Next
End Sub

 

[RccScln]

Unfortunately I get the same error

what else can I do and what type of error is that?

thank you
rocco

 

[rlv01]

Does it help if you explicitly create the new workbook you are adding "A3 ONE PAGER" to?

Sub aggiornacella()
    Dim a, b, c
    Dim I As Long

    a = Sheets("Lista").Range("F3").Value
    b = Sheets("Lista").Range("F4").Value
    c = ""

    For I = a To b
        ThisWorkbook.Sheets("Lista").Cells(3, 3).Value = I
        Application.CalculateFull
        c = Sheets("Lista").Range("C4").Value

        Workbooks.Add Template:=xlWBATWorksheet
        ThisWorkbook.Sheets("A3 ONE PAGER").Copy Before:=ActiveWorkbook.Sheets(1)

        ChDir "C:\Users\cge01522\Desktop\small56down"

        ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=I & c _
                                         , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

        'ActiveWorkbook.SaveAs Filename:=I & c, FileFormat:=51
        ActiveWorkbook.SaveCopyAs Filename:=I & c & ".xlsx"

        'ActiveWorkbook.Close
        ActiveWorkbook.Close SaveChanges:=False
    Next
End Sub

 

[rlv01]

BTW, Over time, Excel can build up a lot of "excess baggage". If you are still using a 32-bit* version of Excel, try using Rob Bovey's excellent and free code cleaner utility. This is has been one of my go-to utilities for years. Sometimes it can resolve unexplainable errors in macros which have suddenly throwing errors after having run w/o trouble for years.

http://www.appspro.com/Utilities/CodeCleaner.htm


*Does not matter if Windows is 64bit. Sadly, Code Cleaner is not compatible with the 64bit version of Office

 

[RccScln]

I am running a 64 bit version. Probably when I used this macro I used a 32 bit version (I changed my PC). How could I fix it aside chaning excel version?

 

[rlv01]

Before code cleaner, I used to do something like this to manually "clean" a workbook.

1. Save a backup copy of your workbook, just in case.
2. With each code module, export the code to a text file.
(The above includes any place your workbook contains code, not just code modules: Forms, worksheet, workbook, etc)
3. Save the workbook as a macro-free workbook (.xlsx)
4. Close the workbook.
5. Reopen the macro-free workbook
6. Re-import all the vba code you exported in Item 2
7. Save the workbook as a macro-enabled workbook (.xlsm)
8. Test