Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom Header Macro | Excel Discussion (Misc queries) | |||
Macro Help (Uppercase multiple ranges?) | Excel Discussion (Misc queries) | |||
Make Custom Macro always Available | Excel Worksheet Functions | |||
Macro Help In Excel | Excel Discussion (Misc queries) | |||
custom toolbar w/ macro | Excel Discussion (Misc queries) |