Excel "copy columns" macro

An Excel Macro to partially copy named columns between sheets


As part of my bookkeeping process I download CSVs and copy selected columns to a more simplified sheet, before moving the individual values into categorised columns to help with budging and accounting requirements:

Raw CSV data
Copy columns to breakdown
Categorise and sum values
Raw CSV data

The most time-consuming and error-prone part of this process is selecting and copying the CSV data:

To do this I have to:

  • identify source and target columns
  • identify previous and current dates
  • review and reconcile incomplete date ranges
  • insert correct number of target rows (so any target SUM()s are pushed down)
  • select and copy partial source column data, in target column order (possibly via a temporary sheet)
  • review / redo if I made any mistakes
  • repeat for each account
  • repeat for each accounting period

I’ve been wanting to automate this for years, so this weekend I dusted off my ancient VBA skills and finally got to it.


Below is the macro in action, on an example expenses sheet:

excel copy columns macro

Running the macro will:

  • read header cells from the target sheet
  • read the bottom line Date in the target sheet
  • grab the corresponding partial source columns, from the next full day
  • confirm the number of entries and sheets
  • copy the values to the target sheet

A few nice UX touches; the macro:

  • uses target sheet headings to determine required data
  • will start from the active source row, if no Date column or matched date
  • inserts full rows before pasting, so any column SUMs are shifted down
  • pastes values only, so target formatting is respected
  • shows a selection preview during confirmation
  • selects the final pasted data

Note that if there is a mismatch between the number of starting date entries between the source and target sheets, the macro will warn – and if continuing – will create duplicates in the target sheet which can be manually edited.



The example macro-enabled workbook above can be found here:

To download and use locally, choose:

  • File > Save As > Download a Copy

Running the macro

To run the macro in the downloaded workbook, choose:

  • Developer > Macros > Copy_Columns > Run

Note that there is no undo for Excel macros, so be sure to save the sheet before running.

If you can’t see the Developer ribbon entry, you may need to enable it in preferences:

Excel preferences to enable the Developer ribbon item

To make the macro globally available, you will need to copy it into a module in the global “personal” workbook:

The VBA code

A copy of the code (should you not want to install) is available here:

It’s very-well commented, so take a look if you want to know what’s going on under the hood.

Excel VBA is a little tricky, but I’ve hopefully iterated on it enough times to identify the footguns, and there’s hopefully a certain level of resilience that it should now work in most scenarios. Feel free to leave a comment if you spot any bugs, or need help to understand the code.

FWIW I used Chat GPT (opens new window) to help me work out a lot of it!


The end result should mean when I need to do my accounts, I can just:

  • paste in the new CSV rows
  • run the macro to copy values
  • just get on with categorising

I’m publishing online for both reference and backup; hopefully it will be useful to others too.


I hope you found this article useful or enjoyable.

If you want to engage further, follow me on Twitter or drop a comment or reaction below.

Either way, thanks for reading!