VBA code to Find and Copy Data

Microsoft Excel 2003 (full product)
June 21, 2010 at 11:24:37
Specs: Windows XP
I have a 3 sheet workbook. I have data that is entered in the "entry" sheet. In the "report" sheet I need to input a week number. I would then have excel look into the "entry" sheet in a specific row, find the same week number . Then select a range of cells fromthe "entry" sheet and copy/paste the cells frm that range into the "report" sheet in a different row/column. Any help you can provide would be greatly appreciated!


See More: VBA code to Find and Copy Data

Report •

June 21, 2010 at 12:36:06

Assuming the week numbers are on one row and the relevant data for each week is in the same column, below the relevant week number, then use a series of HLOOKUP() functions.

These can be used to return data from the selected week number column, in the entry sheet.

The HLOOKUP() functions are on the report worksheet, wherever you need data from a specified week.

If cell A1 on the report sheet is used to select the week number, and week numbers are in row 1 on the entry sheet, the cell formula looks like this:
In this case a value is returned from the second row of data below the week numbers. (The week numbers themselves are row 1).
To work, the range of data specified must contain at least as many rows as the offset value - see the help file).


Report •

June 21, 2010 at 12:48:00
Thank you. I tried the HLookup and am not getting the results.

Here is what I did:

=HLOOKUP(B5,'Entry '!C17:BC17,'Entry '!20:20,FALSE)

I tried it with just , 20,FALSE, but that also returned Ref#

I really appreciate your help


Report •

June 21, 2010 at 13:01:56

1. I am assuming that the worksheet name 'Entry ' does include a terminal space.

2. You don't use ''Entry '!20:20'
The third argument is a numerical value representing the row offset.
If your week numbers are on row 17, then row 20 is 4

3. The initial range 'Entry '!C17:BC17 must include the required row of data - in this case row 20

The formula is likely:
=HLOOKUP($B$5,'Entry '!$C$17:$BC$20,4,FALSE)

Use $ signs for addresses which will be fixed - cell B5 for example, so that if you copy or drag the formula to another cell, it still refers to B5.


Report •

Related Solutions

June 21, 2010 at 13:07:38
THANK YOU - That really helped

Again sorry for the 2 feeds!!

Report •

June 21, 2010 at 13:23:29

Glad to have been able to help.

If you use the formula to copy data from a series of rows, say 20, 21 & 22
and your first formula is on row 9 of the report worksheet, you can use this:
then drag the formula down two more rows.

The formula will look the same, but the returned values will be from sequential rows.

On row 9 the offset was 9-5 (= 4)
The next row it is 10-5 (=5)
and on row 11 it is 11-5 (=6)

The row() function just returns the number of the row that it is on.


Report •

Ask Question