Combining and aligning data from multiple sheets

[Zig208]

I have a large number of worksheets containing date series data (first column has date, second has value corresponding to that date). I'm looking for a macro which will combine these worksheets and align the data so that the data for any given date end up in one row. I've found a macro which combines multiple sheets into one, but don't know how to align the data by date. Any suggestions are welcome. Also...I'm trying to do this with Excel 10 (64 bit) because the resulting combined worksheet will be very large. Thanks in advance for any guidance.

[rbrhodes]

Hi Zig#

1) Are these 'WorkSheets' all in one 'Workbook' eg one file? (A lot of people use the words interchangeably)

1a) If they are in separate WB's are they all in one folder (directory)?

[Zig208]

Hi DR and thanks for your reply. The worksheets are all separate files in one folder. One additional piece of info is: the individual worksheets will span a variety of different dates, but I'd like to be able to select a range of dates and have the macro pick out those values that correspond to those dates. Thanks again, Zig

[rbrhodes]

Ok, so 'Worksheets' is actually 'Workbooks'.

Just so you know:

- a workBOOK is a file
- a workBOOK is composed of min of 1 workSHEET sometimes many more

This is a common problem of vernacular, but when coding for something it is _fatal_ which is why it was my first question. The second question is usually: "What didn't you tell me?" but you got that one <grin>

And as always - you get three OH-BY-THE-WAY's included free - after the 3rd one they will cost you dearly!


I'll have a look.

[rbrhodes]

Questions:

- Drive\Directory name is same everytime, or browse for it each time?

Put path in code or ask user to select path?


- Are WB's (files) named with dates or open all files?

Look for files with 'date' in name or open each and every file and look for dates in file

- 1 sheet per WB or check all?

Always Sheet1 or could be multiple sheets per WB (file). Do all

- Do you have macro you speak of and example files?

Post or email them to me

'//End

[Zig208]

Hello dr,
I've attempted to answer your questions and provided example files in an email. Please let me know if you need further info.
Best,
Zig

[rbrhodes]

Hi Zig,

I got notice of this post but no email?

Decode this for my address:

ExcelVBA <shift + 2> (the at sign) dot shaw dot ca

[Zig208]

Hmmm... I sent it to your ExcelVBA@joe... address from your website. Did you get it there?
Best,
Z

[rbrhodes]

got it & will have a look...

[Aussiebear]

Please don't use a public forum for private discussions. Ifyou take the time to raise an issue publicly..... then keep it there. Don't want it to be discussed in public then don't raise it here.

[rbrhodes]

Hi AB

Noted.

Sorry for taking it 'off the board' but continuing here.

Dusty