asked on

Coping data one spreadsheet to another

I have an excel 2003 Spreadsheet which is generated automatically by a report each day, it always has the same headings, but different number of rows of data. For the example call it SS1

I have another spreadsheet (SS2) excel 2010 this has 3 tabs, historical, cl, rp.

I want to write a macro that copies the data from SS1, line 2 onwards, then first of all copies it to SS2 - tab historical. Each day the macro will be run, so it will need to determine the end of the data in the historical tab and add it to the next line.

Column C has a column called type (values cl or rp). If the type is cl it will copy all of the data from historical where type is cl to the cl tab and where the type is rl to the rl tab.

Again this will be cumulative each day, so it needs to determin the last line of data in cl and rp and add it to the line after

Is this possible?

Thanks

Roy Cox

SS2 will contain the code.

Will SS1 be open when the macro is run?

Why not just copy the main data to the master workbook, you can then filter by type when required. That is more efficient than having 3 sheets?

Mark Wilson

ASKER

SS2 will contain the code

SS1 is not open

I would probably the code to copy it the tab historical in SS2 - I can filter in the other two tabs.

I will need to add each days to the end of the previous days data in the historical table

Roy Cox

This should copy the new data to the historical sheet. You will need to make sure that the workbook names are correct in the code.

I suggest then another macro to clear each of the other two sheets and filter and copy the relevant data to them.

Let me know if you want to continue with my suggestion.

ASKER CERTIFIED SOLUTION

Roy Cox

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial