Excel Tip: Copying and pasting a row automatically

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
    Selection.Copy
    Sheets("ActualJobs").Select
    If ActiveSheet.AutoFilterMode = True Then
    ActiveSheet.AutoFilterMode = False
    End If
    Range("A10001").End(xlUp).Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Rows("1:1").Select
    Selection.AutoFilter
Else
Exit Sub
End If

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)

Note that the code requires all rows that have data to have row A populated.

Once we have entered the code, we can select File, Close and return to Microsoft Excel.

Finally, we want to attach this code to a shortcut key. we can do this by clicking Macros again selecting our Macro from the list and choosing Options. Here we can enter the key that when pressed with the Ctrl key will activate the Macro. I chose J for job.

Then all we need to do to copy a recurring job to the ActualJobs sheet is click any cell in the row on the recurring job sheet and press Ctrl J.

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".