Search Not Just Numbers


Tuesday, 4 November 2014

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


  1. I think that

    Range("A10001").End(xlUp).Offset(1, 0).Select

    can be replaced by

    Cells(Rows.Count, "A").End(xlup).Offset(1,0).Select

    in order to get a solution that doesn't depend on the number of rows of "Actual Jobs" sheet (goes up from the last row in the sheet).

    Sincerely yours,


    1. Thanks Carlos. That's a useful amendment to the code. I told you VBA's not really my thing! :)

  2. In the event you came across a single, next that has to be any fraudulence. It is possible to simply acquire discount rates however, not since low-cost because you can consider. Thinking will be that you are able to save lots of funds also in mere a tiny sum yet when you make trades that along with your close friends, an individual tiny income any time accrued together with actually offer a huge difference coming from in which of one's money. rolex replica uk Several sites offer you special discounts once you advise their particular web sites in your close friends, since basic since revealing their particular web sites in your social media marketing consideration... cartier replica uk The particular seek out the particular low-cost designer purse just isn't just like prize looking. chanel replica online You don't need any guide to work the location where the appreciated designer purse is. fake prada By learning to be a associate for some store on the web purchasing sites, you may acquire notifications, special discounts, and also bargains each and every time. fake hublot Commence exploring and also blocking on the web accessories searching powerplant.

  3. The rental tree?These days louis vuitton replica, more folks are able to rent a living Christmas tree - replica watches a potted tree that is dropped off at your house, picked up after the holidays, and whisked away to be re-planted rolex replica. It's typically pricier than either fake or a (conventional) real tree replica handbags, but the upshots are numerous, allowing you ulysse nardin replica to sidestep the tricky environmental question marks hublot replica surrounding both.

  4. Stuff it if you don't use it in a brief time Stuff your leather louis vuitton replica handbag with tissue paper or soft cloth to preserve its shape as excellent as attainable. The materials within will hold the bag from collapsing, and this is why you can see some stuffs in new handbags in the bag store.Prada messenger bag is a chic oversized chanel replica handbag creating a type statement. Coming in 3 colors, orange, fire red and white, this bag is coated in the interesting however powerful calf buck leather. The total design might be easy and casual, but it indicates that you can tote this with any outfit in your closet at anytime of the day. Aside from, the big big has a slimming result. Unless you are really skim,oversized bag usually make your very own seem delicate in comparison. The polished steel hardware adds a hint of modernity.The Dior Babe louis vuitton replica handbags launched in spring 2008 is completely a stunner to any ensemble. The round shape and sheen hardware give a cute and fashionable touch. The matched clutch is quite lovable and versatile and it can be carried separately or left inside of the Babe bag as a wallet. It signifies you get a single bag, really get two.There are other concerns than design in selecting the ideal purse. The extremely very first decision is regardless of whether you want one particular particular versatile purse or you want a gucci replica different purse for just about every outfit. If you want one distinct purse for most occasions, you need to be far much more discriminating in your determination. Of course, it needs to be an uncomplicated, versatile design that will go with gucci replica handbags what ever you are sporting each day.If you're even now in a position to afford genuine designer handbags you're going to find a great deal of bargains.