Updating data from one sheet to another

[highrankednoob]

So I have 1 row with 8 columns (Date/1st/2nd/3rd/4th/5th/6th/7th) on sheet one that is setup with a web query.
Sheet2 is a collection of the data within the 8 columns on the row.
When Sheet1 refreshes data if there is ANY change in the data on sheet1 then it will add another row to sheet2 at the top with the values that are listed (even if some are the same)
It is a collection of data as it is returned from the web query.
Sheet1

08/10/13

12

12

24

54

54

54

54

67

Sheet2

08/10/13

12

12

24

54

54

54

54

67

THEN Sheet1 updates after web query and shows:

08/14/13

12

12

24

35

54

46

46

67

Sheet2 would then show:

08/14/13

12

12

24

35

54

46

46

67

08/10/13

12

12

24

54

54

54

54

67

Sheet2 will collect the data in a new row each time the DATE value changes

[Richard Buttrey]

Hi,

I assume that's a typo and you mean that you have 9 columns
One way would be to use the Sheet Change event. e.g.

Please Login or Register  to view this content.

[highrankednoob]

I have figured out how to enter the code and it works perfectly. It is returning a wrong value though.
In sheet1 the date returned from the web query looks like this: "Saturday, August 10, 2013".
I have a messy function that takes this value and presents it as just "08/10/13" in the first cell of the 9 on the row I am having copied over with the VB code when the date changes.

When the date changes it copies everything over fine but the date. The Date comes back as #REF!

The function that converts the date is: =TEXT(MID(A170,FIND(" ",A170)+1,99),"mm/dd/yy")

[Richard Buttrey]

OK,
It wasn't apparent that you were copying a formula over to sheet 2 and the formula won't know how to handle the ref to the presumably blank cell A171 on sheet 2. In that case use a Paste Special Values. i.e.

Please Login or Register  to view this content.

[highrankednoob]

Worked like a charm! I am so close to completion it is scary! Thanks to everyone here I can finish my project tonight maybe.