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: 9655
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

Customer Question

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.
Submitted: 1 year ago.
Category: Microsoft Office
Expert:  John D replied 1 year ago.
Hi,

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 http://www.wikisend.com/ (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 1 year ago.

file id# XXXXX


http://wikisend.com/download/321898/gross 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

Expert:  John D replied 1 year ago.
Steve,

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 1 year 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?

Expert:  John D replied 1 year ago.
Sure.

I have already started writing the macro. Should have it ready in about 20 mins...
Expert:  John D replied 1 year ago.

Ok here you go

 

http://www.ge.tt/8Tb2huU/v/0?c

 

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 1 year ago.

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

Expert:  John D replied 1 year ago.
Ok one moment please...
Expert:  John D replied 1 year ago.

Try this link which gas a zipped file

Attachment: 2013-01-08_005944_gross_pay_report_2013_a.zip



Let me know

 

 

Expert:  John D replied 1 year ago.
* has
Expert:  John D replied 1 year ago.

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

 

https://sites.google.com/site/zworkbook/zworkbook?pli=1

 

 

Let me know if you still need any assistance

 

 

*** RATING REQUIRED ***

Customer: replied 1 year 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?

Expert:  John D replied 1 year ago.

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 1 year ago.

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

Expert:  John D replied 1 year ago.

Ok

 

Expert:  John D replied 1 year ago.
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

Thanks
Customer: replied 1 year 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!



 

Expert:  John D replied 1 year ago.

Ok will do it in pdf..

 

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

 

 

 

 

 

 

 

 

 

John D, Bachelor's Degree
Category: Microsoft Office
Satisfied Customers: 9655
Experience: Bachelor of Science - Engineering Consultant
John D and 2 other Microsoft Office Specialists are ready to help you
Expert:  John D replied 1 year ago.

Thanks Steve

 

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

 

 

Expert:  John D replied 1 year ago.

Ok here you go

 

http://www.ge.tt/6lMW0yU/v/0?c

 

 

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

 

 

Customer: replied 1 year ago.

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


Expert:  John D replied 1 year ago.

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

 

graphic

Customer: replied 1 year 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 clear...it 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.


Expert:  John D replied 1 year ago.
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 1 year ago.

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

Expert:  John D replied 1 year ago.
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

http://www.ge.tt/4zM5JyU/v/0?c


Make sure you click Yes when prompted
Customer: replied 1 year ago.

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

Expert:  John D replied 1 year ago.
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 1 year ago.

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

Expert:  John D replied 1 year ago.
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 1 year 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?


Expert:  John D replied 1 year ago.
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 1 year 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!


Expert:  John D replied 1 year ago.
Ok
Expert:  John D replied 1 year ago.
Ok here is the macro for sending Excel attachment

http://www.ge.tt/5Hab2zU/v/0?c

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 1 year 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!



Expert:  John D replied 1 year ago.
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 1 year 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.


Expert:  John D replied 1 year ago.
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 1 year ago.

wikisend file id 417818


 


http://wikisend.com/download/417818/110212.xlsm


 


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


Expert:  John D replied 1 year ago.
How were you able to convert and send the sheets as pdf. Could you outline the steps

Customer: replied 1 year 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!


Expert:  John D replied 1 year ago.
hmm... this is the file that you just sent me


http://wikisend.com/download/417818/110212.xlsm



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 1 year ago.

is there a better way to send you this file?

Expert:  John D replied 1 year ago.
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 1 year ago.
File 417818. Sorry bout that!
Expert:  John D replied 1 year ago.

SAME FILE!!!

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

 

Or you can use this file sharing site instead

 

http://www.ge.tt/#!/





Customer: replied 1 year ago.

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


 


http://ge.tt/3O5Iv3V/v/0?c


 


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


Expert:  John D replied 1 year ago.
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

Thanks
John D, Bachelor's Degree
Category: Microsoft Office
Satisfied Customers: 9655
Experience: Bachelor of Science - Engineering Consultant
John D and 2 other Microsoft Office Specialists are ready to help you
Expert:  John D replied 1 year ago.

Thanks Steve.

 

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

 

 

Expert:  John D replied 1 year ago.

Ok here is the test file

 

http://www.ge.tt/1aoeA7V/v/0?c

 

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 1 year 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.


Expert:  John D replied 1 year ago.

Ok please try this file

 

http://www.ge.tt/3y5zF7V/v/0?c

 

Try sending both emails and let me know

 

 

 

 

 

Customer: replied 1 year 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.


Expert:  John D replied 1 year ago.
Great

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 1 year ago.

i should be fine without that. thanks-


Expert:  John D replied 1 year ago.

Here is the full version

 

http://www.ge.tt/7efTZ7V/v/0?c

 

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 1 year 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.


Expert:  John D replied 1 year ago.
lol...

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

JustAnswer in the News:

 
 
 
Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.
 
 
 

What Customers are Saying:

 
 
 
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
< Last | Next >
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
  • Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland
 
 
 

Meet The Experts:

 
 
 
  • jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
< Last | Next >
  • http://ww2.justanswer.com/uploads/JS/jstinehelfer/2012-6-6_215146_Pictureofme1.64x64.png jstinehelfer's Avatar

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
  • http://ww2.justanswer.com/uploads/JA/JasonJames122/2011-12-16_135647_jasonjamesheadshotweb.64x64.jpg JasonJames122's Avatar

    JasonJames122

    Computer Enthusiast

    Satisfied Customers:

    0
    I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business
  • http://ww2.justanswer.com/uploads/JE/jessmagz/2012-6-6_18129_jm.64x64.jpg Jess M.'s Avatar

    Jess M.

    Computer Support Specialist

    Satisfied Customers:

    301
    Computer Software Specialist for more than 10 years
  • http://ww2.justanswer.com/uploads/CH/chutz747/2011-11-6_14231_me.64x64.jpg The-PC-Guy's Avatar

    The-PC-Guy

    Computer Manager

    Satisfied Customers:

    274
    20 years experience providing remote computer support
  • http://ww2.justanswer.com/uploads/SP/spherrod/2012-6-6_174244_1000852.64x64.JPG Steve Herrod's Avatar

    Steve Herrod

    Computer Support Specialist

    Satisfied Customers:

    125
    Trained in Microsoft Office from 2000 through to latest version 2010.
  • http://ww2.justanswer.com/uploads/SE/Seashore2011/2011-12-6_33418_111205223015.64x64.jpg Chris L.'s Avatar

    Chris L.

    Support Specialist

    Satisfied Customers:

    124
    Certified Expert with over 10 years experience.
  • http://ww2.justanswer.com/uploads/KR/krkljatorm/2012-6-24_153417_ITMiro.64x64.jpg IT Miro's Avatar

    IT Miro

    Computer Scientist

    Satisfied Customers:

    97
    Bachelor's Degree in Information Technology, Microsoft Certified Professional