From the Not Just Numbers blog:
Regular readers of my blog will know that I am not normally a big fan of Macros. I would argue that in the majority of situations that I see them used, they are unnecessary and often result from a lack of understanding of how Excel can address the problem using its standard functionality.
Naturally this is not always the case and one particular situation that cannot easily be addressed using standard functionality is automatically copying a line of data from one row to another.
Before we get into it, I think it is important to point out that there are many times that I speak to a client who thinks this is what they need, when what they really need is a link between the two lists, or one list with a category field that can be used to report two lists from the same data.
I came across an example of when this was genuinely need just last week. In automating an accountancy practice’s job tracking, I had brought all of their data together into three distinct lists – Clients, Recurring Jobs and actual Jobs.
This allowed them to store information at the right level:
Clients – One row per client (e.g. XYZ Ltd), holding all information pertaining to that client.
Recurring Jobs – One row per each recurring job (e.g. XYZ Ltd Annual Accounts), holding the current information relating to that particular recurring job.
Actual Jobs – One row for each actual job, which could be a one-off job or an instance of a recurring job (e.g. XYZ Ltd Annual Accounts 2014). This holds all information for that specific job.
Each Client, Recurring Job and Actual Job then has its own unique reference that is used to link them and access information from the other lists (using lookups and SUMIFs, etc.)
However, on the Actual Jobs list, we need a new instance each time a recurring job comes around. We also want to copy the recurring information over, with the ability to edit it just for this instance. That means copying and pasting a number of columns from Recurring Jobs to Actual Jobs each time.
This can, of course, be done manually, however that can be cumbersome and prone to error – not least in that in this particular instance I wanted to use “paste as values” rather than a full paste, as this could override conditional formatting that I had used on the Actual Jobs sheet.
However we can create a relatively simple macro to do it for us.
To add a macro you will need the Developer Ribbon which you can enable by selecting File, Options, Customize Ribbon and ticking the Developer option on the right hand side.
From the Developer Ribbon, click Macros and enter the name (e.g. CreateJob) that you want to give this Macro and click Create.
Then paste the following code between the lines already there, being Sub CreateJob() (or whatever name you gave the Macro) and End Sub:
If ActiveSheet.Name = “Recurring Job Data” Then
Range(Cells(ActiveCell.Row, “A“), Cells(ActiveCell.Row, “F“)).Select
If ActiveSheet.AutoFilterMode = True Then
ActiveSheet.AutoFilterMode = False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
The yellow items will need to be changed for your individual requirements:
Recurring Job Data is the name of the worksheet that we want to copy from. This part of the code, checks whether we are on this worksheet when the code is run, and exits the code if we are not. That stops the code running when we are on the wrong worksheet.
The A and F are the columns (from and to) that we want to copy over.
ActualJobs is the worksheet that we want to copy to.
A10001 is a cell in the first column of the worksheet that we want to copy to, beyond the bottom of the range of the existing data.
The code works as follows:
- Check the active worksheet is “Recurring Job Data” and exit the Macro if not
- Select columns A to F of the current row (the row that that the currently selected cell is in)
- Copy these cells
- Switch to the “ActualJobs” worksheet
- Switch off Autofilter (as if the list is filtered this may cause the code to incorrectly determine the bottom of the existing list
- Select cell A10001
- Go up column A until it finds some data and selects the cell below this
- Paste as values
- Switch Autofilter back on (assuming that the headings are in row 1)
If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies “The 5 Excel features that you NEED to know” and “30 Chants for Better Charts”.