Trying to Calculate Print_Area 1

[WBURKERT]

Good Friday Morning to all the Tek-Tip guru's,

This doesn't seem to be working as expected. I am trying to set the print_area for a couple hundred spreadsheets. The code seems to make the print_area the same for all worksheets.

For s = 1 To 220
Application.StatusBar = "Setting Print Area on Section " & s
x = Worksheets("Sec" & s).UsedRange.Columns.Count
Set lastCell = Cells.SpecialCells(xlCellTypeLastCell)
Worksheets("Sec" & s).PageSetup.PrintArea = Range(Cells(1, 1), lastCell).Address
Next s

 

[mscallisto]

How is s defined?

You could use Option Explicit, it forces you to define your variables.

sam

 

[WBURKERT]

Dim s As Long.

The For Next Loop works, just having problems finding the last cell/row for the print_area

 

[Gruuuu]

Try

...
Set lastCell = Worksheets("Sec" & s).Cells.Find(What:="*", After:=Range("IV65000"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
...

Haven't tested this, may take some minor changes to work.

You can also adjust the After and the SearchOrder parameters to speed your processing up.

 

[WBURKERT]

For some reason Worksheets("Sec" & s) causes a problem

 

[SkipVought]

hi,

   Dim ws as worksheet

   For each ws in worksheets
    Application.StatusBar = "Setting Print Area on Section " & ws.name
    ws.PageSetup.PrintArea = ws.usedrange.Address
   Next ws

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[WBURKERT]

Hi Skip,

Your coding skills are second to none. You make my code so darn efficient.

Keep on the lookout for my next stumbling block that I am researching now.

THANK-YOU

 

[WBURKERT]

Is there a way to auto respond with YES when trying to create a print_area for an empty worksheet?

 

[SkipVought]

    For Each ws In Worksheets
      with ws.UsedRange
        If .Count = 1 Then
            If .Value = "" Then MsgBox "YES"
        End If
      end with
    Next

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[WBURKERT]

Hey Skip,

Thanks, I think. I have tried to include your second piece into your first piece of coding and either I'm doing it wrong and I did not fully explain myslef.

Sometimes some of my worksheets will be empty and when I try and set print_area I get message pop-up that states I've selected a single cell for the print area and if correct, click OK. It is OK and I click, jut wondering if this can ocur within the macro so it can run unattended.

It looks like your code is popping up a MsgBox with the word "YES" in it, whereas I am trying to have the macro respond to the Pop-up stating I have selected a single cell for print_area. I appreciate the help but maybe you could put the two pieces together and show me the code then???

 

[SkipVought]

"Is there a way to auto respond with YES "

I missunderstood your intent.

  Dim ws As Worksheet
    [b]
   Application.DisplayAlerts = False[/b]
   For Each ws In Worksheets
    Application.StatusBar = "Setting Print Area on Section " & ws.Name
    ws.PageSetup.PrintArea = ws.UsedRange.Address
   Next ws[b]
   Application.DisplayAlerts = True[/b]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}