Over My Head Print Code

[SSDSCA]

I am probably overthinking this and making it more complicated than it really
is and now have myself really confused. I have a sheet to track project costs
by the day that is fixed at 47 rows, however, the number of colums is
dependent on the job length. I would like to repeat columns A thru H on each
sheet printed out and have the sheets print out 7 days per sheet (2 columns
required for each day) with the code being able to identify the last day
entered and print out the required number of sheets. ie - if the job is 11
days long it would print out 2 sheets, one with 7 days and the second with 4
days with entires and the remaining columns would be printed, however there
would be no entries in these colums.

 

[Per Jessen]

Hi Don

Try this:

Sub SetUp_PrintArea()
Dim RepeatArea As Range
Dim MyPrintArea As Range

Set RepeatArea = Range("B1:H47")
LastColumn = Range("B1").End(xlToRight).Column
weeks = WorksheetFunction.RoundUp((LastColumn - 8) / 14, 0)
For p = 0 To weeks - 1
Set MyPrintArea = Union(RepeatArea, Range("I1").Offset(0, p *
14).Resize(47, 14))
ActiveSheet.PageSetup.PrintArea = MyPrintArea.Address
Debug.Print MyPrintArea.Address
' PrintOut
Next
End Sub

Regards,
Per

 

[SSDSCA]

Thanks for responding Per.

I can sure see where you are going with the code, however when I tried to
run the macro it didn't do anything, not even an error box pop up.
Unfortunately my VBA knowledge is not enough to troubleshoot your code
although with my limited know how, it all looks to be correct. Not sure what
to do.
--
Thanks and Regards,
Don

Hi Don

Try this:

Sub SetUp_PrintArea()
Dim RepeatArea As Range
Dim MyPrintArea As Range

Set RepeatArea = Range("B1:H47")
LastColumn = Range("B1").End(xlToRight).Column
weeks = WorksheetFunction.RoundUp((LastColumn - 8) / 14, 0)
For p = 0 To weeks - 1
Set MyPrintArea = Union(RepeatArea, Range("I1").Offset(0, p *
14).Resize(47, 14))
ActiveSheet.PageSetup.PrintArea = MyPrintArea.Address
Debug.Print MyPrintArea.Address
' PrintOut
Next
End Sub

Regards,
Per

 

[Leith Ross]

Hello SSDSCA,

If you don't have the *Immediate Window* open in the Visual Basic
Editor when the macro is run, you won't see anything. I modified the
code so the address appears in a dialog box.

Code:
--------------------

Sub SetUp_PrintArea()
Dim RepeatArea As Range
Dim MyPrintArea As Range

Set RepeatArea = Range("B1:H47")

LastColumn = Range("B1").End(xlToRight).Column
weeks = WorksheetFunction.RoundUp((LastColumn - 8) / 14, 0)

For p = 0 To weeks - 1
Set MyPrintArea = Union(RepeatArea, Range("I1").Offset(0, p * 14).Resize(47, 14))
ActiveSheet.PageSetup.PrintArea = MyPrintArea.Address
MsgBox "Print Area Set to " & MyPrintArea.Address
' PrintOut
Next

End Sub

--------------------

ADDING THE MACRO
1. *Copy* the macro above pressing the keys *CTRL+C*
2. Open your workbook
3. Press the keys *ALT+F11* to open the Visual Basic Editor
4. Press the keys *ALT+I* to activate the *Insert menu*
5. *Press M* to insert a *Standard Module*
6. *Paste* the code by pressing the keys *CTRL+V*
7. Make any custom changes to the macro if needed at this time.
8. *Save the Macro* by pressing the keys *CTRL+S*
9. Press the keys *ALT+Q* to exit the Editor, and return to Excel.

TO RUN THE MACRO...
To run the macro from Excel, open the workbook, and press *ALT+F8* to
display the *Run Macro Dialog*. Double Click the macro's name to *Run*
it.

Sincerely,
Leith Ross

 

[SSDSCA]

Thanks Leith

The code works well to set the print area for week one costs, however when I
enter in project costs on week two it didn't reset the print area for two
weeks. Again, I'm not certain what to change within the code to have it
identify week two.