MrExcel Publishing
Your One Stop for Excel Tips & Solutions

STILL CANT SEEM TO GET THIS TO WORK


Posted by RANDY on September 17, 2001 2:00 PM

Dim intRow As Integer
Dim intCol As Integer
intCol = 31For intRow = 8 To 20

Sheets("INFO").Select
If Not IsEmpty(Cells(intRow, 2)) Then
Sheets("SHEET3").Range("SHEET3!$P$1:$AB$500").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range(Cells(1, intCol), Cells(2, intCol)), CopyToRange:=Range("SHEET3!$AS$1:$BE$1"), Unique:=False
Sheets("SINGLE").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End If

intCol = intCol + 1
Next intRow


Posted by RANDY on September 17, 2001 2:02 PM

IGNORE THAT..WRONG CODE (NT)


Posted by RANDY on September 17, 2001 2:05 PM

RIGHT SET OF CODE

Dim intRow As Integer
Dim intCol As Integer
intCol = 31
For intRow = 8 To 20


If Not IsEmpty(Sheets("INFO").Cells(intRow, 2)) Then
Sheets("SHEET3").Range("SHEET3!$P$1:$AB$500").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range(Cells(1, intCol), Cells(2, intCol)), CopyToRange:=Range("SHEET3!$AS$1:$BE$1"), Unique:=False
Sheets("SINGLE").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End If

intCol = intCol + 1
Next intRow

Posted by Russell Hauf on September 17, 2001 2:28 PM

Re: RIGHT SET OF CODE

What exactly isn't working? Are you getting an error? If so, what is it? If not, what isn't working right?

I believe that I helped you with this. I just tried to take what you had and have it loop 13 times, from what I remember.

-rh If Not IsEmpty(Sheets("INFO").Cells(intRow, 2)) Then Sheets("SHEET3").Range("SHEET3!$P$1:$AB$500").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range(Cells(1, intCol), Cells(2, intCol)), CopyToRange:=Range("SHEET3!$AS$1:$BE$1"), Unique:=False Sheets("SINGLE").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True End If

Posted by randy on September 18, 2001 5:23 AM

Re: RIGHT SET OF CODE

Russell

Thanks for your help..you were helping me with this..
I'm not getting any errors now. I was getting some 'global failers' but I think i strighten that out. What is happening I think is that the intCOL variable is not changeing. When the prints come out they are of the same results.

If Not IsEmpty(Sheets("INFO").Cells(intRow, 2)) Then Sheets("SHEET3").Range("SHEET3!$P$1:$AB$500").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range(Cells(1, intCol), Cells(2, intCol)), CopyToRange:=Range("SHEET3!$AS$1:$BE$1"), Unique:=False Sheets("SINGLE").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True End If

Posted by Russell Hauf on September 18, 2001 8:13 AM

Re: RIGHT SET OF CODE

So the filtering is not changing anything? Have you tried to step through this using the debugger? I suggest doing that. If you still can't see what's going on, maybe email me your workbook if you can (you can always change values, etc. if you need to).

It's hard to know exactly what's going on just from the code, sorry.

-rh

If Not IsEmpty(Sheets("INFO").Cells(intRow, 2)) Then Sheets("SHEET3").Range("SHEET3!$P$1:$AB$500").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range(Cells(1, intCol), Cells(2, intCol)), CopyToRange:=Range("SHEET3!$AS$1:$BE$1"), Unique:=False Sheets("SINGLE").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True End If

Posted by randy on September 18, 2001 10:43 AM

got it!!

i just needed to clear the sorted list before i sorted it again....this is what the finished code looked like.

Dim intRow As Integer
Dim intCol As Integer
intCol = 31
For intRow = 8 To 20


If Not IsEmpty(Sheets("INFO").Cells(intRow, 2)) Then
Sheets("SHEET3").Select
Columns("AS:BE").Select
Selection.ClearContents
Sheets("SHEET3").Range("SHEET3!$P$1:$AB$500").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range(Cells(1, intCol), Cells(2, intCol)), CopyToRange:=Range("SHEET3!$AS$1:$BE$1"), Unique:=False
Sheets("SINGLE").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End If

intCol = intCol + 1
Next intRow

If Not IsEmpty(Sheets("INFO").Cells(intRow, 2)) Then Sheets("SHEET3").Range("SHEET3!$P$1:$AB$500").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range(Cells(1, intCol), Cells(2, intCol)), CopyToRange:=Range("SHEET3!$AS$1:$BE$1"), Unique:=False Sheets("SINGLE").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True End If