Using mail merge in excel without word

Question

I need help to set up macros in excel spreadsheet that can print tax receipt. I have one tab contains data and one tab is the tax receipt template. How can I set up macros to choose a range of records to print individual tax receipt?

Thank you.

john mcghie · Answer

Ah! &nbsp;Two sheets with entirely dissimilar structures. &nbsp;That's what I suspected, which is why I asked the question.&nbsp;So my answer would be: "I wouldn't merge them!" The main reason being that the Time and Wages sheet presumably contains complex calculation logic that you do not want replicated into multiple instances (because if you do, you will get a maintenance nightmare when anything in the rules changes). You would have to track down, and update, every single worksheet.&nbsp;What I would do is "link" the two sheets, so that one is simply drawing data from the other.&nbsp;Whether you link from Time Sheet to Pay Sheet, or from Pay Sheet to Time Sheet depends on which person you expect to do the most updating.&nbsp;If you want each employee to fill in their own timesheet, and the Pay Lady (it's always a lady, men don't have the patenince to do this properly...) to calculate the wages, I would link from the Pay Sheet to the Time Sheet. &nbsp;So you place a set of 31 rows on the timesheet containing each day of the month, with columns for start time, break time, finish time, leave type etc.&nbsp;Then you put a row for each employee in the Pay Sheet, which draws the data from each employee's time sheet and computes their wages.&nbsp;Some design considerations: &nbsp;When you retrieve the employee's time, you MUST also bring across the DATES. &nbsp;And you should time-stamp the data with the date and time that you retrieved it. Then you need to make provision to store the raw data for up to seven years (in Australia you have to keep financial data that affects the employee's tax position for up to seven years; I do not know which jurisdiction you are in, or what your retention requirements are, but there will be some!)&nbsp;You will need to import the data into "Helper Columns" (which can be hidden) so that you can implement "Closing" logic to the data import (If the entry is for a date prior to last Thursday, and I already have data in this row, then do not import again...) to prevent the employee going back and "adjusting" last week's attendance. You need to keep accrual&nbsp;totals for things such as leave and sick day entitlement and decrement them.&nbsp;Excel is not the tool to use to do this job! The logic becomes very complex, the spreadsheet will become very slow if there are a lot of employees, and the impact to the business from a logic error is potentially catastrophic.&nbsp;I would keep the employee Time Sheet in Excel, perhaps; but rely on a proper accounting back-end such as MYOB (https://www.myob.com) to do the calculation and store the data. Applications like that have decades of development and refinement: the chances of getting a catastrophic risk from a logic error is infinitesimal -- and they're easier to use than Excel for this purpose.&nbsp;Hope this helps

xlsage · Answer

Mimi Wong&nbsp;I'm writing a response for other users with the same goal as I've done this for clients many times--mail merge in Excel without Word can be so much more streamlined. You do need basic VBA skills though.&nbsp;1) Create a mapping between the source data and the destination (template) cells. If each record has its own row in the source, for example, you could map column 1 (source name) to cell D1 (template name), column 2 (address) to cell E2 (template address), etc.&nbsp;2) Select the rows in the source to merge. In VBA, iterate through each selected row.&nbsp;3) In each iteration, copy every source cell to the mapped destination cell, then save the template as it's own XLS file and publish it to PDF.&nbsp;Now you have an Excel file and a PDF file for each source record.&nbsp;If you don't know VBA, you can hire a contractor to put this functionality into your spreadsheet without too much cost. There's a visual of the process attached and at http://wvcrest.com/xl/&nbsp;Good luck!

john mcghie · Answer

Hi Mimi:&nbsp;Your question is too broad in its current form. &nbsp;What I suggest you do is to record a Macro to print just one tax receipt. &nbsp;Post the code that gets recorded here, and we can show you how to modify it to enable you to select a record to print.&nbsp;We will need details such as "how do you decide which record to print?", "which version of Excel are you using?" and "Which licence type do you have?".&nbsp;Excel is a very large product, and not all licences have access to all of the features: we need to know what will work in your setting and what won't.&nbsp;You will find a "Record a Macro" topic in the Excel Help that will get you started.&nbsp;Hope this helps

deleted · Answer

Is there are step by step way to merge two excel worksheets into one, WITHOUT using WORD?Any hints or suggestions would be appreciated.&nbsp;&nbsp;

john mcghie · Answer

Hi Shiloh:&nbsp;There are several diffeent ways: but your question is far too broad to give a useful answer.&nbsp;We need to know the data structure of the sheets, and what you mean by "Merge" :-)&nbsp;Cheers

Forum Discussion

Using mail merge in excel without word

9 Replies

Resources