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

"SSDSCA" skrev i meddelelsen
...
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.
--
Thanks and Regards,
Don

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" wrote:

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" skrev i meddelelsen
...
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.
--
Thanks and Regards,
Don