updating spreadsheets automatically

[fishead123]

Hi i have a spreadsheet that links to an online data source that is updating on a regular basis.
Can someone tell me how i can copy and paste these updates into a separate tab or separate spreadsheet altogether?

Updating spreadsheet has 10 rows of data that is updated every minute.
I would like this data to be collected on the 2nd spreadsheet without erasing the previous cut/paste etc. So that over time this spreadsheet will have 100's of rows of data.

thanks in advance.

[Richard Buttrey]

One way would be a macro that runs every minute and copies the 10 rows to the second sheet.

[fishead123]

Thanks Richard, i've never used macros in excel - only in MSAccess. can you possibly assist on this?

[Richard Buttrey]

Sure,

Would you upload the relevant workbook. See the big yellow banner at the top of the forum.

[fishead123]

humm... trying to send attachment....

[fishead123]

Would be great if livedata got dumped into rawdata every minute. Idea is to have compiling list of entries in the rawdata wksh
tks,

[Richard Buttrey]

What is the @QUOTE function?

It's not one I recognise. Are you using Excel with some intermediary software to access an external source?

I had originally thought you were using Excel's Data Connection functionality to a table on a URL link.

[fishead123]

Yes someone created an addin that pulls stock data off the internet .
I can manually cut/paste and change that section for each cell but it's time consuming. I was hoping there was a way to manually update the cells in column A and have the rest of the columns update accordingly.

[Richard Buttrey]

Of course I don't have the add in so can't test it, hence my puzzlement over the @QUOTE

However try this. Copy the column labels in A1:Y1 of Live data to A1:Y1 in raw data

Now add these module level procedures. Change the TimeValue as necessary.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Now add these two Workbook Event procedures

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[fishead123]

Thanks Richard.
I've never dealt with module procedures before and you better explain what i'm to do with this code exactly?

[Richard Buttrey]

Go to the VBE - I generally use Alt-F11 - other methods are available.

In the VBA Project pane on the left double click on the Workbook object, in the left hand drop down of the big code window pick 'Workbook'.
Then with the right hand drop down pick the Workbook_BeforeClose and Workbook_Open event procedures and put the code I gave you in there.

Then in the VBA Project pane expand the Module object and double click the Module1.
Then copy and paste the other three procedures into there.

You can now close the VBE. The macros will start to run the next time you open the workbook.