Save active sheet as PDF and new excel sheet, clear content

[Jnb99]

Hi everyone,

I need help with my code saving active sheet as PDF with file name as specific cell value.

File name must be combination of "F4" + "A14"
Range that must be saved: Sheet = "Estimate" and Range = (A1:G50)

Lastly cells (A23:A41) must "ClearContents" when vba is run

Currently I have this code, which save PDF fine, but thats where it stops.

Private Sub CommandButton1_Click()
Dim saveLocation As String
Dim rng As Range

saveLocation = "C:\Users\****\Desktop\" & Range("f4").Value & Range("a14").Value & ".pdf"
Set rng = Sheets("Estimate").Range("A1:g50")


rng.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=saveLocation

Worksheets("Estimate").Range("A23:a41").ClearContents
End Sub

Sub saveSheetWithoutFormulas()
 Worksheets("Estimate").Copy
    
   ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
    
    ActiveWorkbook.saveas Range("f4")

End Sub

Thanks

 

[Jnb99]

I need to apologize, its my own stupidity. I got it working.

I want it to save the excel file as a normal excel file, not a macro enabled one. Currently it is also saving the active workbook as the file name as per the description in the code. I would like it to save a copy of the sheet, and keep the active workbook as a template. Is that possible?

Now only this one left, can this be done?

 

[rlv01]

Run-time error 1004

Cant do that to a merged cell

No you cannot, which is why you should avoid merged cells when possible. To clear a range containing merged cells, the range has to include ALL cells that make up the merged cells.

 

[rlv01]

Not tested.

Sub saveSheetWithoutFormulas()
    Dim saveLocation As String, xlsxFile  As String
    Dim WB As Workbook, WS As Worksheet
    Dim SheetName As String
    
    SheetName = ActiveSheet.Name
    
    saveLocation = "C:\Users\****\Desktop\" & ActiveSheet.Range("f4").Value
    xlsxFile = Split(saveLocation, ".")(0) & ".xlsx"
    
    Application.DisplayAlerts = False
    ThisWorkbook.SaveCopyAs FileName:=saveLocation
    
    Set WB = Application.Workbooks.Open(FileName:=saveLocation)
    Set WS = WB.Worksheets(SheetName)
    WS.UsedRange.Value = WS.UsedRange.Value

    WB.SaveAs FileName:=xlsxFile, FileFormat:=xlOpenXMLWorkbook    'see XlFileFormat Enumeration for different formats
    WB.Close False
    Application.DisplayAlerts = False
        
    Kill saveLocation
End Sub

 

[Jnb99]

Not tested.

Sub saveSheetWithoutFormulas()
    Dim saveLocation As String, xlsxFile  As String
    Dim WB As Workbook, WS As Worksheet
    Dim SheetName As String
   
    SheetName = ActiveSheet.Name
   
    saveLocation = "C:\Users\****\Desktop\" & ActiveSheet.Range("f4").Value
    xlsxFile = Split(saveLocation, ".")(0) & ".xlsx"
   
    Application.DisplayAlerts = False
    ThisWorkbook.SaveCopyAs FileName:=saveLocation
   
    Set WB = Application.Workbooks.Open(FileName:=saveLocation)
    Set WS = WB.Worksheets(SheetName)
    WS.UsedRange.Value = WS.UsedRange.Value

    WB.SaveAs FileName:=xlsxFile, FileFormat:=xlOpenXMLWorkbook    'see XlFileFormat Enumeration for different formats
    WB.Close False
    Application.DisplayAlerts = False
       
    Kill saveLocation
End Sub

You are a machine! Its working perfectly! Thank you very much!

 

[Jnb99]

Hey @rlv01 ,

I know I am supposed to start a new post, but I am not getting response on it there. May I ask to help me with invoice increment?

I have this code

Range("f4").Value = Range("f4").Value + 1

Inserted it all over the code, keep giving me an error. I have no idea where to place it. It is supposed to increment the Template sheet number +1 each time the macro is run.

I would also like to transfer the main info of each estimate to a estimate database sheet i.e. estimate number "F4", date "F3", Client "A14" and estimate total "G44" when the macro is run. I haven't started to try and figure this part out