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" <madbloke.1t6w2m_1123074848.1229@excelforum-nospam.com> wrote in message
news:madbloke.1t6w2m_1123074848.1229@excelforum-nospam.com...
>
> 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
>

[madbloke]

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)?

[Bernie Deitrick]

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

HTH,
Bernie
MS Excel MVP


"madbloke" <madbloke.1taful_1123239957.176@excelforum-nospam.com> wrote in message
news:madbloke.1taful_1123239957.176@excelforum-nospam.com...
>
> 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
>