• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Workbook doesn(t want to save

Belleke

Well-Known Member
I have this code
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

Excel Ninja
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 :

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

Marc L

Excel Ninja
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

Excel Ninja
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
 

NARAYANK991

Excel Ninja
Hi ,

This works :
Code:
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

Excel Ninja
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
 
Top