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

SeanOZ

New Member
Joined
Oct 31, 2019
Messages
13
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
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,221
Office Version
2007
Platform
Windows
There is some reference to know which is the last row and which is the last column.


Meanwhile, try this:

Code:
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

New Member
Joined
Oct 31, 2019
Messages
13
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

New Member
Joined
Oct 31, 2019
Messages
13
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

Well-known Member
Joined
Dec 3, 2018
Messages
10,221
Office Version
2007
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,089,862
Messages
5,410,854
Members
403,331
Latest member
dignityy

This Week's Hot Topics

Top