So here's the setup:
I have 3 workbooks.. The first two each have one sheet in them called "master sheet".. They are: 'Master Sheet', 'Dummy Sheet' and 'tracker'. "Tracker" has 2 sheets in it... "Reporter" and " copy of master sheet"
Data is entered into the 'master sheet' workbook. I have a button setup to save the changes, which also saves the file as 'dummy sheet', then saves itself again as 'master sheet', thereby duplicating itself with a different names ( the reason for this was issues with read-only access for the master sheet)
The 'tracker' sheet has an update button to retrieve data from the dummy sheet via 'external connection'. This works fine, except when a user goes back into the Master Sheet to make entries and save the changes, the link made by the 'tracker' sheet to the dummy sheet is still active, so that when the user tries to save the changes in the 'master sheet' (which duplicates itself as the dummy sheet), they can't because excel sees the dummy sheet as still being in use.
2 questions:
First, is there a way to sever the connection for a 'get external data' request? I don't want the source removed from the book, because later on I would need to update again, by using that same retrieval. I just want to know if there's a way to 'close' the dummy sheet if I'm accessing it thru an external connection.
Secondly, if the above is not possible, it seems the only other option is to have the update button on the tracker actually OPEN the dummy sheet, copy what it needs, close the dummy sheet, and paste the info into the tracker. I know this is possible but I don't know how to code it?.
I can post some code of the update button in the morning...
Thanks!
Bookmarks