Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

Thread: Macro to copy formulas down columns for a specified number of rows

1. Member
Join Date
15th May 2013
Posts
67

Macro to copy formulas down columns for a specified number of rows

Hello,

I would like to create a macro that will copy formulas from the top of each column down to a row number that I specify (as the number of rows are always changing).

I currently have to manually select the top of each column with a formula in (not all adjacent to one another) and copy & paste it down to the end of the worksheet. There are around 21 columns that I have to do this for whenever new data is pasted into the spreadsheet and overrides the formulas.

I would like to just click a button named 'copy formulas' and any column with a formula will be copy & pasted right down to the end of the data.

Is this possible? Any help will be greatly appreciated.

Thanks,

Kelly

Excel Video Tutorials / Excel Dashboards Reports

2. Re: Macro to copy formulas down columns for a specified number of rows

Which columns will have formulas in row #1?

3. Member
Join Date
15th May 2013
Posts
67

Re: Macro to copy formulas down columns for a specified number of rows

Hi, Columns AB, AD, AJ, AL, AO, AR, AU, AV, AZ, BC, BF, BI, BL, BO, BR, BU, BX, CA, CB, CD

Excel Video Tutorials / Excel Dashboards Reports

4. Re: Macro to copy formulas down columns for a specified number of rows

Give this a try:

VB:
```Sub marine()
Dim I As Long, L As Long, U As Long, N As Long
Dim bry(0 To 19) As String
L = LBound(ary)
U = UBound(ary)
N = Application.InputBox(prompt:="Enter end row", Type:=1)
For I = L To U
bry(I) = ":" & ary(I) & N
ary(I) = ary(I) & "1"
Range(ary(I)).Copy Range(ary(I) & bry(I))
Next I
End Sub

```

5. Member
Join Date
15th May 2013
Posts
67

Re: Macro to copy formulas down columns for a specified number of rows

It comes up with:

Run Time Error '1004'

Copy Method of Range class failed

Excel Video Tutorials / Excel Dashboards Reports

6. Re: Macro to copy formulas down columns for a specified number of rows

What value did you enter in the MsgBox?
Are any of the cells either merged or protected??

7. Member
Join Date
15th May 2013
Posts
67

Re: Macro to copy formulas down columns for a specified number of rows

I entered 77899 in the MsgBox. No cells are protected. The data starts on row 12 and no cells are merged from row 12 down. Some headings a few rows above the data are merged though?

Excel Video Tutorials / Excel Dashboards Reports

8. Member
Join Date
15th May 2013
Posts
67

Re: Macro to copy formulas down columns for a specified number of rows

Sorry, I probably should have said the formulas start at row 12 :/

Excel Video Tutorials / Excel Dashboards Reports

9. Re: Macro to copy formulas down columns for a specified number of rows

But you asked for the macro to copy from the top of each column, that would be row #1. Here is a modification:

VB:
```Sub marine()
Dim I As Long, L As Long, U As Long, N As Long
Dim bry(0 To 19) As String
L = LBound(ary)
U = UBound(ary)
N = Application.InputBox(prompt:="Enter end row", Type:=1)
For I = L To U
bry(I) = ":" & ary(I) & N
ary(I) = ary(I) & "12"
Range(ary(I)).Copy Range(ary(I) & bry(I))
Next I
End Sub

```

10. Member
Join Date
15th May 2013
Posts
67

Re: Macro to copy formulas down columns for a specified number of rows

Thank you so much!! It works perfectly. Sorry I was not very clear with the row number, not particularly helpful. I just wish I understoood the macro so I could do it myself. Perhaps with time I could learn.

Thank you for helping my work process become more efficient, this has made my day :-)

Excel Video Tutorials / Excel Dashboards Reports

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno