How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask John D Your Own Question
John D
John D, Bachelor's Degree
Category: Microsoft Office
Satisfied Customers: 9666
Experience:  Bachelor of Science - Engineering Consultant
Type Your Microsoft Office Question Here...
John D is online now
A new question is answered every 9 seconds

i am trying to create a master worksheet for payroll where

This answer was rated:

i am trying to create a master worksheet for payroll where all data and values from that worksheet fill multiple worksheets (1 each for each employee). example: on the master worksheet (sheet 1) row 5 col A is employee name, col b is email address, col c is commission, col d is overtime pay, etc. the last column would be the total gross pay. so then, sheet two would be for the first employee listed on the master worksheet. row 5 colum a would be (=sheet1!A5) which is the employee name, row 6 column A would be (=sheet1!B6) which is the commission value from the master for that employee and so on. sheet 3 would be for the next employee listed on the master so all values would be one row down on the master but the same column as sheet 2 (the first employee). i would also like to create a macro to email employees their individual worksheet (based ont he email address that is listed on it taken from the master) once all data is entered.

I can help you with the first part as the emailing part requires that you are using an email client such as Outlook for it to work

If that is ok with you please send me the file with the instructions by uploading the file at (no need to signup) then copy the "File ID" number (or the download link) that you will be given and paste it here in your reply.

(if the file has sensitive information let me know before you upload it)
Customer: replied 4 years ago.

file id# XXXXX pay report 2013.xlsx


I went ahead and went through every worksheet and put in the formulas manually. A pain in the butt but i really wanted to get it done. For future reference if you could give me a shortcut for that i would appreciate it! what i need next is to create a macro where every worksheet has a button that i can hit and it would email that single worksheet to the employee listed on it. i would be using outlook for this. please let me know if you can help. feel free to call me at XXX-XXX-XXXX as well. thanks-


steve becker


That can be easily done with a macro. I can write a macro that will pull the data from sheet1 and automatically update all the sheets (no matter how many sheets you have or what their names are)

Or we can create a button on a template sheet which will create a new sheet with the pulled values.

Which method would you prefer
Customer: replied 4 years ago.

whatever you recommend i guess. while we are at it....can we change the worksheet tab name to be whatever is entered in the "employee name" cell?


I have already started writing the macro. Should have it ready in about 20 mins...

Ok here you go


To run the macro click the button on the Summary sheet. Every time you run the macro it deletes any existing employees sheets and creates a new set of sheets for all the employees listed on the Summary sheet


Please note that since there is a macro in the file you need to make sure that you have your Excel Options configured so that macros are allowed to run, otherwise nothing happens when you click the button. If you need assistance in this regard please let me know,.and provide the version of Excel you are running



Hope this is ok. If you have any further questions do not hesitate to ask


Thank you for rating the answer "Excellent" or "Good" if I was able to answer your question




Customer: replied 4 years ago.

i cannot seem to open this link. can you help with it?

Ok one moment please...

Try this link which gas a zipped file


Let me know



* has

Steve here is another link with unzipped file if you're still not able to download the file



Let me know if you still need any assistance




Customer: replied 4 years ago.

hi john-i was able to download the file and it looks great. i was just hoping we could add another macro where there is a button on each worksheet that allows me to email that single worksheet through outlook to the employee email address listed on that worksheet. is this possible?

Great. Glad it worked


If I understand you correctly you want that sheet itself to be sent as a file attachment, and not as text data in the body of the email. Correct?



Customer: replied 4 years ago.

sending as a pdf attachement would be fine. i think that would be the best way.



Ok Steve I looked into the email issue, first in order to run the macro from each individual sheet a separate button associated with a macro must be placed on every sheet.

Secondly, when sending the attachment as a pdf file Outlook security gets in the way prompting the user to confirm prior to sending. While sending it as an Excel attachment. is done seamlessly without further step by the user. Let me know your preference

In the meantime you can go ahead and rate the answer for the main routine if you are happy with the result, and you can add bonus later when the email part is done

Customer: replied 4 years ago.

i think i would prefer the pdf even if it requires the extra step. the reason is i dont want my sheet manipulated in any way and the boys would be able to do that in excel spreadsheet form. if this can be done early today that would be awesome. that way i could give it a go later today for this friday's pay run. man you were answering pretty late last night! dont you sleep? i really appreciate it!


Ok will do it in pdf..


Should have it ready shortly. Meanwhile thank for remembering to rate










John D and 3 other Microsoft Office Specialists are ready to help you

Thanks Steve


Give me a few minutes to finalize the code and upload the file....



Ok here you go



Please go ahead and test it and let me know if it needs any tweaking



Customer: replied 4 years ago.

i tested it and it looks like emails are being sent without the attachments.

hmm.. I am getting the pdf attachment on my end



Customer: replied 4 years ago.

not opposed to letting you plug into my computer to have a go at it. might i need to configure outlook settings differently to allow attachments to send via macro? just to be is only that single worksheet being sent to the email address listed on it rather than the who workbook right? that would not be good! if you want to remote in my computer give me a call at XXX-XXX-XXXX and tell me what to do.

No it is not sending the whole workbook. Only the sheet that you click the button from

Here is the pdf files generated for Bassi, Jerry

Attachment: 2013-01-08_194808_gross_pay_report.pdf

As for remote accessing your computer the site does not permit it (the feature is still being tested)
Customer: replied 4 years ago.

so then a setting on outlook? i tested it again and emails are being sent just with no attachments.

Most likely you have a setting issue as you said which I cannot really control

Let's give it one more shot. Please test this stand alone sheet (do not change email address in B8) and I will let you know what gets sent

Make sure you click Yes when prompted
Customer: replied 4 years ago.

sent. i am not getting the prompt that asks "are you sure you want to send?"

No there was no attachment!

I have no idea why this is happening on your end. I tried it on two systems and the attachment does come through every time

Does it save the pdf on your current directory? Can you try sending it manually? Can you try running the macro on another computer?

The only thing I can do is write the other code which sends the excel sheet instead of pdf hoping this will work. But I have to leave now. Will be back in the evening
Customer: replied 4 years ago.

can you try the excel format. that will have to do.

Ok will work on it when I get back to my base

Also could you please respond to the three questions in my last message, It could help me understand the problem
Customer: replied 4 years ago.

just turned off microsoft essentials and it looks like the pdf is being attached now! is there a way to keep essentials on and still send?

Whew! Glad it worked.

I am not aware of any conflict with MS Essentials nor have I come across this issue before. But if that if that is what it takes then I suggest to try to use it while that program is disabled
Customer: replied 4 years ago.

on second thought...only one pdf was attached and subsequent emails were sent with no attachment. please send me the excel attachment format when you can. it must be easier that way, thanks!

Ok here is the macro for sending Excel attachment

I also added a pop up message to show you where the temporary file has been saved on your computer prior to sending that same file by email. That way you can check to see what each sent attachment looks like

Hope this helps

Customer: replied 4 years ago.

Hi John-


The sheet looks great and in excel format I seem to be able to email everything fine. Still, and now I am being a total pest, I would love to send in PDF format for the reason I have already stated as well as I just think that format is more professional. Is there a way we can work on getting over that Outlook block that is stopping me from doing that?


Well done on this workbook by the way. I showed my wife it last night and she was as impressed as I was at all those worksheets being created at the click of a button. Good stuff!

Great! Glad you're happy with the work

Steve as I've mentioned before that outlook issue is really another issue which is related to your own system settings and which I cannot control. We can try to diagnose it further but that will take quite of bit of time using this type of forum communication. As you know we have already been engaged in over 40 messages on this thread so far (just assume each message to review or write takes about 5 mins and you will see what mean)

Thank you for understanding
Customer: replied 4 years ago.

i understand. if i send you a workbook where pdf's were successfully sent would you be able to decipher that code and see where we are off with our workbook? if i have to be charged for a new case i guess i am alright with it.

I do not I can tell by looking at the pdf file, but you can go ahead and send it and I'll let you know
Customer: replied 4 years ago.

wikisend file id 417818


i was able to send all worksheets in pdf form when i sent this. thanks-

How were you able to convert and send the sheets as pdf. Could you outline the steps

Customer: replied 4 years ago.

I had someone help me come up with that workbook (I believe it was this site actually) and they created a macro that you see on the summary page. i would hit that after creating the worksheets and all worksheets would begin to send in PDF format to the email addresses listed. back then i would alter data on each worksheet and it would transfer to the summary page. what you created is the opposite of that, (i enter data on the summary page and it transfers to the individual worksheets) which it much easier for me. hope this helps!

hmm... this is the file that you just sent me

and the macro in that file was created by me (I can recognize my code), and it does not send pdf but excel file

Customer: replied 4 years ago.

is there a better way to send you this file?

Was it the wrong file? If so please upload the correct file and send me only the File ID and not the link

Customer: replied 4 years ago.
File 417818. Sorry bout that!


Please make sure you try to download the file yourself before sending me the ID


Or you can use this file sharing site instead!/

Customer: replied 4 years ago.

dont know why that happened. here is what i wanted you to see.


i downloaded it myself and it is the one. thanks=

Steve, the macro in that file sends Excel files as attachments not pdf files as you have been telling me. In fact I tried the first two sheets with your address so you can see what I mean

Since this pdf issue which is related to your system setting has taken so much time I think it would be appropriate to apply a rating at this point. In the meantime I will create and send you a test macro that should shed some light on what might be causing the problem on your end

John D and 3 other Microsoft Office Specialists are ready to help you

Thanks Steve.


Will send you the test macro as soon I have it ready



Ok here is the test file


First save the file to your desktop then close all excel files before opening this file


When you click the button, the macro first brings up a message telling you where the pdf file has been saved on your system so you can go there and check it if you want to see it.


Then it launches the Outlook message window so you can see the actual email with the attachment before sending it. You can then manually send it from that window


Please go ahead and test it and let me know what happens


Customer: replied 4 years ago.

the worksheet with my email address worked perfectly. i received the pdf and it looks great. your worksheet would not send and said that macros may be disabled.

Ok please try this file


Try sending both emails and let me know






Customer: replied 4 years ago.

both worksheets seem to have been emailed just fine. we are close!! if you could integrate that macro into the exsisting workbook you created for me i think we will be golden.


Ok give me a few minutes to incorporate it into the final version and test it

I assume you will not need to see file location pop up message box anymore. Correct?

Customer: replied 4 years ago.

i should be fine without that. thanks-

Here is the full version


Hope it's ok. Please feel free to get back to me if there are any issues or if you have any questions





Customer: replied 4 years ago.

looks like we are a go!!! you, my friend, are talented with those darn spreadsheets!! thanks so much for your help with this. it really make my life easier (for payroll at least). what on earth will you do with your time now that this project is complete?? thanks john.


You're very welcome Steve. Looking forward to assisting you on your future excel projects

Related Microsoft Office Questions