Entire Row Copy if cell value

Leeinsa

Board Regular
Joined
Jul 24, 2009
Messages
96
I have a workbook with several worksheets in it. On the first sheet is the main data and a drop down list. What i need to do is when a selection is made using the drop down list, look down column A in sheet1 and if the value of the first cell matches the selection made in the drop down list, then copy the entire row to the next available row on sheet2.

Is this possible? Any help or guidance would be much appreciated.

Lee.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

PCL

Well-known Member
Joined
Jul 15, 2008
Messages
1,348
Leeinsa,
It is certainly possible.
How is the drop down list: Data Validation, Combo Box ...
How to read the value of the drop down selection.
 

Leeinsa

Board Regular
Joined
Jul 24, 2009
Messages
96
PCL,

The drop down list is from the Developer>Insert>Form Controls>Combobox menu in excell2007. The selection from the drop down is currently list is reflected in a separate cell which then needs to be compared with the first cell in each row of my sheet. It is probably worth mentioning that there may be blank rows in my data but never more than 2 or three rows in a group. Its also worth mentioning that the data in the cells concerned are text strings, not numbers.

Many thanks,

Lee
 
Last edited:

PCL

Well-known Member
Joined
Jul 15, 2008
Messages
1,348
Here a piece of code which could help.
The ComboxBox is linked to cell named MyVALUE.
The last cell in sheet2 is fixed by column A.

Code:
Option Explicit
Sub Copy_row()
Dim LASTROW_1  As Long, LASTROW_2  As Long
Dim MyVALUE
Dim MyRG As Range
Dim F
    Sheets("Sheet1").Select
    LASTROW_1 = Range("A" & Rows.Count).End(xlUp).Row
    LASTROW_2 = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
    MyVALUE = Range("MyVALUE")
    Set MyRG = Range(Cells(1, "A"), Cells(LASTROW_1, "A"))
        With MyRG
        Set F = .Find(What:=MyVALUE, After:=.Cells(1, 1), LookIn:=xlValue, _
                LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False)
        If (Not F Is Nothing) Then
            Rows(F.Row + 1).Copy Destination:=Sheets("Sheet2").Range("A" & LASTROW_2+1)
        End If
    End With
End Sub
 
Last edited:

Leeinsa

Board Regular
Joined
Jul 24, 2009
Messages
96

ADVERTISEMENT

Hi PCL,

Thanks for the reply, I tried your code but when it runs, i get an error saying Subscript out of range. Unfortunately, it does not go into debug mode so I cant tell where it it happening. I used some cells to view the value of the LASTROW_1, LASTROW_2 and MyVALUE. and they all report the correct values, So i thinkits freaking out when it gets to the Set MyRG line of code. I am not clued up enough to be able to fix this issue.

Any further help or even a working copy would be much appreciated.

Thanks,

Lee.
 
Last edited:

PCL

Well-known Member
Joined
Jul 15, 2008
Messages
1,348
To see where it happens you could go to ( with EXEL 2003):
Tools > Macro > Step Into
and using the F8 key see where it hits.
 

PCL

Well-known Member
Joined
Jul 15, 2008
Messages
1,348
The macro sent must be recorded in a module because we are working with several sheets and want to access to these sheets: It cannot be in sheet code.
 

Forum statistics

Threads
1,144,567
Messages
5,725,028
Members
422,589
Latest member
JohnnyBravo1

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
Top