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

 

[rlv01]

The code you posted appears to do everything you say you want it to do. What is the problem you are having?

 

[Jnb99]

The code you posted appears to do everything you say you want it to do. What is the problem you are having?

Hi,

Thank you for your quick reply.

It’s not saving a copy of the “estimate” worksheet, or if it is, I can not find. Pdf is saving perfect

 

[Jnb99]

Oh and contents also not clearing

 

[rlv01]

Maybe something like this then.

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 = Worksheets("Estimate").Range("A1:g50")
    rng.ExportAsFixedFormat Type:=xlTypePDF, Filename:=saveLocation

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

Sub saveSheetWithoutFormulas()
    Dim saveLocation As String
    Dim rng As Range

    ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
    
    saveLocation = "C:\Users\****\Desktop\" & ActiveSheet.Range("f4").Value
    ActiveWorkbook.SaveAs Filename:=saveLocation
End Sub

 

[Jnb99]

Hi,

Sorry for the long delay.

I copied the code, but now nothing is working. It first failed on the clear contents bit, so I took it out to test the rest of the code, but its doing nothing

 

[Jnb99]

Wait, I think I made a mistake somewhere, its working!

Only clear contents still a headache

 

[Jnb99]

Ok, another problem. 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?

 

[rlv01]

Hi,

Sorry for the long delay.

I copied the code, but now nothing is working. It first failed on the clear contents bit, so I took it out to test the rest of the code, but its doing nothing

There is nothing wrong with this statement. I tested it on my PC

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

When you say clear contents failed, what do you mean?

 

[Jnb99]

Run-time error 1004

Cant do that to a merged cell