Over My Head Print Code

  • Thread starter Thread starter SSDSCA
  • Start date Start date
S

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.
 
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
 
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


Per Jessen said:
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
Click to expand...
 
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.
 
Back
Top