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.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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?
 
Upvote 0
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 ?
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
Thanks so much Alan! That code worked perfectly. I did not realize that everything needed to be specified that way. Thanks again.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top