Excel Copy Across Macro

When you work with dynamic arrays and spill ranges you get used to the formula automatically spilling across to populate a range. When you aren’t using dynamic arrays, it can be frustrating to have to copy formulas across. Here’s a simple macro solution.

In the image below I need to copy cell C3 across to the range D3:N3.

This is a standard budget layout in Australia where the financial year starts in July.

A one-line macro command can copy this formula across.

Sub CopyAcrossMonths()
Selection.Copy Selection.Resize(, 12)
End Sub

This macro assumes you have cell C3 selected before running the macro.

Make sure the format of the selected cell has the format you want to apply to the range as the format will also be copied across.

Changing the 12 in the macro code will change how many columns are copied to.

Ranges

This macro will also copy a standard range across.

The macro won’t work with multi-range selected using the Ctrl key. For a multi-range selection use the code below. This will work with a standard range as well.

Sub CopyAcrossMonthsMulti()
Dim c
For Each c In Selection
    c.Copy c.Resize(, 12)
Next c
End Sub

If you are unsure about using macros check out this blog post below.