Macro to specifically copy and paste across all sheets

MZING81

New member
Joined
Mar 27, 2012
Messages
40
Reaction score
0
Points
0
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.

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
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,519
Reaction score
4
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Can I just clarify a few things here?

Are you copying D9 from the initial worksheet and pasting it on all worksheets, or are you copying D9 from each worksheet and pasting it on the same worksheet?

You're pasting in column S, row 1 to the last row, but only if that row has data in it. Is that correct?

One of the challenges that I'm seeing here is that you're mixing Range and .Range, and those are very different. But before I try and give you an answer, I'd like to have a very clear picture of exactly what you're doing.
 

MZING81

New member
Joined
Mar 27, 2012
Messages
40
Reaction score
0
Points
0
So the idea is to have cell D9 on an individual sheet copy to S column of the same sheet, and that needs to work across all sheets to fit their individual different number of rows that actually contain data. The formula as it is now only works for the first sheet!!


Can I just clarify a few things here?

Are you copying D9 from the initial worksheet and pasting it on all worksheets, or are you copying D9 from each worksheet and pasting it on the same worksheet?

You're pasting in column S, row 1 to the last row, but only if that row has data in it. Is that correct?

One of the challenges that I'm seeing here is that you're mixing Range and .Range, and those are very different. But before I try and give you an answer, I'd like to have a very clear picture of exactly what you're doing.
 
Top