VBA Print Settings for all worksheets

[Prish]

What code can be used to set the paper size, orientation, print area based on current region in each worksheet?

I tried to use the following but it fails:

Dim wSht As Worksheet

For Each wSht In Worksheets
    wSht.Range("A1").CurrentRegion.Select

'printsettings code
Next wSht

 

[Joe4]

If you turn on your Macro Recorder and record yourself making the settings the way you like, you should have the code you need to do it.

 

[Prish]

If you turn on your Macro Recorder and record yourself making the settings the way you like, you should have the code you need to do it.

Thanks Joe, I am familiar with the recorder. However, I am sure you will know the recorder records selected ranges as fixed ranges eg. A1:E356 etc.
I also don't think the recorder records loops, how would it?
I only need to know how to select the current region for all worksheets. I have the print settings code from recording.

for a single sheet I would normally use

    ActiveCell.CurrentRegion.Select

I cannot figure this out for all sheets.

 

[Norie]

Why would you select the current region to set print settings?

Try doing what Joe suggested and then post the code here, it should be easy to adapt the generated code to add a loop and set the print area for each sheet individually.

 

[Joe4]

You already have most of the code you need.
I would probably set my current region equal to a range variable, and then just use that range variable in my print settings, i.e.

Dim wSht As Worksheet
Dim rng As Range


For Each wSht In Worksheets
    wSht.Activate
    Set rng = wSht.Range("A1").CurrentRegion


'printsettings code
    ActiveSheet.PageSetup.PrintArea = rng.Address
Next wSht

 

[Prish]

You already have most of the code you need.
I would probably set my current region equal to a range variable, and then just use that range variable in my print settings, i.e.

Thanks Joe, that is exactly what I needed.

 

[Joe4]

You are welcome.