Workbook doesn(t want to save

[Belleke]

I have this code

Sub Belle()
Range("A2:R50").Copy
Workbooks.Add
With ActiveSheet
    .PasteSpecial
    .PasteSpecial xlPasteColumnWidths
End With
Path = "C:\test\"
Filename = ActiveSheet.Range("P7")
ActiveWorkbook.SaveAs Filename:=Path & Filename & ".xlsm", FileFormat:=52
ActiveWindow.Close
End Sub

I get an error at this line
ActiveWorkbook.SaveAs Filename:=Path & Filename & ".xlsm", FileFormat:=52
What am I missing?
Thanks

 

[NARAYANK991]

Hi ,

Instead of using the enumeration code of 52 , have you tried using the actual VBA code of xlOpenXMLWorkbookMacroEnabled ?

So your line of code would be :

ActiveWorkbook.SaveAs Filename:=Path & Filename & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled

Narayan

 

[Belleke]

Tried it before.
It gives me error 1004

 

[Marc L]

If the workbook is 'ThisWorkbook' - the workbook containing this procedure - then​

it's very not a good idea to proceed like this ! Better is to use the SaveCopyAs method.​

​

Or it's just a bad pathfile name …​

​

 

[NARAYANK991]

Hi ,

Have you tried displaying the filename ? What does it show ? Does the folder exist ?

Have you tried changing the file format to 51 and seeing whether that works ?

If you can upload your workbook , it will help.

Narayan

 

[Belleke]

example

 

[Belleke]

problem solved, stupid me
Thanks Narayank991

 

[NARAYANK991]

Hi ,

This works :

Sub Belle()
    Path = "F:\Excel File Downloads\"                                  ' Change this to suit
    File_name = Path & ThisWorkbook.ActiveSheet.Range("P7") & ".xlsm"

    Range("A2:R50").Copy

    Workbooks.Add

    With ActiveSheet
         .PasteSpecial
         .PasteSpecial xlPasteColumnWidths
    End With

    ActiveWorkbook.SaveAs File_name, 52

    ActiveWindow.Close
End Sub

Narayan

 

[NARAYANK991]

Hi ,

The real problem is this line :

Range("A2:R50").Copy

The origin for the copy is A2.

When you copy to a new workbook and a new worksheet , since nothing is specified , the origin for the PasteSpecial is A1.

Which is why P7 in your original worksheet is now in P6 in the newly added worksheet. That is the reason P7 in the newly added worksheet is blank.

Narayan

 

[Belleke]

Got it, thanks for the explanation.