Code needed to print multiple workbooks

[madbloke]

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?

 

[Bernie Deitrick]

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]

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)

 

[Bernie Deitrick]

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

HTH,
Bernie
MS Excel MVP