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!!!