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:

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
I look at it tomorrow it is about to be night in Denmark thanks KW
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

KlausW

Board Regular
Joined
Sep 9, 2020
Messages
172
Office Version
  1. 2016
Platform
  1. Windows
Hi, Denmark has woken up 😊

I'm trying to post a few pictures of the result I would like to achieve. It is the sheet "Bestilling" with green that can vary in page number marked with a yellow ring.

So, what I want is for Excel to pack that number in the yellow ring along with

the sheet "Indkøbsrekvisition" with red.

The "Indkøbsrekvisition" sheet with red is always only 1 page.

It's an item order sheet.

KW
 

Attachments

  • Skærmbillede (2).png
    Skærmbillede (2).png
    72.9 KB · Views: 1
  • Skærmbillede (3).png
    Skærmbillede (3).png
    44.1 KB · Views: 1

shknbk2

Active Member
Joined
Mar 5, 2016
Messages
386
Office Version
  1. 365
Platform
  1. Windows
The Midwest has also now woken up!

So is the problem that when Bestilling is exported as a PDF, it has more than 4 pages? Are the extra pages blank cells?

Do you also need both sheets to be exported in a single PDF? If exported as 2 PDFs, can you later combine into a single one?
 

shknbk2

Active Member
Joined
Mar 5, 2016
Messages
386
Office Version
  1. 365
Platform
  1. Windows
It would be possible to have only pages 1 to 4 of Bestilling in the file, but you would first have to switch the order of your sheets so that Indkøbsrekvisition is before Bestilling. And you would have to make sure that the Print Area of Indkøbsrekvisition is set to be only page 1. For example, the saveLocation you get from Indkøbsrekvisition is in M1. I'm guessing that it would not be on page 1 since it is far to the right. However, if Indkøbsrekvisition is never to be more than 1 page, you could set its Print Area to only the menu portion, and the PrintOut method would only output page 1.

This would allow you to use the From and To parameters of ExportAsFixedFormat to specify the page of Indkøbsrekvisition plus the first 4 pages of Bestilling.

When using the array of selected sheets, the From and To also work to print out pages "selected" from the set of pages that would print out if there were no restrictions. When I was using test data where my Bestilling sheet (which in my test case was before Indkøbsrekvisition) had 9 pages and Indkøbsrekvisition had 1 sheet, From = 1 and To=5 only printed out Bestilling pages 1-5. When To was 10, it printed all 9 pages of Bestilling and the 1 page of Indkøbsrekvisition.

Switching Indkøbsrekvisition to put it before Bestilling in the sheet order so it is on the left of Bestilling might get close. For example, the To parameter may be set to Worksheet("Bestilling").Range("F1") + 1. This would give you a single 5-page PDF where page 1 is Indkøbsrekvisition and pages 2-5 are Bestilling. However, the order of pages is different with Indkøbsrekvisition being first in the PDF.

If that is okay, then this code should work (or be close to working) for you after you move Indkøbsrekvisition as described and make sure its Print Area is really just 1 page.
VBA Code:
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, From:=1, To:=Sheets("Bestilling").Range("F1").Value + 1
 
Solution

KlausW

Board Regular
Joined
Sep 9, 2020
Messages
172
Office Version
  1. 2016
Platform
  1. Windows
It would be possible to have only pages 1 to 4 of Bestilling in the file, but you would first have to switch the order of your sheets so that Indkøbsrekvisition is before Bestilling. And you would have to make sure that the Print Area of Indkøbsrekvisition is set to be only page 1. For example, the saveLocation you get from Indkøbsrekvisition is in M1. I'm guessing that it would not be on page 1 since it is far to the right. However, if Indkøbsrekvisition is never to be more than 1 page, you could set its Print Area to only the menu portion, and the PrintOut method would only output page 1.

This would allow you to use the From and To parameters of ExportAsFixedFormat to specify the page of Indkøbsrekvisition plus the first 4 pages of Bestilling.

When using the array of selected sheets, the From and To also work to print out pages "selected" from the set of pages that would print out if there were no restrictions. When I was using test data where my Bestilling sheet (which in my test case was before Indkøbsrekvisition) had 9 pages and Indkøbsrekvisition had 1 sheet, From = 1 and To=5 only printed out Bestilling pages 1-5. When To was 10, it printed all 9 pages of Bestilling and the 1 page of Indkøbsrekvisition.

Switching Indkøbsrekvisition to put it before Bestilling in the sheet order so it is on the left of Bestilling might get close. For example, the To parameter may be set to Worksheet("Bestilling").Range("F1") + 1. This would give you a single 5-page PDF where page 1 is Indkøbsrekvisition and pages 2-5 are Bestilling. However, the order of pages is different with Indkøbsrekvisition being first in the PDF.

If that is okay, then this code should work (or be close to working) for you after you move Indkøbsrekvisition as described and make sure its Print Area is really just 1 page.
VBA Code:
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, From:=1, To:=Sheets("Bestilling").Range("F1").Value + 1
Thanks, so much it works as it should it has facilitated my job many many times. Hello to you my friend best regards Klaus W
 
Last edited:

Forum statistics

Threads
1,148,147
Messages
5,745,055
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