Simple looping macro help

[ma0ffst08]

Hello everyone,

I'm sure this will be really easy for someone, but I've been stuck for ages, so any help would be appreciated please.

I have a table with over 2,000 rows of data.

I would like a macro that starts by copying data from C4:V4 and pastes it as values into C1:V1.
Then it would copy the results from W1:Z1 and paste it as values into W4:Z4.

Then it would move down and copy data from C5:V5 and paste it as values into C1:V1.
Then it would copy the results from W1:Z1 and paste it as values into W5:Z5.

And so on, so the last row (for example row 2000), it would copy data from C2000:V2000 and paste it as values into C1:V1.
Then it would copy the results from W1:Z1 and paste it as values into W2000:Z2000.

But the number of rows might change, so it needs to be able to loop for as many rows as there is data. Column C would always hold data (until the whole row is blank).

Thank you so much!

 

[Snakehips]

Give this a try.

Sub DoStuff()
Dim lr As Long
Dim r As Long
Dim Crng As Range
Dim Wrng As Range

Set Crng = Range("C1:V1")
Set Wrng = Range("W1:Z1")

lr = Cells(Rows.Count, "C").End(xlUp).Row
Application.ScreenUpdating = False

    For r = 4 To lr
        Crng.Value = Range("C" & r & ":V" & r).Value
        Range("W" & r & ":Z" & r).Value = Wrng.Value
    Next r
    
Application.ScreenUpdating = True
End Sub

 

[6StringJazzer]

It sounds like you are doing a calculation on the sheet, then saving the values on another row. It might make more sense to just do the calculations in VBA, which would be faster than all of the copy and paste. What are the formulas in W1:Z1?

 

[ma0ffst08]

Give this a try.

Sub DoStuff()
Dim lr As Long
Dim r As Long
Dim Crng As Range
Dim Wrng As Range

Set Crng = Range("C1:V1")
Set Wrng = Range("W1:Z1")

lr = Cells(Rows.Count, "C").End(xlUp).Row
Application.ScreenUpdating = False

    For r = 4 To lr
        Crng.Value = Range("C" & r & ":V" & r).Value
        Range("W" & r & ":Z" & r).Value = Wrng.Value
    Next r
   
Application.ScreenUpdating = True
End Sub

Thank you so much

 

[ma0ffst08]

It sounds like you are doing a calculation on the sheet, then saving the values on another row. It might make more sense to just do the calculations in VBA, which would be faster than all of the copy and paste. What are the formulas in W1:Z1?

Hi, C1:V1 link to other sheets with various other calculations, so it's just the output from many calcs which come back to W1:Z1. It would be too difficult to do formulas in W1:Z1 in this instance