Absolute to Relative Cell Referencing Macro Help!

Hello everyone,

I really couldn't find anything on this in the search, so I really hope I'm not beating a dead horse with this one.

I am trying to create a macro that will automatically copy data from a cell and paste it in a list in another worksheet. Let me explain a bit more;

I am creating a workbook that takes stock prices/highs-lows/change%/etc. for a certain company and assimilating that data into separate worksheets for each category. The stock information is imported directly via "refreshable stock price" feature in excel. So the worksheet this data is imported in is refreshed in the macro and then I manually copy and pasted each entity into it's respective category worksheet. So when you hit the "Update" button it refreshes copy/paste all data over and over again. The problem I am having is that the imported data is supposed to be an absolute reference and the other worksheets that store the data over time should be pasted with relative references to create the list. My VB knowledge is fairly limited and I tried using the "Use Relative References" button by switching it on and off when I am copying and pasting but when I run the macro it is not working. It just pastes the cells over and over.

Here is the macro ( I didn't have time to clean it up so there might be some redundant coding in there) Also I did some formatting stuff at the end that can be ignored I think. Any help will be greatly appreciated.

Please Login or Register  to view this content.

http://www.mediafire.com/?p2lu94helhd5g4y

Unfortunately your post does not comply with the forum rules. Can wrap you code in code tags, see my sig for details. And if it is possible, could you post a sample file and what you would like to achieve to help your problem?

OK, see the attached workbook.
Up the top of the Company Profile worksheet is a button "update" . Press this and it will give you the values for that date. The data is in cell "B2" and I have set it to automatically update each day, so if you are entering data once a day for that days trading it will automatically update for you. There is a prompt that allows you to overwrite today's data if you mistakenly upload the wrong data and want to change it. Clicking no will allow fresh data to be added to the sheets.

If you want to play around with the dates, you can manually enter the date in cell "B2" to test the code.

Please Login or Register  to view this content.

Wow, I can't thank you enough. This is so simple but so much more effective then my attempt. I really need to build up my VB knowledge. I am so impressed. Thank you!!

Would it be very hard to add additional items or companies to your code?

Have a look at the new workbook. If you keep the same format for each company information, you can add as many companies as like at any time you need. The code will automatically adjust for the added companies data. But, I stress that you can't change the format of the Companies profiles sheets, you can add as much data going down the page, but you can't add across the page. With the other pages that you are transferring data to, you can add the companies name across the page in the order that they are in on the companies profile sheet.

Please Login or Register  to view this content.