Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
various printing from a hiden sheet
"Shawn" wrote:
I have a hidden sheet with a table of data. Column B:B has various employee names in it in desending alphabetical order. I would like a macro that would, without selecting the page if possible, print out all rows of data with employee A, then, on another sheet, print out all rows of data with employee B, then employee C and so on. Do you want to print this information on another worksheet or on paper? The AutoFilter method is probably the best way to do this, but you first have to set up a range on which to run the method. The other thing you have to do is provide some method of selecting which of the names in column B you want to use as the filter criterion. Maybe you could generate a form and populate a listbox with unique entries from the column in question. Another problem with your plan is that you can't print from a hidden sheet. If you really want to print from the hidden sheet, you have to temporarily unhide it. Here's something to get you started: Sub JustSmith() 'Dim, Dim, Dim... Set sht = Sheets("NameOfHiddenSheet") topRow = sht.Range("B1").End(xlDown).Row bottomRow = sht.Range("B65536").End(xlUp).Row Set NameRange = sht.Range("B" & topRow & ":B" & bottomRow) ' ... or set up range some other way if you have to ' you can replace hard-coded name with text from listbox: NameRange.AutoFilter Field:=1, Criteria1:="Smith, Tom", VisibleDropdown:=False Application.ScreenUpdating = False sht.Visible = xlSheetVisible sht.PrintPreview ' or .PrintOut sht.Visible = xlSheetHidden Application.ScreenUpdating = True End Sub If you want to copy to another sheet, you could step through each row of the NameRegion, decide if it is not hidden (AutoFilter hides non-matching rows,) then copy non-hidden rows to another worksheet. --Shawn |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Printing Hiden Pages | Excel Discussion (Misc queries) | |||
Why can't I unhide a hiden row | Excel Discussion (Misc queries) | |||
Is column hiden? | Excel Programming | |||
Is column hiden? | Excel Programming | |||
hiden sheet | Excel Programming |