Custom Printing in a Macro
 

I have worksheet that contains Names in Col A. SS#'s in Col B. and row's 1-3
are title rows. My Question is how can I print the Title Rows, and Only
print Col's A&B when their is corrisponding data in the Col's being printed
for them.

Basically I have many col's of data, that I have a Macro printing them one
per page with Titles and Col's A&B. I only want the Name's In Col A&B if
they have data for the col being printed.

Can this be done?

Custom Printing in a Macro
 

I think you'll have to repost with a clearer description of exactly what you
want to achieve! If you print, and there is nothing below the title rows,
you will only print the title rows?
--
Hth

Kassie Kasselman


"Rich" wrote:

I have worksheet that contains Names in Col A. SS#'s in Col B. and row's 1-3
are title rows. My Question is how can I print the Title Rows, and Only
print Col's A&B when their is corrisponding data in the Col's being printed
for them.

Basically I have many col's of data, that I have a Macro printing them one
per page with Titles and Col's A&B. I only want the Name's In Col A&B if
they have data for the col being printed.

Can this be done?

Custom Printing in a Macro
 

I have Mutiple Col's and The First being a list of Employees. I want to
print Seperate reports on for each col. But only Print the employee's in The
first col if they have data in the corrisponding col's.



"kassie" wrote:

I think you'll have to repost with a clearer description of exactly what you
want to achieve! If you print, and there is nothing below the title rows,
you will only print the title rows?
--
Hth

Kassie Kasselman


"Rich" wrote:

I have worksheet that contains Names in Col A. SS#'s in Col B. and row's 1-3
are title rows. My Question is how can I print the Title Rows, and Only
print Col's A&B when their is corrisponding data in the Col's being printed
for them.

Basically I have many col's of data, that I have a Macro printing them one
per page with Titles and Col's A&B. I only want the Name's In Col A&B if
they have data for the col being printed.

Can this be done?

Custom Printing in a Macro
 

Maybe you can apply Data|Filter Autofilter to show just the rows that have data.

I'm not quite sure if you have more used columns than A:B, but you could hide
the columns you don't want before you print, too.

Rich wrote:

I have Mutiple Col's and The First being a list of Employees. I want to
print Seperate reports on for each col. But only Print the employee's in The
first col if they have data in the corrisponding col's.

"kassie" wrote:

I think you'll have to repost with a clearer description of exactly what you
want to achieve! If you print, and there is nothing below the title rows,
you will only print the title rows?
--
Hth

Kassie Kasselman


"Rich" wrote:

I have worksheet that contains Names in Col A. SS#'s in Col B. and row's 1-3
are title rows. My Question is how can I print the Title Rows, and Only
print Col's A&B when their is corrisponding data in the Col's being printed
for them.

Basically I have many col's of data, that I have a Macro printing them one
per page with Titles and Col's A&B. I only want the Name's In Col A&B if
they have data for the col being printed.

Can this be done?

--

Dave Peterson

Custom Printing in a Macro
 

The issue is I'm trying to do this all in One Worksheet and Print Multiple
reports using
a Macro.

Example:
COL A COL B COL C COL D COL E

John Doe 1.00 5.00
Jane Doe 2.00 6.00
Tom 8.00
Dick 1.00
Harry 2.00 10.00


When the Reports Print, I need the following resluts,
One Report for COL B, But I should only see John Doe and Dick
A report for COL C and Only Jand Doe and Harry should be listed, and A report
for Col D that Johh, Jand and Tom should be Listed. And Finally the Rport
for E should only have Harry.

Is there anyway in the Macro that I can put conditions to only print the
Employees that have data in the col that I'm Printing.

Rich


"Dave Peterson" wrote:

Maybe you can apply Data|Filter Autofilter to show just the rows that have data.

I'm not quite sure if you have more used columns than A:B, but you could hide
the columns you don't want before you print, too.

Rich wrote:

I have Mutiple Col's and The First being a list of Employees. I want to
print Seperate reports on for each col. But only Print the employee's in The
first col if they have data in the corrisponding col's.

"kassie" wrote:

I think you'll have to repost with a clearer description of exactly what you
want to achieve! If you print, and there is nothing below the title rows,
you will only print the title rows?
--
Hth

Kassie Kasselman


"Rich" wrote:

I have worksheet that contains Names in Col A. SS#'s in Col B. and row's 1-3
are title rows. My Question is how can I print the Title Rows, and Only
print Col's A&B when their is corrisponding data in the Col's being printed
for them.

Basically I have many col's of data, that I have a Macro printing them one
per page with Titles and Col's A&B. I only want the Name's In Col A&B if
they have data for the col being printed.

Can this be done?

--

Dave Peterson

Custom Printing in a Macro
 

Apply autofilter to cover all columns and rows of your data. In your
macro you could hide the columns you do not want to print (not sure if
you want to do this - you don't make it clear if you want to print John
Doe's data for column D when you are printing based on column B) then
apply a filter to the column you are interested in - Custom,
non-blanks, then print. Then select the next column etc.

Hope this helps.

Pete

Rich wrote:

The issue is I'm trying to do this all in One Worksheet and Print Multiple
reports using
a Macro.

Example:
COL A COL B COL C COL D COL E

John Doe 1.00 5.00
Jane Doe 2.00 6.00
Tom 8.00
Dick 1.00
Harry 2.00 10.00


When the Reports Print, I need the following resluts,
One Report for COL B, But I should only see John Doe and Dick
A report for COL C and Only Jand Doe and Harry should be listed, and A report
for Col D that Johh, Jand and Tom should be Listed. And Finally the Rport
for E should only have Harry.

Is there anyway in the Macro that I can put conditions to only print the
Employees that have data in the col that I'm Printing.

Rich


"Dave Peterson" wrote:

Maybe you can apply Data|Filter Autofilter to show just the rows that have data.

I'm not quite sure if you have more used columns than A:B, but you could hide
the columns you don't want before you print, too.

Rich wrote:

I have Mutiple Col's and The First being a list of Employees. I want to
print Seperate reports on for each col. But only Print the employee's in The
first col if they have data in the corrisponding col's.

"kassie" wrote:

I think you'll have to repost with a clearer description of exactly what you
want to achieve! If you print, and there is nothing below the title rows,
you will only print the title rows?
--
Hth

Kassie Kasselman


"Rich" wrote:

I have worksheet that contains Names in Col A. SS#'s in Col B. and row's 1-3
are title rows. My Question is how can I print the Title Rows, and Only
print Col's A&B when their is corrisponding data in the Col's being printed
for them.

Basically I have many col's of data, that I have a Macro printing them one
per page with Titles and Col's A&B. I only want the Name's In Col A&B if
they have data for the col being printed.

Can this be done?

--

Dave Peterson

Custom Printing in a Macro
 

This macro does what I explained in the earlier post:

Option Explicit
Sub testme()
Dim wks As Worksheet
Dim iCol As Long
Dim FirstCol As Long
Dim LastCol As Long
Dim LastRow As Long
Dim LastHeaderRow As Long

Set wks = Worksheets("sheet1")

With wks
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastHeaderRow = 3
FirstCol = 2
LastCol = .Cells(LastHeaderRow, .Columns.Count).End(xlToLeft).Column

'remove any filter
.AutoFilterMode = False

'apply it to where we want it
.Range(.Cells(LastHeaderRow, "A"), .Cells(LastRow, LastCol)).AutoFilter

For iCol = FirstCol To LastCol
'hide all the columns from B over to the right
.Range(.Cells(1, FirstCol), .Cells(1, LastCol)) _
.EntireColumn.Hidden = True
'unhide the column that we're printing (along with A)
.Columns(iCol).Hidden = False

'show all the data
If .FilterMode Then
.ShowAllData
End If

'show only non-blank rows in that column
.AutoFilter.Range.AutoFilter field:=iCol, Criteria1:="<"

'print it out
.PrintOut preview:=True
Next iCol
'remove the filter
.AutoFilterMode = False
'show all the columns
.UsedRange.Columns.Hidden = False
End With
End Sub

It assumes that you can find the range to filter based on the lastheaderrow (row
3 has to have something in it for the last column).

And it figures that column A defines how many rows should be looked at.

Change the preview:=true to :=false after you've tested it.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


Rich wrote:

The issue is I'm trying to do this all in One Worksheet and Print Multiple
reports using
a Macro.

Example:
COL A COL B COL C COL D COL E

John Doe 1.00 5.00
Jane Doe 2.00 6.00
Tom 8.00
Dick 1.00
Harry 2.00 10.00

When the Reports Print, I need the following resluts,
One Report for COL B, But I should only see John Doe and Dick
A report for COL C and Only Jand Doe and Harry should be listed, and A report
for Col D that Johh, Jand and Tom should be Listed. And Finally the Rport
for E should only have Harry.

Is there anyway in the Macro that I can put conditions to only print the
Employees that have data in the col that I'm Printing.

Rich

"Dave Peterson" wrote:

Maybe you can apply Data|Filter Autofilter to show just the rows that have data.

I'm not quite sure if you have more used columns than A:B, but you could hide
the columns you don't want before you print, too.

Rich wrote:

I have Mutiple Col's and The First being a list of Employees. I want to
print Seperate reports on for each col. But only Print the employee's in The
first col if they have data in the corrisponding col's.

"kassie" wrote:

I think you'll have to repost with a clearer description of exactly what you
want to achieve! If you print, and there is nothing below the title rows,
you will only print the title rows?
--
Hth

Kassie Kasselman


"Rich" wrote:

I have worksheet that contains Names in Col A. SS#'s in Col B. and row's 1-3
are title rows. My Question is how can I print the Title Rows, and Only
print Col's A&B when their is corrisponding data in the Col's being printed
for them.

Basically I have many col's of data, that I have a Macro printing them one
per page with Titles and Col's A&B. I only want the Name's In Col A&B if
they have data for the col being printed.

Can this be done?

--

Dave Peterson


--

Dave Peterson

Custom Printing in a Macro
 

Thanks Dave,
I was able to to it. Your suggestions were very helpfu.

Thank you.

"Dave Peterson" wrote:

This macro does what I explained in the earlier post:

Option Explicit
Sub testme()
Dim wks As Worksheet
Dim iCol As Long
Dim FirstCol As Long
Dim LastCol As Long
Dim LastRow As Long
Dim LastHeaderRow As Long

Set wks = Worksheets("sheet1")

With wks
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastHeaderRow = 3
FirstCol = 2
LastCol = .Cells(LastHeaderRow, .Columns.Count).End(xlToLeft).Column

'remove any filter
.AutoFilterMode = False

'apply it to where we want it
.Range(.Cells(LastHeaderRow, "A"), .Cells(LastRow, LastCol)).AutoFilter

For iCol = FirstCol To LastCol
'hide all the columns from B over to the right
.Range(.Cells(1, FirstCol), .Cells(1, LastCol)) _
.EntireColumn.Hidden = True
'unhide the column that we're printing (along with A)
.Columns(iCol).Hidden = False

'show all the data
If .FilterMode Then
.ShowAllData
End If

'show only non-blank rows in that column
.AutoFilter.Range.AutoFilter field:=iCol, Criteria1:="<"

'print it out
.PrintOut preview:=True
Next iCol
'remove the filter
.AutoFilterMode = False
'show all the columns
.UsedRange.Columns.Hidden = False
End With
End Sub

It assumes that you can find the range to filter based on the lastheaderrow (row
3 has to have something in it for the last column).

And it figures that column A defines how many rows should be looked at.

Change the preview:=true to :=false after you've tested it.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


Rich wrote:

The issue is I'm trying to do this all in One Worksheet and Print Multiple
reports using
a Macro.

Example:
COL A COL B COL C COL D COL E

John Doe 1.00 5.00
Jane Doe 2.00 6.00
Tom 8.00
Dick 1.00
Harry 2.00 10.00

When the Reports Print, I need the following resluts,
One Report for COL B, But I should only see John Doe and Dick
A report for COL C and Only Jand Doe and Harry should be listed, and A report
for Col D that Johh, Jand and Tom should be Listed. And Finally the Rport
for E should only have Harry.

Is there anyway in the Macro that I can put conditions to only print the
Employees that have data in the col that I'm Printing.

Rich

"Dave Peterson" wrote:

Maybe you can apply Data|Filter Autofilter to show just the rows that have data.

I'm not quite sure if you have more used columns than A:B, but you could hide
the columns you don't want before you print, too.

Rich wrote:

I have Mutiple Col's and The First being a list of Employees. I want to
print Seperate reports on for each col. But only Print the employee's in The
first col if they have data in the corrisponding col's.

"kassie" wrote:

I think you'll have to repost with a clearer description of exactly what you
want to achieve! If you print, and there is nothing below the title rows,
you will only print the title rows?
--
Hth

Kassie Kasselman


"Rich" wrote:

I have worksheet that contains Names in Col A. SS#'s in Col B. and row's 1-3
are title rows. My Question is how can I print the Title Rows, and Only
print Col's A&B when their is corrisponding data in the Col's being printed
for them.

Basically I have many col's of data, that I have a Macro printing them one
per page with Titles and Col's A&B. I only want the Name's In Col A&B if
they have data for the col being printed.

Can this be done?

--

Dave Peterson


--

Dave Peterson