Announcement

Collapse
No announcement yet.

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

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    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!

    Comment


    • #3
      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

      Comment


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

        Give this a try:

        Code:
        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!

        Comment


        • #5
          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

          Comment


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

            Comment


            • #7
              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?

              Comment


              • #8
                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 :/

                Comment


                • #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:

                  Code:
                  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!

                  Comment


                  • #10
                    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 :-)

                    Comment


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

                      Thank you for the feedback!
                      Have a Great Day!

                      Comment


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

                        Hi, sorry I just realsied the input box may not be necessary. Instead of having the input box to specify the number of rows, can it just look at when the data in column A ends and copy the formulas down to that point without having to tell it the actual row number each time? Or would the entire macro have to change to enable this?

                        Comment


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

                          A simple one-line change:

                          Code:
                          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 = Cells(Rows.Count, "A").End(xlUp).Row
                              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!

                          Comment


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

                            Excellent. Thank you. Just one more question. If I ever wanted to add a column to the macro would it be a case of just adding in the reference with the others and changing the 0 To 19 part to 0 To 20? So for example if I added column BP to the macro it would look like this?...

                            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,BP,BR,BU,BX,CA,CB,CD", ",")
                            Dim bry(0 To 20) As String
                            L = LBound(ary)
                            U = UBound(ary)
                            N = Cells(Rows.Count, "A").End(xlUp).Row
                            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

                            Comment


                            • #15


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

                              Yes..............Why wait?

                              Make a copy of your workbook and experiment to insure it works!
                              Have a Great Day!

                              Comment

                              Working...
                              X