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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
various printing from a hiden sheet
Ok...I am getting somewhere. This worked except it only printed one row of
the data for employee A????? She has several rows of data? "Shawn O'Donnell" wrote: "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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
various printing from a hiden sheet
Here is the new code...it isn't filtering just right???? It prints out the
first line of everybody, instead of everything per person???? Sheets("Stepdown3").Select Columns("B:B").Select For Each Cell In Selection.SpecialCells(xlConstants, 23) If Cell.Value < 0 Then Cell.Select With Sheets("Letter") .Range("C13").Value = Selection.Value .Range("E13").Value = Selection.Offset(0, -1).Value .Range("C14").Value = Selection.Offset(0, 1).Value .Range("I24").Value = Selection.Offset(0, 8).Value .Range("I27").Value = Selection.Offset(0, 7).Value .Range("I30").Value = Selection.Offset(0, 10).Value .PrintOut Copies:=1 End With Set sht = Sheets("Stepdown2") topRow = sht.Range("B1").End(xlDown).Row bottomRow = sht.Range("B65536").End(xlUp).Row Set NameRange = sht.Range("B" & topRow & ":B" & bottomRow) sht.AutoFilter Field:=1, Criteria1:=Sheets("Letter").Range("C13").Value, VisibleDropdown:=False sht.Visible = xlSheetVisible sht.PrintOut sht.Visible = xlSheetHidden End If Next Cell "Shawn" wrote: Ok...I am getting somewhere. This worked except it only printed one row of the data for employee A????? She has several rows of data? "Shawn O'Donnell" wrote: "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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
various printing from a hiden sheet
"Shawn" wrote:
Here is the new code...it isn't filtering just right???? It prints out the first line of everybody, instead of everything per person???? I'm doing a lot of guessing here, since I don't know what's on the various spreadsheets you refer to. I assume Stepdown2 is the one that you described before, with three (or more?) lines of information for each person. I'm guessing Stepdown3 has only one line per person, and its column B contains the names that you're going to use in the filter? There are a couple of pitfalls to this approach, by the way. If a person's name isn't entered exactly the same way in every cell, Excel will think it's got different people. That could be one of your problems. And that's why they invented normalized databases. Sheets("Stepdown3").Select Columns("B:B").Select For Each Cell In Selection.SpecialCells(xlConstants, 23) That 23 is a magic number. It's better to type out the constants and the summation you do to get it. That gives the poor sucker trying to read the code a better chance. But if you're going to use all the options, you can just say SpecialCells(xlConstants), can't you? Is 0 a valid option for an entry in Stepdown3's column B? If not, do you need this? If Cell.Value < 0 Then Cell.Select With Sheets("Letter") .Range("C13").Value = Selection.Value .Range("E13").Value = Selection.Offset(0, -1).Value .Range("C14").Value = Selection.Offset(0, 1).Value .Range("I24").Value = Selection.Offset(0, 8).Value .Range("I27").Value = Selection.Offset(0, 7).Value .Range("I30").Value = Selection.Offset(0, 10).Value .PrintOut Copies:=1 The trees asked me to ask you to change PrintOut to PrintPreview until you get this working right... End With Set sht = Sheets("Stepdown2") topRow = sht.Range("B1").End(xlDown).Row By the way, that line assumes that there's no chance of the data starting in row one. I should have suggested something like If sht.Range("B1").value < "" then topRow = sht.Range("B1").End(xlDown).Row else topRow = 1 End If bottomRow = sht.Range("B65536").End(xlUp).Row Set NameRange = sht.Range("B" & topRow & ":B" & bottomRow) sht.AutoFilter Field:=1, Criteria1:=Sheets("Letter").Range("C13").Value, VisibleDropdown:=False sht.Visible = xlSheetVisible sht.PrintOut sht.Visible = xlSheetHidden End If Next Cell "Shawn" wrote: Ok...I am getting somewhere. This worked except it only printed one row of the data for employee A????? She has several rows of data? Are you sure the name is entered the same way in each cell? In the VBE immediate window, paste the following on a fresh line: Sheets("Stepdown2").Range("B1:B100").AutoFilter Field:=1, Criteria1:="Employee A. Name", VisibleDropdown:=True Use a big enough Range to get all your data. And put Employee A's real name in the quotes. Then hit enter. Unhide the sheet Stepdown2 if you haven't already. Then look at the drop-down arrow in column B. How many times does employee A's name appear? Trim() can help with leading and trailing spaces. Spaces in the middle & other inconsistencies will be more difficult to track down. |
Reply |
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 |