Copy formula down in next blank row andn hard paste previous results

[colindickson]

I have the following code which does what I want it do apart from it copies and pastes row 8 each time instead of the one above where the formula has been copied down from:

    Sheets("Data").Select

    LastRow = Range("C" & Rows.Count).End(xlUp).Row


With Range("C8:L" & LastRow)
      .Copy .Offset(1)
      .Value = .Value
      .Offset(-1, 1)(1).Select
      

      End With

[p45cal]

try:

LastRow = Range("C" & Rows.Count).End(xlUp).Row
With Range("C" & LastRow & ":L" & LastRow)
  .Copy .Offset(1)
  .Value = .Value
  .Offset(-1, 1)(1).Select
End With

[colindickson]

try:

LastRow = Range("C" & Rows.Count).End(xlUp).Row
With Range("C" & LastRow & ":L" & LastRow)
  .Copy .Offset(1)
  .Value = .Value
  .Offset(-1, 1)(1).Select
End With

That works perfectly!!

Not sure if this is a separate post but can this be applied to a number of worksheets?

[p45cal]

If the code is not in a sheet code-module it can apply to the active sheet, whichever that happens to be. If it is in a sheet's code-module it will only apply to that sheet.

[colindickson]

Thanks - what I meant was can this be an array type application.... so that it does it to a number of worksheets rather than having to name each sheet at a time and effectively loop through them? or is that the solution some sort of loop?

[p45cal]

Sub blah()
For Each sht In Sheets(Array("Sheet1", "Sheet2", "Sheet3")) '<<adjust the sheets you want to process.
  With sht
    LastRow = .Range("C" & .Rows.Count).End(xlUp).Row
    With .Range("C" & LastRow & ":L" & LastRow)
      .Copy .Offset(1)
      .Value = .Value
      '.Offset(-1, 1)(1).Select 'can't select something on a sheet which isn't the active sheet.
    End With
  End With    'sht
Next sht
End Sub

[colindickson]

Genius, thank you!!!