Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 16

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

  1. #1
    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. #2
    Join Date
    20th December 2012
    Location
    Lakehurst, NJ, USA
    Posts
    223

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

    Which columns will have formulas in row #1?
    Have a Great Day!

  3. #3
    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. #4
    Join Date
    20th December 2012
    Location
    Lakehurst, NJ, USA
    Posts
    223

    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 
        ary = Split("AB,AD,AJ,AL,AO,AR,AU,AV,AZ,BC,BF,BI,BL,BO,BR,BU,BX,CA,CB,CD", ",") 
        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 
    
    
    Have a Great Day!

  5. #5
    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. #6
    Join Date
    20th December 2012
    Location
    Lakehurst, NJ, USA
    Posts
    223

    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??
    Have a Great Day!

  7. #7
    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. #8
    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. #9
    Join Date
    20th December 2012
    Location
    Lakehurst, NJ, USA
    Posts
    223

    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 
        ary = Split("AB,AD,AJ,AL,AO,AR,AU,AV,AZ,BC,BF,BI,BL,BO,BR,BU,BX,CA,CB,CD", ",") 
        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 
    
    
    Have a Great Day!

  10. #10
    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


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 1
    Last Post: January 25th, 2013, 03:45
  2. Replies: 1
    Last Post: January 23rd, 2012, 02:33
  3. Replies: 1
    Last Post: November 2nd, 2011, 21:48
  4. Replies: 9
    Last Post: November 1st, 2011, 02:22
  5. Replies: 2
    Last Post: May 28th, 2009, 22:31

Bookmarks

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