Results 1 to 3 of 3

Thread: Macro to specifically copy and paste across all sheets

  1. #1

    Macro to specifically copy and paste across all sheets



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,271
    Articles
    57
    Blog Entries
    14
    Excel Version
    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.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    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!!


    Quote Originally Posted by Ken Puls View Post
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •