I am creating a macro to reformat data pulled into Excel from a csv file.
It contains various data at the beginning of the report, followed by column
headings and data. The column heading row is not a constant because the info
at the beginning of the report is variable. I want only the column headings
and data to appear on subsequent pages. I can search and locate the column
headings and select them. How do I get this row (or cells) that I've
selected into the PrintTitleRow for subsequent pages?
--
mkferrara

With ActiveSheet.PageSetup
.PrintTitleRows = "$2:$2"
.PrintTitleColumns = ""
End With


A good approach is to turn on the macro recorder, then set the page setup
and print area settings manually. Then turn it off and look at the code.
Eliminate the lines that will default and that you don't need to set -
otherwise, each line executes as a separate command and can take a lot of
time.


--
Regards,
Tom Ogilvy



"mkferrara" wrote:

I am creating a macro to reformat data pulled into Excel from a csv file.
It contains various data at the beginning of the report, followed by column
headings and data. The column heading row is not a constant because the info
at the beginning of the report is variable. I want only the column headings
and data to appear on subsequent pages. I can search and locate the column
headings and select them. How do I get this row (or cells) that I've
selected into the PrintTitleRow for subsequent pages?
--
mkferrara

Your response seems to show Row 2 as printing all the time. This macro would
be run for different files, and each one could have that header row in a
different row.

If I search for the row with the headers and select them, I get:
Cells.Find(What:="count", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Range(Selection, Selection.End(xlToRight)).Select

If I use page setup and choose the row with headers, it appears to be
taking row 18 always, and that won't work for some files.
With ActiveSheet.PageSetup
.PrintTitleRows = "$18:$18"
.PrintTitleColumns = ""
End With

While using "rows to repeat", I can't seem to do a search. Is there a way
to combine the .PrintTitleRows= with the searchable selection?
--
mkferrara


"Tom Ogilvy" wrote:

With ActiveSheet.PageSetup
.PrintTitleRows = "$2:$2"
.PrintTitleColumns = ""
End With


A good approach is to turn on the macro recorder, then set the page setup
and print area settings manually. Then turn it off and look at the code.
Eliminate the lines that will default and that you don't need to set -
otherwise, each line executes as a separate command and can take a lot of
time.


--
Regards,
Tom Ogilvy



"mkferrara" wrote:

I am creating a macro to reformat data pulled into Excel from a csv file.
It contains various data at the beginning of the report, followed by column
headings and data. The column heading row is not a constant because the info
at the beginning of the report is variable. I want only the column headings
and data to appear on subsequent pages. I can search and locate the column
headings and select them. How do I get this row (or cells) that I've
selected into the PrintTitleRow for subsequent pages?
--
mkferrara

That was a sample. I assumed you would adapt it to your situation.

Dim r as Range
set r = Cells.Find(What:="count", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
With ActiveSheet.PageSetup
.PrintTitleRows = r.Address(1,1)
.PrintTitleColumns = ""
End With

--
Regards,
Tom Ogilvy


"mkferrara" wrote:

Your response seems to show Row 2 as printing all the time. This macro would
be run for different files, and each one could have that header row in a
different row.

If I search for the row with the headers and select them, I get:
Cells.Find(What:="count", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Range(Selection, Selection.End(xlToRight)).Select

If I use page setup and choose the row with headers, it appears to be
taking row 18 always, and that won't work for some files.
With ActiveSheet.PageSetup
.PrintTitleRows = "$18:$18"
.PrintTitleColumns = ""
End With

While using "rows to repeat", I can't seem to do a search. Is there a way
to combine the .PrintTitleRows= with the searchable selection?
--
mkferrara


"Tom Ogilvy" wrote:

With ActiveSheet.PageSetup
.PrintTitleRows = "$2:$2"
.PrintTitleColumns = ""
End With


A good approach is to turn on the macro recorder, then set the page setup
and print area settings manually. Then turn it off and look at the code.
Eliminate the lines that will default and that you don't need to set -
otherwise, each line executes as a separate command and can take a lot of
time.


--
Regards,
Tom Ogilvy



"mkferrara" wrote:

I am creating a macro to reformat data pulled into Excel from a csv file.
It contains various data at the beginning of the report, followed by column
headings and data. The column heading row is not a constant because the info
at the beginning of the report is variable. I want only the column headings
and data to appear on subsequent pages. I can search and locate the column
headings and select them. How do I get this row (or cells) that I've
selected into the PrintTitleRow for subsequent pages?
--
mkferrara

Thank you! That does the job perfectly. I wouldn't have been able to figure
that one out by myself.
--
mkferrara


"Tom Ogilvy" wrote:

That was a sample. I assumed you would adapt it to your situation.

Dim r as Range
set r = Cells.Find(What:="count", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
With ActiveSheet.PageSetup
.PrintTitleRows = r.Address(1,1)
.PrintTitleColumns = ""
End With

--
Regards,
Tom Ogilvy


"mkferrara" wrote:

Your response seems to show Row 2 as printing all the time. This macro would
be run for different files, and each one could have that header row in a
different row.

If I search for the row with the headers and select them, I get:
Cells.Find(What:="count", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Range(Selection, Selection.End(xlToRight)).Select

If I use page setup and choose the row with headers, it appears to be
taking row 18 always, and that won't work for some files.
With ActiveSheet.PageSetup
.PrintTitleRows = "$18:$18"
.PrintTitleColumns = ""
End With

While using "rows to repeat", I can't seem to do a search. Is there a way
to combine the .PrintTitleRows= with the searchable selection?
--
mkferrara


"Tom Ogilvy" wrote:

With ActiveSheet.PageSetup
.PrintTitleRows = "$2:$2"
.PrintTitleColumns = ""
End With


A good approach is to turn on the macro recorder, then set the page setup
and print area settings manually. Then turn it off and look at the code.
Eliminate the lines that will default and that you don't need to set -
otherwise, each line executes as a separate command and can take a lot of
time.


--
Regards,
Tom Ogilvy



"mkferrara" wrote:

I am creating a macro to reformat data pulled into Excel from a csv file.
It contains various data at the beginning of the report, followed by column
headings and data. The column heading row is not a constant because the info
at the beginning of the report is variable. I want only the column headings
and data to appear on subsequent pages. I can search and locate the column
headings and select them. How do I get this row (or cells) that I've
selected into the PrintTitleRow for subsequent pages?
--
mkferrara