Simple Loop with If Statement

[thmcauley]

Does someone have time to help me finish up what I think should be a simple Loop? I've struggled way too long on this and am just not getting it!

Purpose: to take a sheet of 1500 rows and 10 columns, evaluate col A and if it is a "Y" then copy that row to another worksheet and print that other worksheet. It would be nice to look at col B for max # of rows instead of processing all 1500, but that isn't necessary. Here is what I have so far and it is just returning the first row:

Sub PrintInv6()
'
' PrintInv6 Macro
'
'
Application.Goto Reference:="R6C1"
For i = 1 To 6
If Cells(i, 1).Value = "Y" Then
ActiveCell.Range("A1:J1").Select
Selection.Copy
Sheets("Invoice").Select
Application.Goto Reference:="R17C9"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
Sheets("Billing Detail").Select
Else: End If
Next i
Sheets("Billing Detail").Select
ActiveCell.Offset(-4, 8).Range("A1").Select
Application.CutCopyMode = False
End Sub


Thank you.

 

[Drrellik]

I don't know VBA well enough to help you, but from my old basic days and just a guess here, your 4th line ActiveCell.Range("A1:J1").select might be the reason you are only getting the first line returned, if I knew how to replace that range with the value of your next i value = yes I would try that.

I will wait to see how far off I was from someone that knows.

 

[timfritsch]

Try:

Sub PrintInv6()
Dim i As Long, startRow As Long, endRow As Long

With Sheets("Billing Detail")

startRow = 6
endRow = .Range("A65536").End(xlUp).Row

For i = startRow To endRow
If UCase(.Cells(i, 1)) = "Y" Then
.Cells(i, 1).Resize(1, 10).Copy
Sheets("Invoice").Range("I17").PasteSpecial Paste:=xlPasteValues
Sheets("Invoice").PrintOut Copies:=1
End If
Next i
End With
End Sub

 

[mumps]

Why look at column B for the maximum number of rows? Why not look at column A?

 

[thmcauley]

I'm not familiar with the Printout Copies command. Do I need to replace the abc with something? I'm getting a Run-time error 1004: Printout method of worksheet class failed.

 

[thmcauley]

Col A is a formula all the way down. Col B will only get filled in as we add more data.

 

[timfritsch]

I'm not familiar with the Printout Copies command. Do I need to replace the abc with something? I'm getting a Run-time error 1004: Printout method of worksheet class failed.

Yeah, sorry about that. When testing, I didn't want to send pages to my printer, so I used "abc" to cause an error. I've edited my post changing "abc" to 1.

 

[thmcauley]

Ahhhh . . . it's a beautiful thing! Thanks. That's just what I needed and it works like a charm now. Wish I were better at VBA, but I guess this is what I need to get there.