Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Copy Entire Row to different sheet based on cell value
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2015
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Copy Entire Row to different sheet based on cell value

    Hello all, I have a project at work I am trying to do where I need to copy an entire row to a different sheet based on a cell value.

    I have been searching for answers and have tried various others' solutions but I can't seem to get this to work. I am quite new to VBA although I've been around Excel for awhile.

    In column C of worksheet "Report-From-QB", there is an item list. I need to copy that item's row to a worksheet called "Data". There are quite a number of items in column C, and I need to sort it based on name, in this case "250000 (MT Solar Custom Order)". This action needs to be done multiple times for various items, then strip out all blank and/or impertinent cell to create a chart, but I guess that's a different topic.

    I'm using Excel 2013.

    Any help would be greatly appreciated.

  2. #2
    Board Regular jim may's Avatar
    Join Date
    Jul 2004
    Location
    Roanoke, VA
    Posts
    7,449
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy Entire Row to different sheet based on cell value

    Why not try RECORDING A MACRO. Apply an auto-filter against your data range, then from column c select the data you wish to copy. Once only those records are showing select the row headers of all filtered records. Copy and then Paste to you destination sheet, DATA. STOP MACRO and post the code produced.

  3. #3
    New Member
    Join Date
    May 2015
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy Entire Row to different sheet based on cell value

    Ok I recorded a macro that did what I needed it to do. Thanks, Jim.

    Here's the code produced:

    Code:
    Sub MT_Solar_Custom_Order()
    '
    ' MT_Solar_Custom_Order Macro
    '
    
    
    '
        ActiveSheet.Range("$C$1:$C$187").AutoFilter Field:=1, Criteria1:= _
            "=250000 (MT Solar Custom Order)", Operator:=xlAnd
        Rows("44:44").Select
        Selection.Copy
        Sheets("Data").Select
        Rows("3:3").Select
        ActiveSheet.Paste
    End Sub

    The problem is that the range for the row is absolute, meaning the macro is recording the row number (44:44) and not whatever row in which the data is located. How can the macro be changed to support this? The data is being produced externally by another program and could possibly change from time to time, but the data for column C should remain constant. Any help on this?

    Thanks, Kelsey

  4. #4
    New Member
    Join Date
    May 2015
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy Entire Row to different sheet based on cell value

    I found some code online which I adapted for my use.

    The one problem I have with this though is that the macro only works on the sheet "Report-From-QB".

    How do I get the code to perform properly regardless of the current active sheet?

    I would also like to incorporate a button on a different sheet which runs the macro behind the scenes.

    Here is my code:

    Code:
    Sub Update_Chart()Dim Check As Range, r As Long, lastrow2 As Long, lastrow As Long
    Application.ScreenUpdating = False
    lastrow = Worksheets("Report-From-QB").UsedRange.Rows.Count
    lastrow2 = Worksheets("Data").UsedRange.Rows.Count
    If lastrow2 = 1 Then lastrow2 = 0
        For r = lastrow To 2 Step -1
            If Range("C" & r).Value = "250000 (MT Solar Custom Order)" Then
                Rows(r).Copy Destination:=Worksheets("Data").Range("A3")
                lastrow2 = lastrow2 + 1
                Else:
            End If
                
            If Range("C" & r).Value = "250004 (MT Solar Top Of Pole Mount, 4 Module)" Then
                Rows(r).Copy Destination:=Worksheets("Data").Range("A4")
                lastrow2 = lastrow2 + 1
                Else:
            End If
            
              If Range("C" & r).Value = "250006 (MT Solar Top Of Pole Mount, 6 Module)" Then
                Rows(r).Copy Destination:=Worksheets("Data").Range("A5")
                lastrow2 = lastrow2 + 1
                Else:
            End If
            
                If Range("C" & r).Value = "250008 (MT Solar Top Of Pole Mount, 8 Module)" Then
                Rows(r).Copy Destination:=Worksheets("Data").Range("A6")
                lastrow2 = lastrow2 + 1
                Else:
            End If
            
                If Range("C" & r).Value = "250010HD (MT Solar 8-TOP-10, 10 Modules, 72 CELL)" Then
                Rows(r).Copy Destination:=Worksheets("Data").Range("A7")
                lastrow2 = lastrow2 + 1
                Else:
            End If
            
                If Range("C" & r).Value = "250012 (MT Solar Top Of Pole Mount, 12 Module)" Then
                Rows(r).Copy Destination:=Worksheets("Data").Range("A8")
                lastrow2 = lastrow2 + 1
                Else:
            End If
            
            If Range("C" & r).Value = "250015 (MT Solar Top Of Pole Mount, 15 Module)" Then
                Rows(r).Copy Destination:=Worksheets("Data").Range("A9")
                lastrow2 = lastrow2 + 1
                Else:
            End If
            
            If Range("C" & r).Value = "250120 (MT Solar Splice Kit, 90"")" Then
                Rows(r).Copy Destination:=Worksheets("Data").Range("A10")
                lastrow2 = lastrow2 + 1
                Else:
            End If
            
            If Range("C" & r).Value = "250120HD (MT Solar HD Splice Kit, 90"" (Two I Beams))" Then
                Rows(r).Copy Destination:=Worksheets("Data").Range("A11")
                lastrow2 = lastrow2 + 1
                Else:
            End If
            
            If Range("C" & r).Value = "250122 (MT Solar Splice Kit, 45"" (Two I-Beams))" Then
                Rows(r).Copy Destination:=Worksheets("Data").Range("A12")
                lastrow2 = lastrow2 + 1
                Else:
            End If
            
            If Range("C" & r).Value = "250129 (MT Solar Wing Kit, 45"" (Four I-Beams))" Then
                Rows(r).Copy Destination:=Worksheets("Data").Range("A13")
                lastrow2 = lastrow2 + 1
                Else:
            End If
            
            If Range("C" & r).Value = "250129HD (MT Solar HD Wing Kit, 45"" (Four I-Beams))" Then
                Rows(r).Copy Destination:=Worksheets("Data").Range("A14")
                lastrow2 = lastrow2 + 1
                Else:
            End If
            
        Next r
    Application.ScreenUpdating = False
    End Sub

  5. #5
    Banned user
    Join Date
    May 2014
    Location
    Germany
    Posts
    1,336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy Entire Row to different sheet based on cell value

    Quote Originally Posted by kelseyd91 View Post
    How do I get the code to perform properly regardless of the current active sheet?

    .......

    Usualy replacing
    Worksheets("Report-From-QB")
    with
    ActiveSheet
    would surffice. Make sure the Active sheet is the one that you are looking at, and have "clicked in" somewhere at least once..

    As fot the Button.. it is probably quicker for you to google that one.
    . There are loads of links explaining that better than i could

    Foe example
    https://support.office.com/en-in/art...d-9c72c843a283
    Last edited by DocAElstein; May 27th, 2015 at 12:08 PM.

  6. #6
    New Member
    Join Date
    May 2015
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy Entire Row to different sheet based on cell value

    Quote Originally Posted by DocAElstein View Post
    Usualy replacing
    Worksheets("Report-From-QB")
    with
    ActiveSheet
    would surffice. Make sure the Active sheet is the one that you are looking at, and have "clicked in" somewhere at least once..
    Maybe I should clarify. I would like to be able to run the macro via keystroke or button regardless of which sheet is currently active. Wouldn't ActiveSheet do the opposite?

  7. #7
    Banned user
    Join Date
    May 2014
    Location
    Germany
    Posts
    1,336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy Entire Row to different sheet based on cell value

    Quote Originally Posted by kelseyd91 View Post
    Maybe I should clarify. I would like to be able to run the macro via keystroke or button regardless of which sheet is currently active. Wouldn't ActiveSheet do the opposite?
    Hi,
    . I do not quite follow.
    . ActiveSheet refers to the one you are "looking at" at the time
    . If you wish to refer to specific Worksheets, regardless of where you "are", then something along the lines of your original code Post # 4 should be OK..
    I assume all sheets are in the same File, and it is open ?

  8. #8
    New Member
    Join Date
    May 2015
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy Entire Row to different sheet based on cell value

    Quote Originally Posted by DocAElstein View Post
    Hi,
    . I do not quite follow.
    . ActiveSheet refers to the one you are "looking at" at the time
    . If you wish to refer to specific Worksheets, regardless of where you "are", then something along the lines of your original code Post # 4 should be OK..
    I assume all sheets are in the same File, and it is open ?
    All the sheets are in the same file and are open. It does seem like my original code should work but when I am on a different sheet and type the keystroke, the macro doesn't function. Maybe it has something to do with lastrow?

  9. #9
    Banned user
    Join Date
    May 2014
    Location
    Germany
    Posts
    1,336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy Entire Row to different sheet based on cell value

    Quote Originally Posted by kelseyd91 View Post
    All the sheets are in the same file and are open. It does seem like my original code should work but when I am on a different sheet and type the keystroke, the macro doesn't function. Maybe it has something to do with lastrow?

    OK: Sorry , I think I am with You now….
    . you just need to go through your program and make sure you are ALWAYS referencing the correct sheet: This would then be the start


    Code:
    Sub Update_Chart()
    Dim Check As Range, r As Long, lastrow2 As Long, lastrow As Long
    Dim ws1 As Worksheet: Dim ws2 As Worksheet
    Set ws1 = Worksheets("Report-From-QB")
    Set ws2 = Worksheets("Data")
    'Application.ScreenUpdating = False
    lastrow = ws1.UsedRange.Rows.Count
    lastrow2 = ws2.UsedRange.Rows.Count
    If lastrow2 = 1 Then lastrow2 = 0
        For r = lastrow To 2 Step -1
            If ws1.Range("C" & r).Value = "250000 (MT Solar Custom Order)" Then
                ws1.Rows(r).Copy Destination:=ws2.Range("A3")
                lastrow2 = lastrow2 + 1

    This demonstrate how important it always ist o be explicit with your referencing of ranges, I should have caught that straight away..
    Otherwise things like Range will always refer to the active sheet, so you will get different results depending on where “you are!”

    Alan

  10. #10
    New Member
    Join Date
    May 2015
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy Entire Row to different sheet based on cell value

    Thanks so much Alan! That code worked perfectly. I did not realize that everything needed to be specified that way. Thanks again.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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