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?

Mark WilsonBI DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
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 WilsonBI DeveloperAuthor Commented:
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 CoxGroup Finance ManagerCommented:
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.
Roy CoxGroup Finance ManagerCommented:
Sorry missed your reply. The code that I pasted should do what you want but will need to open SS2.

Something like this
Option Explicit

Sub CopyData()
Dim wb As Workbook
Dim rCopy As Range, rCl As Range

Set wb = Workbooks.Open(Filename:="C:\SS1.xls")
Set rCopy = wb.Sheets(1).Range("A1").CurrentRegion
With ThisWorkbook.Worksheets("historical")
Set rCl = .Cells(.Rows.Count, 1).End(xlUp).Offset(1)
End With

 rCopy.Copy rCl
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.