Save Sheet page VBA

KlausW

Board Regular
Joined
Sep 9, 2020
Messages
172
Office Version
  1. 2016
Platform
  1. Windows
Hi, I'm using this VBA code to save the sheets, this works really well.
VBA Code:
'Create and assign variables
Dim saveLocation As String
Dim sheetArray As Variant

saveLocation = "C:\Users\k-wit\Documents\Bestilling af proviant\" & Sheets("Indkøbsrekvisition").Range("m1").Value & ".pdf"
sheetArray = Array("Bestilling", "Indkøbsrekvisition")

'Select specific sheets from workbook, the save all as PDF
Sheets(sheetArray).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=saveLocation
Now I would like Excel to save the same sheets, but some sheets may well be on 2 or more pages it appears in F1. Is it possible to save exactly the number of pages.
All help will be appreciated.
Best Regards Klaus W
 
Last edited by a moderator:

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

shknbk2

Active Member
Joined
Mar 5, 2016
Messages
386
Office Version
  1. 365
Platform
  1. Windows
Please forgive my understanding of your request. Can you say what you would like in a different way?
What is the difference between the PDFs that are being saved now and the new PDFs you would like to have?
What does F1 have to do with the 2 or more pages?
How are the PDFs now not saving exactly the number of pages?
 

KlausW

Board Regular
Joined
Sep 9, 2020
Messages
172
Office Version
  1. 2016
Platform
  1. Windows
Please forgive my understanding of your request. Can you say what you would like in a different way?
What is the difference between the PDFs that are being saved now and the new PDFs you would like to have?
What does F1 have to do with the 2 or more pages?
How are the PDFs now not saving exactly the number of pages?
Hi, as it saves it now in pdf format it is really good. One of the sheet can sometimes be on more than one page, it's standing in cell F1. If it says 1 in cell F1, Excel should only make one page out of sheet, it says 2 in cell F1, Excel should make 2 and so on. It's an order sheet for food. Therefore, the pages may well vary.
KW
 

shknbk2

Active Member
Joined
Mar 5, 2016
Messages
386
Office Version
  1. 365
Platform
  1. Windows
It seems like it could be an issue with the print area. If you manually print one of the sheets as a PDF, does it behave as the code does now or does it behave like you want it to?
 

KlausW

Board Regular
Joined
Sep 9, 2020
Messages
172
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

It seems like it could be an issue with the print area. If you manually print one of the sheets as a PDF, does it behave as the code does now or does it behave like you want it to?
VBA Code:
'Create and assign variables
Dim saveLocation As String
Dim sheetArray As Variant

saveLocation = "C:\Users\k-wit\Documents\Bestilling af proviant\" & Sheets("Indkøbsrekvisition").Range("m1").Value & ".pdf"
sheetArray = Array("Bestilling", "Indkøbsrekvisition")

'Select specific sheets from workbook, the save all as PDF
Sheets(sheetArray).Select
PageTo = Range("f1").Value
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=saveLocation
It seems like it could be an issue with the print area. If you manually print one of the sheets as a PDF, does it behave as the code does now or does it behave like you want it to?
Hi, when I try your suggestions it does not do what I want. I tried to insert the line "PageTo = Range (" f1 "). Value" when I hoped it would solve the problem. So if in F1 is 2, Excel must save 2 pages of the sheet, the pages in the sheet are always the same range. KW
 

shknbk2

Active Member
Joined
Mar 5, 2016
Messages
386
Office Version
  1. 365
Platform
  1. Windows
Forget the code for now. If you try to save one of the sheets by using File->Save As and then choose PDF as the output, does it include 2 pages or something else?
 

KlausW

Board Regular
Joined
Sep 9, 2020
Messages
172
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Forget the code for now. If you try to save one of the sheets by using File->Save As and then choose PDF as the output, does it include 2 pages or something else?
Hi again, I am trying another way, in another Excel sheet I am using this code to print only the number of pages that are in cell F1. Do you think some of it can be used?
KW
VBA Code:
Dim intRaekke As Integer
Dim SidsteSide As Integer

    For intRaekke = 3 To Worksheets.Count
            If Cells(intRaekke, 2).Value = "x" Then
            
        SidsteSide = Sheets(intRaekke).Range("F1")
        
            Sheets(intRaekke).PrintOut From:=1, To:=SidsteSide
            End If
    Next
 

shknbk2

Active Member
Joined
Mar 5, 2016
Messages
386
Office Version
  1. 365
Platform
  1. Windows
Sure. You could use a combination of the codes you provided. Change the printer name to match what your Microsoft PDF printer is called (check your printers in the computer's Start->Settings->Devices->Printers & Scanners).
VBA Code:
    Dim intRaekke As Integer
    Dim SidsteSide As Integer
    Dim saveLocation As String
    
    For intRaekke = 3 To Worksheets.Count
        If Cells(intRaekke, 2).Value = "x" Then
            SidsteSide = Sheets(intRaekke).Range("F1")
            saveLocation = "C:\Users\k-wit\Documents\Bestilling af proviant\" & Sheets(intRaekke).Range("m1").Value & ".pdf"
            Sheets(intRaekke).PrintOut From:=1, To:=SidsteSide, ActivePrinter:="Microsoft Print to PDF", prToFileName:=saveLocation
        End If
    Next
 

KlausW

Board Regular
Joined
Sep 9, 2020
Messages
172
Office Version
  1. 2016
Platform
  1. Windows
Sure. You could use a combination of the codes you provided. Change the printer name to match what your Microsoft PDF printer is called (check your printers in the computer's Start->Settings->Devices->Printers & Scanners).
VBA Code:
    Dim intRaekke As Integer
    Dim SidsteSide As Integer
    Dim saveLocation As String
   
    For intRaekke = 3 To Worksheets.Count
        If Cells(intRaekke, 2).Value = "x" Then
            SidsteSide = Sheets(intRaekke).Range("F1")
            saveLocation = "C:\Users\k-wit\Documents\Bestilling af proviant\" & Sheets(intRaekke).Range("m1").Value & ".pdf"
            Sheets(intRaekke).PrintOut From:=1, To:=SidsteSide, ActivePrinter:="Microsoft Print to PDF", prToFileName:=saveLocation
        End If
    Next
Sorry but it unfortunately does not run.
 

shknbk2

Active Member
Joined
Mar 5, 2016
Messages
386
Office Version
  1. 365
Platform
  1. Windows
If you put the cursor anywhere in the procedure, you can use F8 to step through each line of the code. Can you step through the code to see where the issue is?
 

Forum statistics

Threads
1,148,145
Messages
5,745,050
Members
423,917
Latest member
Frank1931

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top