hello.
I have a page set up macro, and need to configure my print titles line of
activesheet.pagesetup

I see where I can insert the exact rows I want, but mine vary from file to
file, so I need to have a variant so that I can base the number of rows on
some variant criteria.

I was thinking that I could make this based on the rows being filled in with
a color, or no-fill.

The color is typically mid-grey-- color index # 15.


Thank you.

Used this macro when I was at JP Morgan. Put your numbers in ColumnA and run
the macro:
Sub InsertAnyRows()

Dim insertNumber As Range
Dim insertStart As Range
Dim redRng As Range
Dim i As Integer

Set insertNumber = Application.InputBox _
(Prompt:="Select a point to begin inserting rows.
For instance, choose first non blank cell in Column A",
Title:="Add a row", Type:=8)
insertNumber.Select
If insertNumber <= 0 Then
MsgBox ("Invalid Number Entered")
Exit Sub
End If
Dim myRow As Long

lastcell = Cells(Rows.Count, "A").End(xlUp).Row
myRow = 1
Do Until myRow = lastcell
For i = 1 To Cells(myRow, 1)

If Cells(myRow, 1) < "" Then
Cells(myRow + 1, 1).Select
Selection.EntireRow.Insert Shift:=xlDown
End If

Next
lastcell = Cells(Rows.Count, "A").End(xlUp).Row
myRow = myRow + 1
Loop

End Sub

Remember to make a back of your Excel file, just in case the result
is...ummmm...unintended. It's a total PITA to try to recover lost data.

Regards,
Ryan---


--
RyGuy


"SteveDB1" wrote:

hello.
I have a page set up macro, and need to configure my print titles line of
activesheet.pagesetup

I see where I can insert the exact rows I want, but mine vary from file to
file, so I need to have a variant so that I can base the number of rows on
some variant criteria.

I was thinking that I could make this based on the rows being filled in with
a color, or no-fill.

The color is typically mid-grey-- color index # 15.


Thank you.

hi Ryan,
Thanks for the input. I really appreciate it.
That however doesn't do what I want.

In Page Set Up from the printer/page set up command, there's an option on
the last tab to determine which rows/columns you want repeated on each page
of the print out.
I've presently got mine set to blank. I.e.,

With ActiveSheet.PageSetUp
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With

What I want is something that will look at some attribute, or property of
the worksheet that can set the PrintTitleRows for me when I activate it. I am
aware I can use an application.inputbox, but prefer not to.

My thinking was that I can/should be able to use the color index or some
other atribute/property to set the PrintTitleRows to that location.
I.e., something like....

if colorindex < -4142 then
set activesheet.PrintTitleRows = $1:$???

With ActiveSheet.PageSetUp
.PrintTitleRows = "$1:$???"
.PrintTitleColumns = ""
End With

I hope that makes my idea clearer.
Again, thank you.

"ryguy7272" wrote:

Used this macro when I was at JP Morgan. Put your numbers in ColumnA and run
the macro:
Sub InsertAnyRows()

Dim insertNumber As Range
Dim insertStart As Range
Dim redRng As Range
Dim i As Integer

Set insertNumber = Application.InputBox _
(Prompt:="Select a point to begin inserting rows.
For instance, choose first non blank cell in Column A",
Title:="Add a row", Type:=8)
insertNumber.Select
If insertNumber <= 0 Then
MsgBox ("Invalid Number Entered")
Exit Sub
End If
Dim myRow As Long

lastcell = Cells(Rows.Count, "A").End(xlUp).Row
myRow = 1
Do Until myRow = lastcell
For i = 1 To Cells(myRow, 1)

If Cells(myRow, 1) < "" Then
Cells(myRow + 1, 1).Select
Selection.EntireRow.Insert Shift:=xlDown
End If

Next
lastcell = Cells(Rows.Count, "A").End(xlUp).Row
myRow = myRow + 1
Loop

End Sub

Remember to make a back of your Excel file, just in case the result
is...ummmm...unintended. It's a total PITA to try to recover lost data.

Regards,
Ryan---


--
RyGuy


"SteveDB1" wrote:

hello.
I have a page set up macro, and need to configure my print titles line of
activesheet.pagesetup

I see where I can insert the exact rows I want, but mine vary from file to
file, so I need to have a variant so that I can base the number of rows on
some variant criteria.

I was thinking that I could make this based on the rows being filled in with
a color, or no-fill.

The color is typically mid-grey-- color index # 15.


Thank you.

Can you work with this?

Dim rws As Variant
Dim col As Variant

rws = Sheets("Sheet2").Range("I1")
col = Sheets("Sheet2").Range("J1")

With ActiveSheet.PageSetup
.PrintTitleRows = rws
.PrintTitleColumns = col
End With
'etc., etc., etc.


Or........
rws = ActiveSheet.Range("I1")
col = ActiveSheet.Range("J1")

In Cell I1 I have this: 1:1
In Cell J1 I have this: H:H



HTH,
Ryan---


--
RyGuy


"SteveDB1" wrote:

hi Ryan,
Thanks for the input. I really appreciate it.
That however doesn't do what I want.

In Page Set Up from the printer/page set up command, there's an option on
the last tab to determine which rows/columns you want repeated on each page
of the print out.
I've presently got mine set to blank. I.e.,

With ActiveSheet.PageSetUp
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With

What I want is something that will look at some attribute, or property of
the worksheet that can set the PrintTitleRows for me when I activate it. I am
aware I can use an application.inputbox, but prefer not to.

My thinking was that I can/should be able to use the color index or some
other atribute/property to set the PrintTitleRows to that location.
I.e., something like....

if colorindex < -4142 then
set activesheet.PrintTitleRows = $1:$???

With ActiveSheet.PageSetUp
.PrintTitleRows = "$1:$???"
.PrintTitleColumns = ""
End With

I hope that makes my idea clearer.
Again, thank you.

"ryguy7272" wrote:

Used this macro when I was at JP Morgan. Put your numbers in ColumnA and run
the macro:
Sub InsertAnyRows()

Dim insertNumber As Range
Dim insertStart As Range
Dim redRng As Range
Dim i As Integer

Set insertNumber = Application.InputBox _
(Prompt:="Select a point to begin inserting rows.
For instance, choose first non blank cell in Column A",
Title:="Add a row", Type:=8)
insertNumber.Select
If insertNumber <= 0 Then
MsgBox ("Invalid Number Entered")
Exit Sub
End If
Dim myRow As Long

lastcell = Cells(Rows.Count, "A").End(xlUp).Row
myRow = 1
Do Until myRow = lastcell
For i = 1 To Cells(myRow, 1)

If Cells(myRow, 1) < "" Then
Cells(myRow + 1, 1).Select
Selection.EntireRow.Insert Shift:=xlDown
End If

Next
lastcell = Cells(Rows.Count, "A").End(xlUp).Row
myRow = myRow + 1
Loop

End Sub

Remember to make a back of your Excel file, just in case the result
is...ummmm...unintended. It's a total PITA to try to recover lost data.

Regards,
Ryan---


--
RyGuy


"SteveDB1" wrote:

hello.
I have a page set up macro, and need to configure my print titles line of
activesheet.pagesetup

I see where I can insert the exact rows I want, but mine vary from file to
file, so I need to have a variant so that I can base the number of rows on
some variant criteria.

I was thinking that I could make this based on the rows being filled in with
a color, or no-fill.

The color is typically mid-grey-- color index # 15.


Thank you.

hi RyGuy,
Sorry I took so long to respond. I dfidn't see your final response until
today.

No it won't work.
Thanks though.
My system is based on something more complex than this.
I did finally find something that worked, so I'll consider this issue solved.
Thank you.

"ryguy7272" wrote:

Can you work with this?

Dim rws As Variant
Dim col As Variant

rws = Sheets("Sheet2").Range("I1")
col = Sheets("Sheet2").Range("J1")

With ActiveSheet.PageSetup
.PrintTitleRows = rws
.PrintTitleColumns = col
End With
'etc., etc., etc.


Or........
rws = ActiveSheet.Range("I1")
col = ActiveSheet.Range("J1")

In Cell I1 I have this: 1:1
In Cell J1 I have this: H:H



HTH,
Ryan---


--
RyGuy


"SteveDB1" wrote:

hi Ryan,
Thanks for the input. I really appreciate it.
That however doesn't do what I want.

In Page Set Up from the printer/page set up command, there's an option on
the last tab to determine which rows/columns you want repeated on each page
of the print out.
I've presently got mine set to blank. I.e.,

With ActiveSheet.PageSetUp
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With

What I want is something that will look at some attribute, or property of
the worksheet that can set the PrintTitleRows for me when I activate it. I am
aware I can use an application.inputbox, but prefer not to.

My thinking was that I can/should be able to use the color index or some
other atribute/property to set the PrintTitleRows to that location.
I.e., something like....

if colorindex < -4142 then
set activesheet.PrintTitleRows = $1:$???

With ActiveSheet.PageSetUp
.PrintTitleRows = "$1:$???"
.PrintTitleColumns = ""
End With

I hope that makes my idea clearer.
Again, thank you.

"ryguy7272" wrote:

Used this macro when I was at JP Morgan. Put your numbers in ColumnA and run
the macro:
Sub InsertAnyRows()

Dim insertNumber As Range
Dim insertStart As Range
Dim redRng As Range
Dim i As Integer

Set insertNumber = Application.InputBox _
(Prompt:="Select a point to begin inserting rows.
For instance, choose first non blank cell in Column A",
Title:="Add a row", Type:=8)
insertNumber.Select
If insertNumber <= 0 Then
MsgBox ("Invalid Number Entered")
Exit Sub
End If
Dim myRow As Long

lastcell = Cells(Rows.Count, "A").End(xlUp).Row
myRow = 1
Do Until myRow = lastcell
For i = 1 To Cells(myRow, 1)

If Cells(myRow, 1) < "" Then
Cells(myRow + 1, 1).Select
Selection.EntireRow.Insert Shift:=xlDown
End If

Next
lastcell = Cells(Rows.Count, "A").End(xlUp).Row
myRow = myRow + 1
Loop

End Sub

Remember to make a back of your Excel file, just in case the result
is...ummmm...unintended. It's a total PITA to try to recover lost data.

Regards,
Ryan---


--
RyGuy


"SteveDB1" wrote:

hello.
I have a page set up macro, and need to configure my print titles line of
activesheet.pagesetup

I see where I can insert the exact rows I want, but mine vary from file to
file, so I need to have a variant so that I can base the number of rows on
some variant criteria.

I was thinking that I could make this based on the rows being filled in with
a color, or no-fill.

The color is typically mid-grey-- color index # 15.


Thank you.