I have two Sheets in my Excel Work book (Sheet 1 and Sheet 2).

Sheet 1 is set up thus;

Row 1 is a title row, with the following headings;

Cell A1 CC
Cell B1 Number

In rows 2 to 20 I have the following information;

Cells A2 to A20 the following numbers 120, 130, 140, 150, 160, 170,
180.
Each number appears at least once (obviously all numbers are repeated
in order to occupy the rows 2 to 20)

Cells B2 to B20 various numbers (exact details not relevant).

What do I want ?

Well, I would like a macro that;

Opens with a Message Box, prompting you for a CC number.
Then, using this number, works its way down the rows from 2 to 20 and
selects those rows which match the CC number entered.
Once these rows have been selected it copies these rows and then pastes
them into Sheet 2 (starting at row 2, column A).

In summary, I have a database from which I would like to interrogate,
based on the CC number entered into the Message Box, the return value
being the entire row on Sheet 1.

Can anyone Help ?



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

This code will do what you require, assuming there are
only 20 rows on your first sheet and all the entries in
column A are numbers. If the number can change then amend
X = 2 to 20 to
X = 2 to application.worksheetfunction.counta(sheets
(1).Columns(1))-1

Sub PickARow()

Dim X As Integer
Dim y As Integer
Dim WhichCC As Integer
y = 2

WhichCC = InputBox("Which CC do you want to view data
for?")
Sheets(2).Rows("2:100").ClearContents

For X = 2 To Application.WorksheetFunction.CountA(Sheets
(1).Columns(1))
If Sheets(1).Cells(X, 1).Value = WhichCC Then
Rows(X).Copy Destination:=Sheets(2).Cells(y, 1)
y = y + 1
End If
Next X


End Sub

Cheers, Pete

Sorted it out thanks ....... brialliant !



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/