Printing problem

I have this small macro, that after printing 3pages, just give an error,
I guess it need some time of pause between every page, but I don't know
much about this language, (VBA) can someone help me?

Thanks,

Fernando

CODE

Private Sub CommandButton1_Click()
ans = MsgBox("Do you want to print faxes?", vbYesNo)
If ans = vbNo Then
Exit Sub
End If
Dim col As Integer
Dim row As Integer
Dim counter As Integer
Dim I As Integer


col = 2
row = 2
counter = 1

For row = row To 20
If Sheet8.Cells(row, counter) = "" Then
Exit Sub
Else
Sheet1.Cells(3, 7) = Sheet8.Cells(row, counter)
counter = counter + 1
Sheet1.Cells(5, 3) = Sheet8.Cells(row, counter)
counter = counter + 1
Sheet1.Cells(5, 6) = Sheet8.Cells(row, counter)
counter = counter + 1
Sheet1.Cells(5, 9) = Sheet8.Cells(row, counter)
counter = counter + 1
Sheet1.Cells(5, 12) = Sheet8.Cells(row, counter)
counter = counter + 1
counter = 1
Sheet1.PrintOut
Sheet8.Cells(row, 5) = "PRINTED"



For I = I To 300
Next I
I = 0


End If
Next row

End Sub

I'd stay away from variables like Row. It looks way to much like the VBA
property .Row. It may not confuse excel, but it would confuse me.

And I think I'd use something more like this:

Option Explicit
Private Sub CommandButton1_Click()

Dim Ans As Long
Dim myRow As Long

Ans = MsgBox("Do you want to print faxes?", vbYesNo)
If Ans = vbNo Then
Exit Sub
End If

For myRow = 2 To 20
If Sheet8.Cells(myRow, 1) = "" Then
Exit Sub
Else
Sheet1.Cells(3, 7).Value = Sheet8.Cells(myRow, "A").Value
Sheet1.Cells(5, 3).Value = Sheet8.Cells(myRow, "B").Value
Sheet1.Cells(5, 6).Value = Sheet8.Cells(myRow, "C").Value
Sheet1.Cells(5, 9).Value = Sheet8.Cells(myRow, "D").Value
Sheet1.Cells(5, 12).Value = Sheet8.Cells(myRow, "E").Value
Sheet1.PrintOut preview:=True
Sheet8.Cells(myRow, 5) = "PRINTED"
End If
Next myRow

End Sub


(Preview:=true is to save paper while testing.)

But your loop
for row = row to 20
looks like a typo

And instead of using a counter, I just plopped the actual column letter into the
assignment lines.

Fernando Duran wrote:
>
> I have this small macro, that after printing 3pages, just give an error,
> I guess it need some time of pause between every page, but I don't know
> much about this language, (VBA) can someone help me?
>
> Thanks,
>
> Fernando
>
> CODE
>
> Private Sub CommandButton1_Click()
> ans = MsgBox("Do you want to print faxes?", vbYesNo)
> If ans = vbNo Then
> Exit Sub
> End If
> Dim col As Integer
> Dim row As Integer
> Dim counter As Integer
> Dim I As Integer
>
> col = 2
> row = 2
> counter = 1
>
> For row = row To 20
> If Sheet8.Cells(row, counter) = "" Then
> Exit Sub
> Else
> Sheet1.Cells(3, 7) = Sheet8.Cells(row, counter)
> counter = counter + 1
> Sheet1.Cells(5, 3) = Sheet8.Cells(row, counter)
> counter = counter + 1
> Sheet1.Cells(5, 6) = Sheet8.Cells(row, counter)
> counter = counter + 1
> Sheet1.Cells(5, 9) = Sheet8.Cells(row, counter)
> counter = counter + 1
> Sheet1.Cells(5, 12) = Sheet8.Cells(row, counter)
> counter = counter + 1
> counter = 1
> Sheet1.PrintOut
> Sheet8.Cells(row, 5) = "PRINTED"
>
> For I = I To 300
> Next I
> I = 0
>
> End If
> Next row
>
> End Sub

--

Dave Peterson