Copy/Paste Section Values with Offset

[ipperz]

Hello,

I am looking for recommendations to speed up a macro. The below macro copies a lot of formulas located in A2:R43, pastes the selection every 47 rows, then pastes the new values of the formulas over the previous paste.

'Copy/Pastes A2:R43 the number of times equaling A1's value

    For i = 1 To Worksheets("Calc Sheet").Range("A1").Value
      Range("A2:R43").Copy Destination:=Range("A2").Offset(47 * i)
    
    
'Copy/Paste below formulas into values over itself

    Range("A2:R43").Offset(47 * i).Copy
    Range("A2").Offset(47 * i).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Selection.Cut

    Range("A2").Offset(47 * i).Select
    ActiveSheet.Paste
    Next i

Thank you!

 

[Fluff]

How about

    For I = 1 To Worksheets("Calc Sheet").Range("A1").Value
      Range("A2:R43").Copy Destination:=Range("A2").Offset(47 * I)
      With Range("A2").Offset(47 * I)
         .Value = .Value
      End With
    Next I

 

[JoeMo]

Does this do what you want?

Sub ipperz()
Application.ScreenUpdating = False
For i = 1 To Worksheets("Calc Sheet").Range("A1").Value
    Range("A2:R43").Copy
    With Range("A2:R43").Offset(47 * i)
      .PasteSpecial Paste:=xlPasteAll
      .Calculate
      .Value = .Value
    End With
Next i
With Application
    .CutCopyMode = False
    .ScreenUpdating = True
End With
End Sub

 

[ipperz]

Thanks Fluff and JoeMo!

I may have been unclear in original post, my apologies. The pastes formulas below row 47 should be set as values only, and your suggestions seem to be keeping the formula format of the paste.

 

[Fluff]

Whilst my code was wrong it should have been

   For I = 1 To Worksheets("Calc Sheet").Range("A1").Value
      Range("A2:R43").Copy Destination:=Range("A2").Offset(47 * I)
      With Range("A2:R43").Offset(47 * I)
         .Value = .Value
      End With
    Next I

JoeMo's code converts all the formulae to values.

 

[ipperz]

Perfect, thank you!

 

[Fluff]

Glad we could help