Combine data from multiple files into a master file using VBA

Hello

In continuation to my previous posts on this subject, I have set up a master file which combines data from 6 different files. My query has 2 parts.

Query 1
I am currently trying to combine the data in the Spot Quotes Tab from each individual user file into the "Combine Data" Tab of the master file (South UK Spot quotes). The code works fine except for 2 of the user files - Price Desk Quotesheet M and Price Desk Quotesheet Mi. In the case of these files, it is not determining the last row in the file correctly (I am not sure if this is because the first data row Row 12 / Column D is blank in these files) and therefore copying the header. I did try to put in a code if there is no data but not sure if I have got it right...Need some ideas on how to resolve this.

Query 2
The second stage of this project is to copy the data from each tab (Spot Quotes, Network Quotes and Sales Proposal) in each user file to the Combined Data, Combined Data Network and Combined Data Sales Prop tabs respectively in the Master file. My idea was to replicate the code I have for the Spot Quotes data for each of the other tabs, but is that the most efficient way of doing it?

Hope to have some help on this.

Thank you.

1 Headers in Row 11 like in your file
2 Loop for Columns D to Y to find the last Row

maybe this give you idee for query 2

Please Login or Register  to view this content.

Cheers
Leo

[QUOTE=LeoTaxi;4497908]1 Headers in Row 11 like in your file
2 Loop for Columns D to Y to find the last Row

Hi Leo

Thanks for responding.

I checked all files and the headers are in Row 11 in each file. Im not sure I understand what you mean? I am new to vba so so appreciate if you could explain the above.

Thanks!

to understand you have to read your own code
the part where this comment is

we dont want delete the headers

Did you test the Code ?

Cheers
Leo

the code was working fine until I added the 2 sheets - Prices Desk Quotesheet M and Prices Desk Quotesheet Mi.

Yes the code does say "don't want to delete the headers - and I don't want to delete the headers! but I do not want the code to copy the headers out from each file either....hence the query. The code is only copying the headers for these 2 files so I guess for some reason it is not able to determine which row is the header row..

Code i provided does not copy headers, only data

cheers
Leo

If the result from code i provided is different from desired result, post a file with desired result.


Cheers
Leo

Hi Leo

Sorry for the delay in getting back to you... I tried the code....I am getting an error at this stage... "Run time error 1004: Application defined or object defined error

.Range(Cells(12, "D"), Cells(RowNum, "Y")).Copy sh.Range("D" & lr2 + 1)

What does this error refer to?

Thank you

If the result from code i provided is different from desired result, post a file with desired result.

can nothing do without file

cheers
Leo