"IgnorePrintAreas:=False" --- trying to get the equivalent when saving excel to excel, anyone help?

Jmorrison67

Board Regular
Joined
Aug 20, 2021
Messages
51
Office Version
  1. 2016
Platform
  1. Windows
Good afternoon Community,

I am looking for help on how to save the print area's when copying worksheets to a new workbook (excel to excel). User Akuini has helped tremendously to get the code I need to where it is now.
I am currently using this parameter when saving worksheets to pdf >
VBA Code:
IgnorePrintAreas:=False
and works as expected for pdf, but I also need to adapt the excel equivalent code which Akuini helped with so that I only save the print area's in the new excel workbook.

Bit about the file I am using:
Workbook has 95 tabs
All worksheets have different Print Area ranges as all contain different tables of info and graphs
I use the whole workbook to pull out different worksheets for different managers to see different info
Each manager (8 or so) gets a combination of different worksheets, with most any one manager gets is 35 worksheets.
There is just as much data outside the print area's, so it is essential the managers only see the print area data

This is the output bit of the code:

VBA Code:
Sheets(ary).Copy
For Each ws In ActiveWorkbook.Worksheets
    With ws.UsedRange
        .Value = .Value
    End With
Next ws

With ActiveWorkbook
    .SaveAs Filename:=FolderPath & " - " & Format(Now, "dd-mm-yyyy hhmm") & ".xlsx"
    .Close
End With

MsgBox "Excel file has been successfully exported."

End Sub


Can anyone help in getting the 'print areas' bit added to the code for when I have to save these down as excel workbooks?

KR
Jmorrison67
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,908
Office Version
  1. 2007
Platform
  1. Windows
Check your sheet. Delete all columns on the right and rows below that are "apparently empty", but may have blank cells.

Or copy only the cells that have information to a new sheet. Delete the original sheet.
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Forum statistics

Threads
1,147,482
Messages
5,741,402
Members
423,657
Latest member
Medrok2021

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