Copy Entire Row to different sheet based on cell value

kelseyd91

New Member
Joined
May 15, 2015
Messages
6
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.
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,471
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.
 

kelseyd91

New Member
Joined
May 15, 2015
Messages
6
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
 

kelseyd91

New Member
Joined
May 15, 2015
Messages
6
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
 

DocAElstein

Banned user
Joined
May 24, 2014
Messages
1,336
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/ar...orksheet-d58edd7d-cb04-4964-bead-9c72c843a283
 
Last edited:

kelseyd91

New Member
Joined
May 15, 2015
Messages
6
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?
 

DocAElstein

Banned user
Joined
May 24, 2014
Messages
1,336
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 ?
 

kelseyd91

New Member
Joined
May 15, 2015
Messages
6
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?
 

DocAElstein

Banned user
Joined
May 24, 2014
Messages
1,336
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:
[color=blue]Sub[/color] Update_Chart()
[color=blue]Dim[/color] Check [color=blue]As[/color] Range, r [color=blue]As[/color] [color=blue]Long[/color], lastrow2 [color=blue]As[/color] [color=blue]Long[/color], lastrow [color=blue]As[/color] [color=blue]Long[/color]
[color=blue]Dim[/color] ws1 [color=blue]As[/color] Worksheet: [color=blue]Dim[/color] ws2 [color=blue]As[/color] Worksheet
[color=blue]Set[/color] ws1 = Worksheets("Report-From-QB")
[color=blue]Set[/color] ws2 = Worksheets("Data")
[color=darkgreen]'Application.ScreenUpdating = False[/color]
lastrow = ws1.UsedRange.Rows.Count
lastrow2 = ws2.UsedRange.Rows.Count
[color=blue]If[/color] lastrow2 = 1 [color=blue]Then[/color] lastrow2 = 0
    [color=blue]For[/color] r = lastrow [color=blue]To[/color] 2 [color=blue]Step[/color] -1
        [color=blue]If[/color] ws1.Range("C" & r).Value = "250000 (MT Solar Custom Order)" [color=blue]Then[/color]
            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
 

kelseyd91

New Member
Joined
May 15, 2015
Messages
6
Thanks so much Alan! That code worked perfectly. I did not realize that everything needed to be specified that way. Thanks again.
 

Watch MrExcel Video

Forum statistics

Threads
1,101,904
Messages
5,483,639
Members
407,400
Latest member
Rakeforms

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top