Help needed to solve printing error

[madbloke]

I have the following code set up to print varying numbers of excel workbooks, based on info from a master list, and it works fine, except for one thing. Whenever it comes across an item which requires 0 (zero) prints, it returns an error saying the number must be between 1 and 63212 (or something).

Any ideas on how I can get the macro to skip items which don't require printing?

Sub Printitem()

Dim a As String
Dim b As Integer
Dim c As Integer


'go down the list getting the name of the workbook and
'the amount needed

For b = 2 To Worksheets("Sheet1").Cells(65536, 1).End(xlUp).Row

'a gets the name of the workbook
a = Worksheets("Sheet1").Cells(b, 1).Value

'c gets the number of prints needed
c = Worksheets("Sheet1").Cells(b, 8).Value

'print needed amount of workbook

Workbooks.Open ThisWorkbook.Path & Application.PathSeparator & a & ".xls"
ActiveSheet.PrintOut Copies:=c
ActiveWorkbook.Close False

'loop till end

Next b

End Sub

[Snake Plissken]

what about that:

> 'c gets the number of prints needed
> c = Worksheets("Sheet1").Cells(b, 8).Value
>
> 'print needed amount of workbook

if c <> 0 then

> Workbooks.Open ThisWorkbook.Path & Application.PathSeparator & a &
> ".xls"
> ActiveSheet.PrintOut Copies:=c
> ActiveWorkbook.Close False

else

ActiveWorkbook.Close False

end if

>
> 'loop till end
>
> Next b
>
> End Sub
>
>

[madbloke]

Thanks for that, but unfortunately doesnt work. Using your amendments, if it hits a 0, it closes the master sheet.

I amended it so that it opened the unneeded sheet and then closed it without printing, which worked, but took a long time (there's about 500 sheets to go through!)

What I need it to do ideally is ignore any unneeded sheets and just open and print the ones I do need.

what about that:

> 'c gets the number of prints needed
> c = Worksheets("Sheet1").Cells(b, 8).Value
>
> 'print needed amount of workbook

if c <> 0 then

> Workbooks.Open ThisWorkbook.Path & Application.PathSeparator & a &
> ".xls"
> ActiveSheet.PrintOut Copies:=c
> ActiveWorkbook.Close False

else

ActiveWorkbook.Close False

end if

>
> 'loop till end
>
> Next b
>
> End Sub
>
>

[Snake Plissken]

>>
>> > 'c gets the number of prints needed
>> > c = Worksheets("Sheet1").Cells(b, 8).Value
>> >
>> > 'print needed amount of workbook
>>
>> if c <> 0 then
>>
>> > Workbooks.Open ThisWorkbook.Path & Application.PathSeparator & a &
>> > ".xls"
>> > ActiveSheet.PrintOut Copies:=c
>> > ActiveWorkbook.Close False

' right - so it looks that <else> command and next line is not required...


>> end if
>>
>> >
>> > 'loop till end
>> >
>> > Next b
>> >
>> > End Sub
>> >

[Dave Peterson]

Option Explicit
Sub Printitem()

Dim a As String
Dim b As Long
Dim c As Long

'go down the list getting the name of the workbook and
'the amount needed

For b = 2 To Worksheets("Sheet1").Cells(65536, 1).End(xlUp).Row

'a gets the name of the workbook
a = Worksheets("Sheet1").Cells(b, 1).Value

'c gets the number of prints needed
c = Worksheets("Sheet1").Cells(b, 8).Value

'print needed amount of workbook
If c > 0 Then
Workbooks.Open ThisWorkbook.Path _
& Application.PathSeparator & a & ".xls"
ActiveSheet.PrintOut Copies:=c
ActiveWorkbook.Close False
End If
'loop till end
Next b

End Sub

(Just making it easier to read.)


madbloke wrote:
>
> I have the following code set up to print varying numbers of excel
> workbooks, based on info from a master list, and it works fine, except
> for one thing. Whenever it comes across an item which requires 0 (zero)
> prints, it returns an error saying the number must be between 1 and
> 63212 (or something).
>
> Any ideas on how I can get the macro to skip items which don't require
> printing?
>
> Sub Printitem()
>
> Dim a As String
> Dim b As Integer
> Dim c As Integer
>
> 'go down the list getting the name of the workbook and
> 'the amount needed
>
> For b = 2 To Worksheets("Sheet1").Cells(65536, 1).End(xlUp).Row
>
> 'a gets the name of the workbook
> a = Worksheets("Sheet1").Cells(b, 1).Value
>
> 'c gets the number of prints needed
> c = Worksheets("Sheet1").Cells(b, 8).Value
>
> 'print needed amount of workbook
>
> Workbooks.Open ThisWorkbook.Path & Application.PathSeparator & a &
> ".xls"
> ActiveSheet.PrintOut Copies:=c
> ActiveWorkbook.Close False
>
> 'loop till end
>
> Next b
>
> End Sub
>
> --
> madbloke
> ------------------------------------------------------------------------
> madbloke's Profile: http://www.excelforum.com/member.php...o&userid=14422
> View this thread: http://www.excelforum.com/showthread...hreadid=393788

--

Dave Peterson

[madbloke]

Spot on! Cheers.

I'd fallen into the trap of assuming an IF needed an ELSE.