Help needed to solve printing error

  • Thread starter Thread starter madbloke
  • Start date Start date
M

madbloke

I have the following code set up to print varying numbers of exce
workbooks, based on info from a master list, and it works fine, excep
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 an
63212 (or something).

Any ideas on how I can get the macro to skip items which don't requir
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 Su
 
what about that:
'c gets the number of prints needed
c = Worksheets("Sheet1").Cells(b, 8).Value

'print needed amount of workbook
Click to expand...

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

else

ActiveWorkbook.Close False

end if
 
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.
 
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.)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel Copy cells from one worksheet to another workseet - increment row & do again 0
Macro help needed 4
Need Help in Selecting PrintArea Dynamically .. 0
Excel How to capture data to a specified excel sheet(first workbook) of two different workbooks from the 0
Cell values to an array, then array transpose to another workbook 8
Code needed to print multiple workbooks 3
Macro for all Excel Workbooks to print to PDF 2
Print function parsing help 5

Back
Top