MrExcel Publishing
Your One Stop for Excel Tips & Solutions

help out the new girl


Posted by deb on May 21, 2000 9:36 PM

I have quite a few workbooks
on the first page of each workbook i have a summary page titled summary
now i need to print out this first page for about 15 people
such as mark gets the first page of these two workbooks
and john gets one of those and three more
any way to get excel to do this?
Keep in mind i am good with excel and formulas but i have not used any of the macros or visual basic
Thanks deb


Posted by Celia on May 22, 2000 4:06 AM


Deb
From one printing to the next, if there are no changes in the names or quantities of either the workbooks, or the worksheets, or each print quantity, you could use the macro recorder to record your actions.
Perhaps I haven’t completely understood the situation ?
Celia


Posted by Ivan Moala on May 22, 2000 4:09 AM

Hi deb
haven't really look into this in great depth
but my 1st thoughts are,
Set up a named range for each person that reflects
their Range to print requirements and set your
print area to this eg.

1) Select Print range for Mark
2) If your formulabar is visible click on the
formula drop list and Type "Mark"
3) Do this for for the others.
When you are ready to print just select the named
range on the formula drop list / then select
File / Print Area / Set printArea and Print.

Then procede down the drop list selection.

Ivan

Posted by deb on May 22, 2000 8:37 PM

John Bob Greg Jay
workbook1 x x x x
workbook2 x x
workbook3 x x x
workbook4 x x

Posted by deb on May 22, 2000 8:41 PM

oh well that didn't work well but the workbooks do change but the same people get the same work books all the time it is just that i might only need 3 workbooks one day and 20 the rest and i have 20 people that i need to sort these for so if i print out 20 copys of 20 workbooks it takes me so long to sort them if i could get it to print out the correct workbooks in the correct order it would be perfect then all i would need to do is count through the print outs

Posted by Celia on May 23, 2000 2:12 AM

Deb
My understanding is as follows :-
-Only the “Summary” sheet of each workbook is to be printed.
-For each workbook, specific persons are to receive a fixed number of copies.
-The number of workbooks to be printed may vary.
Based on the above, one way of doing what you want is as follows. It is not very elegant and there is very probably a better way – but it can be done without knowledge of VBA.

Add a macro to each workbook (could be attached to a button on the worksheet) that prints the required number of copies and at the same time identifies the recipient.
The recipient could be identified by printing his name either as a print header or in one of the worksheet cells.
An initial macro can be created with the macro recorder in one of the workbooks and then copied to the other workbooks with relevant changes made to the code for the different recipient names and number of copies.

You may find it useful to use the recorder to create such a macro and then look at the code recorded.
The recorded code can be reduced to the following. Please note that I have not tested this code – my printer’s not working.

Celia

EITHER :-
‘Identifies recipient in cell F1 of the printed sheet
Sub PrintSummary1 ()
With Sheets("Summary")
'Enters recipient name
.Range("F1").Value = "MARK"
'Prints
.PrintOut Copies:=1
'Enters recipient name
.Range("F1").Value = "PETER"
'Prints
.PrintOut Copies:=1
'Clears recipient name
.Range("F1").ClearContents
End With
End Sub

OR :-
'Identifies recipient in right header of the printed sheet
Sub PrintSummary2()
With Sheets("Summary")
'Enters recipient name
.PageSetup.RightHeader = "MARK"
'Prints
.PrintOut Copies:=1
'Enters recipient name
.PageSetup.RightHeader = "JOHN"
'Prints
.PrintOut Copies:=1
'Clears recipient name
.PageSetup.RightHeader = ""
End With
End Sub


Posted by deb on May 24, 2000 2:47 AM

Thanks but I have a new question for you

Thanks you to ivan and celia for your help
Celia your program worked great.
One more question if you have time.
I am creating a sheet for due dates for projects.
in column c there is a unit in months such as 12.
Then in column f there is a start date "04/24/00".
In column g there is a due date. Is there an easy way to get To add the number of months in column c to the date in column f and store that in column g.


Posted by Celia on May 24, 2000 4:28 AM

Re: Thanks but I have a new question for you

Thanks you to ivan and celia for your help

Deb
Enter the following formula in cell G1 and drag it down :-
=DATE(YEAR(F1),MONTH(F1)+C1,DAY(F1))
Celia

Posted by deb on May 24, 2000 7:34 PM

Re: Thanks but I have a new question for you

Thanks you to ivan and celia for your help

all right that did not work for me.
More specifics.
h3 is the date. 5/10/99
f3 is the number of months. 24
i3 is the due date. (should be 5/10/01)
=DATE(YEAR(H3),MONTH(H3)+F3,DAY(H3))
it gives me a "#num!" am i making an error?

Posted by Celia on May 24, 2000 11:44 PM

Re: Thanks but I have a new question for you

Deb
Are you sure you have the entries in the correct cells?
If yes, the problem must be cell F3. Does this just contain the number 24 formatted either as general or as number?
If you can't get it to work, mail me a workbook with the formula entered which shows the result "#num" so that I can take a look.
Celia

Posted by deb on May 25, 2000 7:41 PM

Problem solved

I had to cut out the information for the email to protect confidentiality for work and when I pasted the stuff into a new sheet it worked perfectly.
So I am deleting the worksheets and pasting them into a new workbook. Seems to be working great. Thanks again.