Additional code required to save document to PDF and to automatically adjust with row macro code

[SeanOZ]

Hi wonderful people.

My last enquiry is that I now have one more question.

I have written a macro to automatically pdf my document, but when I had rows, I then have a to manually adjust to suit my macros for adding columns and rows.

Is there anyway for this code to automatically update when I had rows. So for example I don't want to print below row 22 at the moment but by adding a row this then chops the information by one line and I have to manually adjust each time.

Also when creating the pdf, can I get an option to save to a location of my choice as this currently just saves to my documents on which ever computer I am using at the time. So basically I get a local copy and then have to rename and save to a network.

Please see the code I am using:
Sub Button2_Click()
Dim sFile As String


sFile = Application.DefaultFilePath & "" & _
ActiveWorkbook.Name & ".pdf"


Sheets("Notes for Quotes").Select
ActiveSheet.PageSetup.PrintArea = "A1:I22"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=sFile, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=True
End Sub

Again, thank you so much for all your help.

Kind regards,

Sean

 

[DanteAmor]

There is some reference to know which is the last row and which is the last column.


Meanwhile, try this:

Sub Button2_Click()
  Dim sFile As String, sPath As String
  sFile = ActiveWorkbook.Name & ".pdf"
  With Application.FileDialog(msoFileDialogFolderPicker)
    .Title = "Select folder"
    .AllowMultiSelect = False
    .InitialFileName = Application.DefaultFilePath
    If .Show <> -1 Then Exit Sub
    sPath = .SelectedItems(1)
  End With
  Sheets("Notes for Quotes").Select
  ActiveSheet.PageSetup.PrintArea = "A1:I22"
  ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=sPath & "\" & sFile, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
    OpenAfterPublish:=True
End Sub

 

[SeanOZ]

Hi Dante,

Thank you so much.

I will give this a try.

I really appreciate the help that everyone is giving me on this forum.

Kind regards,

Sean

 

[SeanOZ]

Hi Dante,

So the code you provided works perfectly where I can now save to a specific location, so again, thanks again for this.

With regards to what I want to pdf, I only want to pdf the top half of the spreadsheet and no information below a specific line / row which is my case is 22.

At the moment I don't wish to pdf anything beyond row 22 but when I add an additional line in the spreadsheet using a macro which I am using and then I use the pdf to save macro, I then have to manually update this so if I have added one row, I update to row 23. As I add more lines I then have to update accordingly.

Happy to provide any further information you required.

Kind regards,

Sean

 

[DanteAmor]

And visually how do you know which is the last row with data?
That is, if you put the course in cell A1 and press the end key and then the down key, does the cursor stop in row 22?
Or if you put the cursor in cell A1000 and then press the end key and then the up key, does the cursor stop in row 22?
Or do those rounds and tell me in which column and with which method it stops in row 22.