MrExcel.com - Your One Stop for Excel Tips & Solutions

Continuation of old question

Posted by Deb on May 29, 2000 9:17 PM

All right for those that don't know I print out the first sheet of about 20 workbooks for about 20 people

A lot of the workbooks are in not in use every day. This code is planted into every sheet now with the correct names in each workbook. What i would like to do is "now" Is search through the twenty workbooks and if the value in B2 is yesterdays date I would like to print out the first persons sheet then the second persons then the hird and so on.

So If workbooks 1 5 and 7 have 5/29/00 then i want to print out those workbook 1 with pegs name then workbook5 with pegs name then workbook 7 with pegs name.

Now Joe does not get worksheet 5 so next it would print out worrkbook1 with joes name then worksheet 7 with joes name.

This sheet works fine but Would like to save myself the filing time to seperate 300 or so copies.

Thank you to Celia who Helped program this
Sub Workbook1()
With Sheets("Summary")
'Enters recipient name
.PageSetup.RightHeader = "&14File"
'Prints
.PrintOut Copies:=1
'Enters recipient name
.PageSetup.RightHeader = "&14Peg"
'Prints
.PrintOut Copies:=1
'Enters recipient name
.PageSetup.RightHeader = "&14Joe"
'Prints
.PrintOut Copies:=1
'Enters recipient name
.PageSetup.RightHeader = "&14Rob"
'Prints
.PrintOut Copies:=1
'Enters recipient name
.PageSetup.RightHeader = "&14Ken"
'Prints
.PrintOut Copies:=1
'Clears recipient name
.PageSetup.RightHeader = ""
End With
End Sub

Posted by Celia on June 02, 0100 5:29 PM


Deb
There is probably a much better way of doing it, but try the following.

Create a macro for each name similar to this :-

Sub Peg()
Dim name As String
Dim WS, wbArray(1 To 3) As Worksheet
name = "Peg"
Set wbArray(1) = Workbooks("Whateve

Posted by Celia on June 02, 0100 6:43 PM

Deb

As an alternative to trying to use the WORKDAY function, a check on the date in B2 could be made by use of either an input box or a date input to a worksheet cell.

For example, in the workbook (“The WorkbooK”) that contains the macros, input the date to be checked in a cell (“A1”) on a sheet (“The Sheet”), then change the macro :-

Sub Peg()
Dim theSheet As Worksheet, theDate As Range
Set theSheet = Workbooks("The Workbook").Sheets("The Sheet")
Set theDate = theSheet.Range("A1")
Dim name As String
Dim WS, wbArray(1 To 3) As Worksheet
name = "Peg"
Set wbArray(1) = Workbooks("Whatever.xls").Sheets("Sheet1")
Set wbArray(2) = Workbooks("Whomever.xls").Sheets("Sheet1")
Set wbArray(3) = Workbooks("Wherever.xls").Sheets("Sheet1")
For Each WS In wbArray
WS.Activate
If Int(Range("B2")) = Int(theDate) Then
With WS
.PageSetup.RightHeader = "&14" & name
.PrintOut copies:=1
.PageSetup.RightHeader = ""
End With
End If
Next
End Sub

Celia