Pulling values from sheet that changes

**jrhertel** · 03-10-2007, 12:52 PM

My company posts a report that's updated throughout the day with numbers, about 1000 lines of them. I made a sheet that pulls out the numbers I need and puts them into a clean sheet. The problem is that this only works until they change the order of the numbers, which is often. I was thinking of making macro's that would search for the name of the numbers I want (ex. "Profit") then copy the location (not the value in the cell but the location, ie A20) to my sheet so I can pull numbers from that row. Do you think this will work? How do I copy the location of a cell to another sheet? Any better ideas?

**duane** · 03-10-2007, 01:08 PM

since your link will only work until the spreadsheet is updated, why not have the macro just grab the value you want?

**jrhertel** · 03-10-2007, 01:12 PM

Originally Posted by duane

since your link will only work until the spreadsheet is updated, why not have the macro just grab the value you want?

Column A has the name of the numbers then columns B-H have numbers in reference to that. I want to search through for a name in column A and copy all the numbers from B-H in that row. I tried using a macro to search for the 'name' from column A then copy and paste those numbers however the copy function would pull the numbers from the cell location that I originally copied from...ie. If 'profit' was in A20 today then tomorrow A22, it would still copy the values from A20-H20 rather than A22-H22

**duane** · 03-10-2007, 01:22 PM

let's say "profit" is in cell a1 of sheet sheet1
and you are looking in sheet2 for the data in rows 1:100, column A

 
myword = Sheets("sheet1").Cells(1, 1).Text
Set rng = Sheets("sheet2").Range("a1:a100").Find(myword)
If rng Is Nothing Then GoTo ' somewhere to go if "profit" not found
myrow = rng.Row
range(cells(myrow,2),cells(myrow,10).copy

'and paste them where you want

'note you will have to specify the workbooks in the above code

**jrhertel** · 03-11-2007, 11:50 AM

 myword = Sheets("sheet1").Cells(1, 1).Text
    Set rng = Sheets("sheet2").Range("a1:a100").Find(myword)
    myrow = rng.Row
    Range(Cells(myrow, 2), Cells(myrow, 10)).Copy
    Sheets("sheet1").Cells(2, 1).Paste

Above is my code. I'm having an error on the red line, anyone see anything wrong with it?