saves in xlsx format instead of PDF

[KlausW]

Hello everyone, can anyone help to change this VBA code so that it saves in xlsx format instead of PDF.
Any help will be appreciated.

Best regards Klaus W

Sub Gem_og_Send_Klik()

'Varriables
Dim SaveRng As Range
Dim pdfname As String
Dim path As String

'Setting range to be saved
Set SaveRng = Range("A1:j42")

'path

    path = Range("S9")
    Filename = Range("K9")
    
    ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=path & Filename & ".pdf", _
    OpenAfterPublish:=False, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    Quality:=xlQualityStandard

Find_and_Open_Product_Workbook
   
   
End Sub

 

[Joe4]

If your turn on your Macro Recorder, and do a "SaveAs", and choose the "xlsx" option, you will see the code that you need. However, you would need to do that in another workbook, as you cannot save any file that contains VBA/Macros to an "xlsx" file, which is a VBA/Macro free file, by definition. Files with VBA code would need to be saved with something like an "xlsm" or "xlsb" extension.

 

[KlausW]

If your turn on your Macro Recorder, and do a "SaveAs", and choose the "xlsx" option, you will see the code that you need. However, you would need to do that in another workbook, as you cannot save any file that contains VBA/Macros to an "xlsx" file, which is a VBA/Macro free file, by definition. Files with VBA code would need to be saved with something like an "xlsm" or "xlsb" extension.

Good morning Joe4 I can't get it to run. KW

 

[MARK858]

Try the below (assuming you have a valid filepath in S9 ending with a path separator)

Sub Gem_og_Send_Klik2()

    'Varriables
    Dim Filename  As String
    Dim path As String

    'Setting range to be saved
    'Set SaveRng = Range("A1:j42")

    'path

    path = Range("S9")
    Filename = Range("K9")
    
    Application.DisplayAlerts = False

    ActiveWorkbook.SaveAs path & Filename & ".xlsx", 51

    Application.DisplayAlerts = True

    Find_and_Open_Product_Workbook
   
   
End Sub

 

[KlausW]

Try the below (assuming you have a valid filepath in S9 ending with a path separator)

Sub Gem_og_Send_Klik2()

    'Varriables
    Dim Filename  As String
    Dim path As String

    'Setting range to be saved
    'Set SaveRng = Range("A1:j42")

    'path

    path = Range("S9")
    Filename = Range("K9")
   
    Application.DisplayAlerts = False

    ActiveWorkbook.SaveAs path & Filename & ".xlsx", 51

    Application.DisplayAlerts = True

    Find_and_Open_Product_Workbook
  
  
End Sub

Hi MARK858 it copies the file, but what I would like it to do is to copy the sheet based on the name that is in cell A3. And preferably range A1:J42.

 

[MARK858]

Hi MARK858 it copies the file

It saves the file with a new name and file format, not copy the file

sheet based on the name that is in cell A3

The name in your original code was in K9, change that to A3

You can't save a sheet or a range, you can only save a workbook.
If you want a separate copy then you need to save your Xlsm/Xlsb first then when you run the macro it will create a separate XLSX file or copy the sheet or range to a new workbook and save that workbook (again not a sheet or a range)

 

[KlausW]

It saves the file with a new name and file format, not copy the file

The name in your original code was in K9, change that to A3

You can't save a sheet or a range, you can only save a workbook.
If you want a separate copy then you need to save your Xlsm/Xlsb first then when you run the macro it will create a separate XLSX file or copy the sheet or range to a new workbook and save that workbook (again not a sheet or a range)

Hi MARK858 I see what you mean. But I change the cell as I was hoping to make it work but it doesn't. I close the question and make a new and more understandable one. I really just have to copy the sheet to a new workbook, and name it from cell A3. And save it as an xlsx file. Thanks for the help, best regards Klaus W