I have a spreadsheet that uses ODBC/MS Query to connect to a database and
pull in part numbers each time the worksheet is opened the file refreshes.
The part numbers are put in a column, and the contents from this column are
copied to another worksheet using the copy paste special option.

Each time the ODBC connects and refreshes the data the cell numbers change
and causes the links from source to target not to work. For example if I have
20 part numbers in cells C1:C20 in the source worksheet, which are copied to
the target worksheet when I created the orginial links, the next time the
file is opened/refreshed the cells in the source worksheet may be C50:C70
or some other number the target worksheet is still expecting to copy C1:C20
from the source worksheet.

The part numbers in the target still show the orginial values when I created
the orginial links from cells C1:C20 in the source worksheet.

How can I fix this so the copy-paste-special links will always copy the
current data contained in the source worksheet after the file has refreshed
regardless of the cell numbers?

I don't understand why the results of a query would move from C1:C20 to
C50:C70 when the query is refreshed. I can accept that the number of
rows returned could differ and that individual items might be on
different rows. I will assume that is what you meant and that the
results table still starts at the same location.

Is the copying done by a macro currently?
If so please post the code.
If not, please record a macro while doing the operation manually and
post the recorded code.
That way it will be clearer what we are both talking about.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

I refreshed the query again today, and the cells stayed consistent i.e
C29:C30, but the data from source worksheet to target worksheet in the same
workbook was still not updating upon opening or refreshing the file.

If I manually delete data in say cell C29 or C31 from the source worksheet
the target data updates automatically. Why wont the target data update from
the source upon opening or refreshing the query?

I followed the Excel help menu for recording a macro but did not see any
data. I am not using a macro only copy-paste-special link, plus my MS Query
to extract information from my database via ODBC which works fine. Can you
please help!

"Bill Manville" wrote:

I don't understand why the results of a query would move from C1:C20 to
C50:C70 when the query is refreshed. I can accept that the number of
rows returned could differ and that individual items might be on
different rows. I will assume that is what you meant and that the
results table still starts at the same location.

Is the copying done by a macro currently?
If so please post the code.
If not, please record a macro while doing the operation manually and
post the recorded code.
That way it will be clearer what we are both talking about.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Not a problem I have heard of before.

What version of Excel are you using?

When the links have not updated after a refresh of the table, what
calculation mode are you in (Tools / Options / Calculation)?
If you are in manual mode they would not normally update.
If you are in automatic mode they should update; does Ctrl+Alt+F9 cause
them to update correctly?.

What is the formula in one of the cells containing the links?

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

What version of Excel are you using? 2000 (9.0.6926 SP-3)

I am in automatic calculation mode.

Ctrl+Alt+F9 does not cause them to update after the file is opened or
refresh, but if I manually delete the contents of the cells in the source
worksheet the target worksheet updates correctly.

The formula in one of the cells containing the links is =RAW DATA!C31

The name of the source worksheet is called €œRAW DATA€�.

Please help,
Chris Petta


"Bill Manville" wrote:

Not a problem I have heard of before.

What version of Excel are you using?

When the links have not updated after a refresh of the table, what
calculation mode are you in (Tools / Options / Calculation)?
If you are in manual mode they would not normally update.
If you are in automatic mode they should update; does Ctrl+Alt+F9 cause
them to update correctly?.

What is the formula in one of the cells containing the links?

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

I can only suggest that there is something wrong with your worksheet.
Ctrl+Alt+F9 recalculates all formulas in the workbook so if your links
are not refreshed it must be because the calculation chain is not
properly formed.

Try Edit / Replace / = with = / Replace All
on each worksheet

That should rebuild the calculation chain

If that doesn't work, try rebuilding the workbook from scratch.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Thanks. I'll give it a try and let you know the results. Also, Thanks for the
quick responce. This is a great user group and more valuable than an online
training. Have a great weekend.

"Bill Manville" wrote:

I can only suggest that there is something wrong with your worksheet.
Ctrl+Alt+F9 recalculates all formulas in the workbook so if your links
are not refreshed it must be because the calculation chain is not
properly formed.

Try Edit / Replace / = with = / Replace All
on each worksheet

That should rebuild the calculation chain

If that doesn't work, try rebuilding the workbook from scratch.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup