Results 1 to 4 of 4

Thread: Macro Help

  1. #1
    Email problem
    Join Date
    Jun 2012
    Location
    Durban
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro Help

    Using a macro how do I copy a range of cells, with formulas, from one range to the next range/column? Then copy the new range of cells to the next column when the macro is run again?

  2. #2
    Email problem IMHO's Avatar
    Join Date
    Jan 2012
    Location
    NW
    Posts
    540
    Thanks
    11
    Thanked 54 Times in 48 Posts
    You will have to provide a bit more info, like what it is what you are trying to do, for us to understand the question. As I understand, you want to copy A1:A10 to B1:B10 and next run of the macro it must copy B1:B10 to C1:C10. Or must it select the same type of range from where your cursor is when the macro is run and paste it at a position relative to where your cursor is?

    Do you know about the select, drag and release to copy and paste? You would select say A1:A10. Then look for the 'handle' at the bottom right of A10 and drag the whole selection to the right, as far as you want, and the formulas from column A will be copied to all the columns you dragged it to. You can drag horizontal or vertical. Play with it!
    ~Expenses will eat you alive! - My first Boss~

  3. #3
    Diamond Member
    Join Date
    Mar 2010
    Location
    Cape Town
    Posts
    6,329
    Thanks
    426
    Thanked 978 Times in 795 Posts
    I think that all you need to do is have a cell that retains the column number.

    i.e. set up cel a1 to contain the name of the colum that you want to move. and a2 the number of cells in the range

    the macro reads the starting column number from cell a1, do the copy for the number of times found in a2 and once it has done its thing rewrites a1 with the new column name.

    When you run it again it will find the new starting point in cel a1 and it will still run for the correct number of iterations.

  4. #4
    Diamond Member
    Join Date
    Mar 2010
    Location
    Cape Town
    Posts
    6,329
    Thanks
    426
    Thanked 978 Times in 795 Posts
    Each time the macro is run the column specified in Next Col will be copied from the colum to the left of it, starting from position Next Row for the number of rows in Count. When the macro is done it updates the Next Col field so that the entire operation shifts one position to the right.

    There is one thing that you must keep in mind. When you copy a formula this way the formula retains is fixed cell coordinates i.e. when you copy a formula to the right manually then Excel offsets to the right automatically. Using the Macro, each copy that you make of the formula will continue to refer to the original formula positions, exactly as they are.

    so, if the cell containing the 33 has a formula that say is A22+B22 and you copy the formula using this method, the new formula will still be A22+B22 and not B22+C22

    Anyhow, this is my quick and dirty version of what I think you want to do.


    Code:
          1           2        3       4       5      6      7
    1  Next Col   Next Row   Count
    2        6           3       4
    3                                         33
    4                                         44
    5                                         55
    6                                         66
    Code:
    Sub ah_copy()
    Dim CurX, NextX, NextY, NumY, nEndY
    Dim i As Integer
    
    NextX = Worksheets("Sheet1").Cells(2, 1).Value  ' the X coordinate of the cell we are going to write to
    NextY = Worksheets("Sheet1").Cells(2, 2).Value  ' the Y coordinate of the first cell we are going to write to
    NumY = Worksheets("Sheet1").Cells(2, 3).Value  ' the number of vertical cells we will write
    
    
    CurX = NextX - 1                ' the X coordinate of the cel where the data is now
    nEndY = NextY + NumY - 1   ' the Y coordinate of the last vertical cell we will write to
    
    For i = NextY To nEndY ' do this loop for all the cells in the vertical range starting with the specified cel
        Worksheets("Sheet1").Cells(i, NextX).Formula = Worksheets("Sheet1").Cells(i, CurX).Formula ' copy the formula from left to right
    Next
    
        Worksheets("Sheet1").Cells(2, 1).Value = NextX + 1 ' update our counter so that we remember which row to do next
    End Sub

  5. Thanks given for this post:

    Dave A (14-Jun-12)

Did you like this article? Share it with your favourite social network.

Did you like this article? Share it with your favourite social network.

Posting Permissions

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