Hi,

Been trying to write up a code that once a button is clicked the following process will happen. Firstly it would copy the current sheet I am working from into a new workbook. I am not sure when you copy the sheet into a new workbook it would only copy the values and layout excluding macros. (Preferably copy the sheet's printable area, but if it compllicates things then please disregard)

Then it would save the new workbook with the filename automatically generated from the value of 2 cells (B10 "Name" and F4 "Ref #") in a designated folder.

Finally to top off the already complex and difficult macro, it would then convert the new workbook into a PDF file or print it as a PDF in the same save folder.

I've written a code but it was very basic as I am new to VB. I just used the function "record a macro". 

    

Sub Save and Print()

Sheets("Enquiry").Select
    Sheets("Enquiry").copy
    Sheets("Enquiry").Select
    Sheets("Enquiry").Name = "Enquiry Copy"

ActiveWorkbook.SaveAs FileName:= _
        "D:\" & Range("F4").Value & Range("B10").value, FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False

 With ActiveSheet.UsedRange
lngLastCol = .Columns.Count + .Column - 1
lngLastRow = .Rows.Count + .Row - 1
End With

With ActiveSheet
strPrintRng = .Range(Cells(1, 1), Cells(lngLastRow, lngLastCol)).Address(RowAbsolute:=False, ColumnAbsolute:=False)
.PageSetup.PrintArea = strPrintRng
End With

Application.Dialogs(xlDialogPrint).Show 

End Sub