Hello everyone
I have a Macro that almost works perfectly as I need it. What it's supposed to do is copy the cell in d9 and paste it in s10 all the way down the whole column only in rows that have data. It works in the first sheet, but not the other 150+ with varying number rows. It copies and pastes the same number of rows in all the sheets. here's the macro below.
I thought that by having it go to the last cell that it paste to the last row in every sheet, but just ended up mirroring the first sheet.
Any help would be greatly appreciated.
Thank you
I have a Macro that almost works perfectly as I need it. What it's supposed to do is copy the cell in d9 and paste it in s10 all the way down the whole column only in rows that have data. It works in the first sheet, but not the other 150+ with varying number rows. It copies and pastes the same number of rows in all the sheets. here's the macro below.
Code:
Sub NEW_CPSTE()
Dim WS As Worksheet
Dim R As Long
On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each WS In Worksheets
With WS.UsedRange
Range("D9").Select
Selection.Copy
For R = .Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountA(.Rows(R).EntireRow) = 1 Then
.Rows(R).Range("S9").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveSheet.Paste
End If
Next R
End With
Next WS
EndMacro:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationManual
End Sub
I thought that by having it go to the last cell that it paste to the last row in every sheet, but just ended up mirroring the first sheet.
Any help would be greatly appreciated.
Thank you