various printing from a hiden sheet

[Guest]

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.

For example, if Smith, Tom is repeated in Rows B2, B3, B4, I would want it
to print Rows 2, 3 and 4 on a sheet of paper. Then if Thompson, Jill is
repeated in B5, B6, B7, I would want it to print rows 5, 6 and 7 on the next
sheet. Please help.

 

[Tom Ogilvy]

select you data and do Data=>filter=>Autofilter

select Smith, Tom from the dropdown in the column of Names.

This hides all but rows with Smith, Tom.

Now you can print

turn on the macro recorder and peform this action manually. then stop
recording and look at the code. You can use you unique list of employee
names to populate the criteria argument in the code and then print the sheet
for each setting.

The dropdown arrows don't show up when you print.

 

[Guest]

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

 

[Don Guillett]

try this. chg sheet to yours and printpreview to printout
Sub printeachchange()
With Sheets("sheet5")
..Visible = True
LastRow = .Cells(Rows.Count, "b").End(xlUp).Row
For i = LastRow To 2 Step -1
y = Application.CountIf(.Range("b:b"), .Cells(i, "b"))
If .Cells(i - 1, "b") <> .Cells(i, "b") Then
..Cells(i, "b").Resize(y, 1).entirerow.PrintPreview
End If
Next i
..Visible = False
End With
End Sub

 

[Guest]

It might help if I added my code (I want this to print out on my printer):
Sheets("Stepdown3").Select
Sheets("Letter").Visible = True
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

With Sheets("Sheet2")
.Visible = True
.AutoFilter Field:=2,
Criteria1:=Sheets("Letter").Range("C13").Value
.PrintOut Copies:=1
End With
End If
Next Cell

 

[Guest]

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?

 

[Guest]

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

 

[Guest]

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

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.