Code needed to print multiple workbooks
 

I have a large collection of workbooks, and need to be able to print a
specified number of each from a master workbook which looks like this:

Title No. Reqd
A 3
D 2
F 0
X 2

Originally, this was meant to be only one workbook, with a worksheet
for each title, so I had the following code -

Sub Dostuff()

Dim a As String
Dim b As Integer
Dim c As Integer
Dim d As Integer
Dim e As Integer

'Clear variable
a = ""

'get number of worksheets
d = Worksheets.Count

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

For b = 2 To d

a = Worksheets("Master").Cells(b, 1).Value
c = Worksheets("Master").Cells(b, 2).Value

'print needed amount of worksheet

Worksheets(a).PrintOut Copies:=c

'loop till end

Next b

End Sub


This worked perfectly, but now the worksheet contents have been made
into individual workbooks (all stored in the same folder, and with
names corresponding to the 'Title' column on the master document). What
changes do I need to make to the above code to work with seperate
workbooks? Or do I need to start again from scratch?


--
madbloke
------------------------------------------------------------------------
madbloke's Profile: http://www.excelforum.com/member.php...o&userid=14422
View this thread: http://www.excelforum.com/showthread...hreadid=392477

Code needed to print multiple workbooks
 

Assumes: Title is the file name, without the .xls, and all files (including the one with the Master
sheet) reside in the same folder.

Sub Dostuff2()

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

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

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

a = Worksheets("Master").Cells(b, 1).Value
c = Worksheets("Master").Cells(b, 2).Value

'print needed amount of worksheet

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

'loop till end

Next b

End Sub

HTH,
Bernie
MS Excel MVP


"madbloke" wrote in message
...

I have a large collection of workbooks, and need to be able to print a
specified number of each from a master workbook which looks like this:

Title No. Reqd
A 3
D 2
F 0
X 2

Originally, this was meant to be only one workbook, with a worksheet
for each title, so I had the following code -

Sub Dostuff()

Dim a As String
Dim b As Integer
Dim c As Integer
Dim d As Integer
Dim e As Integer

'Clear variable
a = ""

'get number of worksheets
d = Worksheets.Count

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

For b = 2 To d

a = Worksheets("Master").Cells(b, 1).Value
c = Worksheets("Master").Cells(b, 2).Value

'print needed amount of worksheet

Worksheets(a).PrintOut Copies:=c

'loop till end

Next b

End Sub


This worked perfectly, but now the worksheet contents have been made
into individual workbooks (all stored in the same folder, and with
names corresponding to the 'Title' column on the master document). What
changes do I need to make to the above code to work with seperate
workbooks? Or do I need to start again from scratch?


--
madbloke
------------------------------------------------------------------------
madbloke's Profile: http://www.excelforum.com/member.php...o&userid=14422
View this thread: http://www.excelforum.com/showthread...hreadid=392477

Code needed to print multiple workbooks
 

That did the trick.

I had to add the folder path to it, though. Is there any way of makin
it relative (i.e. it works wherever the files and master sheet ar
saved)

--
madblok
-----------------------------------------------------------------------
madbloke's Profile: http://www.excelforum.com/member.php...fo&userid=1442
View this thread: http://www.excelforum.com/showthread.php?threadid=39247

Code needed to print multiple workbooks
 

Workbooks.Open ThisWorkbook.Path & "\" & a & ".xls"

HTH,
Bernie
MS Excel MVP


"madbloke" wrote in message
...

That did the trick.

I had to add the folder path to it, though. Is there any way of making
it relative (i.e. it works wherever the files and master sheet are
saved)?


--
madbloke
------------------------------------------------------------------------
madbloke's Profile: http://www.excelforum.com/member.php...o&userid=14422
View this thread: http://www.excelforum.com/showthread...hreadid=392477