+ Reply to Thread
Results 1 to 14 of 14

Macro to compare data between sheets and copy/paste

Hybrid View

  1. #1
    Valued Forum Contributor
    Join Date
    03-03-2009
    Location
    UK
    MS-Off Ver
    MS365 Subscription Excel for Mac
    Posts
    1,017

    Macro to compare data between sheets and copy/paste

    Sub ABCTESTABC()
    
     Const sh1 As String = "test"
     Const sh2 As String = "PIF"
      
     Dim rng As Range, c As Range, FoundCell As Range
    
        With Worksheets(sh2).Range("a1")
            With .CurrentRegion
                .AutoFilter Field:=8, Criteria1:="U"
                Set rng = .SpecialCells(xlCellTypeVisible)
            End With
            .AutoFilter
        End With
        
        With Worksheets(sh1)
            For Each c In rng.Rows
                If Not rng(c.Row, "f") = vbNullString Then
                    Set FoundCell = .Range("f:f").Find(What:=rng(c.Row, "f"))
                    If Not FoundCell Is Nothing Then
                        c.EntireRow.Copy .Cells(FoundCell.Row, "a")
                    End If
                End If
            Next
        End With
        Set rng = Nothing
        Set FoundCell = Nothing
        Set c = Nothing
        
    
        
    End Sub

    How would I alter the above so when it pastes into sheet test it doesn't paste the whole row, instead it just pastes rows A to O ???

  2. #2
    Valued Forum Contributor
    Join Date
    03-03-2009
    Location
    UK
    MS-Off Ver
    MS365 Subscription Excel for Mac
    Posts
    1,017

    Re: Macro to compare data between sheets and copy/paste

    I know it's the below line which currently copies the entire row but I just want the range Ax:Ox to be pasted.

    If Not FoundCell Is Nothing Then
                        c.EntireRow.Copy .Cells(FoundCell.Row, "a")

  3. #3
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,167

    Re: Macro to compare data between sheets and copy/paste

    Untested but you can try -
    .range("A" & c.row & ":O" & c.row).copy .cells(foundcell.row,"a")
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  4. #4
    Valued Forum Contributor
    Join Date
    03-03-2009
    Location
    UK
    MS-Off Ver
    MS365 Subscription Excel for Mac
    Posts
    1,017

    Re: Macro to compare data between sheets and copy/paste

    Doesn't work at all arlu I'm afraid.

  5. #5
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,167

    Re: Macro to compare data between sheets and copy/paste

    Will this option work? Copy as is and delete the unwanted columns?

  6. #6
    Valued Forum Contributor
    Join Date
    03-03-2009
    Location
    UK
    MS-Off Ver
    MS365 Subscription Excel for Mac
    Posts
    1,017

    Re: Macro to compare data between sheets and copy/paste

    I don't want any columns deleted. What I want is, when the row is copied from sheet PIF, I want it to be pasted into sheet test, but only paste in the row from Ax:Ox.

  7. #7
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,167

    Re: Macro to compare data between sheets and copy/paste

    I dont like the find code that much. But maybe if you explain to me what you are trying to achieve, i can create another code for you.

  8. #8
    Valued Forum Contributor
    Join Date
    03-03-2009
    Location
    UK
    MS-Off Ver
    MS365 Subscription Excel for Mac
    Posts
    1,017

    Re: Macro to compare data between sheets and copy/paste

    My original post works perfectly for what I've described. The only issue is that I don't want the whole row copied and pasted, just range A to O in the row.

  9. #9
    Valued Forum Contributor
    Join Date
    03-03-2009
    Location
    UK
    MS-Off Ver
    MS365 Subscription Excel for Mac
    Posts
    1,017

    Re: Macro to compare data between sheets and copy/paste

    I want to look in column H on sheet PIF and if it finds 'U' then copy that row range A to O and then look at whats in column F on the row where it found U on the same sheet and then goes and find whats in column F on sheet test searching only column F and if it finds a match then paste on that row.
    Last edited by ScabbyDog; 11-23-2012 at 02:43 PM. Reason: Clarifying what I want exactly

  10. #10
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Macro to compare data between sheets and copy/paste

    Maybe this one.

    c.Range("A:O" & LastRow).Copy .Cells(FoundCell.Row, "a")
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  11. #11
    Valued Forum Contributor
    Join Date
    03-03-2009
    Location
    UK
    MS-Off Ver
    MS365 Subscription Excel for Mac
    Posts
    1,017

    Re: Macro to compare data between sheets and copy/paste

    Doesn't seem to work. Any other ideas?

  12. #12
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Macro to compare data between sheets and copy/paste

    Post your file so we can test it.

  13. #13
    Valued Forum Contributor
    Join Date
    03-03-2009
    Location
    UK
    MS-Off Ver
    MS365 Subscription Excel for Mac
    Posts
    1,017

    Re: Macro to compare data between sheets and copy/paste

    Basic sheet attached so you can test it.

    Book1.xls

  14. #14
    Valued Forum Contributor
    Join Date
    03-03-2009
    Location
    UK
    MS-Off Ver
    MS365 Subscription Excel for Mac
    Posts
    1,017

    Re: Macro to compare data between sheets and copy/paste

    Book1.xlsm

    OK, I've tested it and what you posted ooeldere works, but, it always give me an object defined error after it runs and highlights the piece of code you gave me even though it does what I now want it to do! Can you check the below sample file with code included?

    Thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1