VBA Copy Paste Formula after change the formula to value loop down with same source

[alphabo1201]

Hello Excel gurus.

I have a range of formula from A3 through Z3.
I want to copy that row and paste formula to A4 through Z4 as formula first and convert that to value after.

Then reselect A3 through Z3 and paste formula to A5 through Z5 as formula first and convert to value.
I would like this to continue copy paste formula then values until row count N column.

so the main source of formula will stay as formula and rest of the data will be values.

Could someone be kind enough to find the answer?

Thank you

 

[yky]

Try this:

Sub test_copy()

    Const N = 12   'need to define the value of N

    Dim rng1 As Range
    Dim rng2 As Range

    Set rng1 = ActiveSheet.Range("A3:Z3")
    Set rng2 = ActiveSheet.Range("A4:Z" & N)

   'copy and paste formulae
    rng1.Copy Destination:=rng2

   'copy and paste value
    rng2.Value = rng2.Value

End Sub

 

[alphabo1201]

Hello,

Thank you for replying, for some reason its not working but it give me a message if I would like to replace data. If I click yes, it only changes the first row to values.

Would it be possible to have the original formula cell to stay as formula but rest of the as values? and if possible it needs to keep the same format.

Thank you

 

[yky]

I tested it and found no problem. Your original formulae are in row 3, right? I don't see why the code would change anything in row 3. What the code does is 1. define two ranges, source (rng1) and destination (rng2), 2. copy source to destination (with formulae), 3. copy destination to itself (by value).

Did you change N to suit your need?
What is the exact message?
When you said "it only changes the first row to values", what row is it?

 

[alphabo1201]

I tested it and found no problem. Your original formulae are in row 3, right? I don't see why the code would change anything in row 3. What the code does is 1. define two ranges, source (rng1) and destination (rng2), 2. copy source to destination (with formulae), 3. copy destination to itself (by value).

Did you change N to suit your need?
What is the exact message?
When you said "it only changes the first row to values", what row is it?

Sorry for the late response, I created a sample image to explain better.

I have a set of data in range of A2 to G19. Then I have set of formula H2 to P2.

I need to copy H2 :P2 and paste as formula FIRST in the next row H3 to P3 AND THEN change it to value (reason for copying and pasting one row at a time is because I will have over 30,000 rows with a lot more data and if I copy paste formula to all the rows it would freeze the excel file).

My Marco commend that I would want in below sequence:
step 1: copy formula H2:P2
Step 2: paste as formula in H3:P3
step 3: Make H3: P3 as values
step 4: copy formula H2:P2
Step 5: paste as formula in H4:P4
step 6: Make H4: P4 as values
and this continues until and row above end of Row G


So essentially I want the macro to use the first set of formula and paste on to the bottom only leaving formula in the first row to keep the file smaller.

 

[yky]

Try this. It may take a while. You'll need to define N to the last row, 30,000 or something like that.

Option Explicit

Sub test_copy( )

    Const N = 12   'last row number. Need to define the value of N
    Dim counter As Long
    Dim rng1 As Range
    Dim rng2 As Range
    
    Application.ScreenUpdating = False

    Set rng1 = ActiveSheet.Range("H2:P2")
    
    For counter = 3 To N
    
        Set rng2 = ActiveSheet.Range("H" & counter & ":" & "P" & counter)

        'copy and paste formulae
        rng1.Copy Destination:=rng2

        'copy and paste value
        rng2.Value = rng2.Value
    
        Next counter
    
    Application.ScreenUpdating = True

End Sub