Combine 13+ spreadsheets onto one Master Sheet

[kgkev]

We have 13 salesman each completes a quotation log (each log is identical - except for the data obviously)

For month end and reporting purposes it would be useful to have 1 single log sorted by date.

It is not possible for each sales man to input the data into one log as some are away on business and take the log with them.

The logs are all stored in the same directory however there are other files and excel spreadsheets in the same directory.

This is how I would like to see it working - On opening the "MASTER" sheet it automatically (no user input required) clears out the old data, then gathers all the data from 13 spreadsheets, sorts it by quote date and dumps it into a new workbook.

We will be using excel 2000, 2003 & 2007 I can enable macros on all machines if necissary.

The spread sheets are very simple, single sheet with no formulas - just manually adding data. There will be no blank rows and data will be held in the range A - S with rows 1-4 being headers and titles

I can upload an example of the spread sheet but it is as basic as you can get so I don't think it is necissary.

 

[kgkev]

Maybe I was sleepping while writing the code...

still not bad!!

 

[Anthony47]

Congratulation for debugging the error.

Now, I test colum A to determine which is the first empty line; is col A surely filled with data, or is it unreliable?
And I use the same column to select the valid data from the current log...

Try this:
-set a break on Range(Cells(2,1), cells(Cells(Rows.count,1).end(xlup).Row,Range(CColumns). etc etc (set the curson into the code, press F9; repeat to remove the break)
-start the macro: it will stop when ready to copy from log and paste to Master; examine the data on Log (active sheet) and on Master, re-set Log as the active sheet.
-press F8; the current instruction will be executed and the next one will be highlit; reexamine the data on Master, then re-activate Log sheet.
-press F5 to run the macro; repeat the data examination process and let me know what are your findings.
Remember that I test column A to determine up to which line both Log and Master are filled..

I don't understand the value set in A1=Director: why C:\Documents and settings\keving\desktop.xls and not C:\Documents and settings\keving\ only (the "names" are in the other list)?

Bye.

 

[kgkev]

Congratulation for debugging the error.

just a good guess!!

I don't understand the value set in A1=Director: why C:\Documents and settings\keving\desktop.xls and not C:\Documents and settings\keving\ only (the "names" are in the other list)?

Its actually C:\Documents and settings\keving\desktop (no.xls)

Coloumn A contains a date and is always there - Never ever blank.

I missed the Reactivate "log Sheet" a couple of times so it kept closing the wrong book.

Any way.

Up to the break it has cleared the sheet apart from the header
1. opens logfile.
2. copies data to MASTER
3.Closes logfile
4.returns to the top of the loop
5.it now clears the data again apart from the header

I think

Range(Cells(FFRow, 1), Cells(Rows.Count, 255)).Clear

is the culprit - Shouldn't be inside the loop.

changed it to

Sub Data_Merge()
'
'
CColumns = "A1:Q1"    '<<< Columns to copy from log files
FFRow = 2                 '<<< First free Row (headers are above)
MSheet = "Sheet1"     '<<< Sheet to use on Master
LSheet = "Sheet1"       '<<< Sheet to copy from, in Log
Range(Cells(FFRow, 1), Cells(Rows.Count, 255)).Clear
For Each LName In Range("LogList")
ThisWorkbook.Activate
Sheets(MSheet).Select
    Range("A1").Select
FFName = Range("Director").Value & LName.Value
Workbooks.Open Filename:=FFName
Sheets(LSheet).Select
Range(Cells(2, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, Range(CColumns).Columns.Count)).Copy Destination:=ThisWorkbook.Sheets(MSheet).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
    Windows(LName.Value).Activate        'Safety only
Application.DisplayAlerts = False
    ActiveWindow.Close
Next LName
End Sub

Thats amazing (I'm probably a bit too exited just by a little bit of code working but Hey)

works like a dream!!!!!!

Can we make it sort by coloumn A at the end.

 

[kgkev]

I have just done this as a test and now I want to change the files to the actual ones I cannot expand the assigned cells "celllist"

My test only had 3 examples and now I want to add all file names I cannot make the assigned range any bigger.

Am I missing something obvoius?

 

[Anthony47]

I had quite a busy day but you moved ahead to the target; I am sure you learned a lot in doing that.
To extend the list, either remove the existing "range name" and define it again with the new size; or select a complete line at the middle of the existing list, then "Menu /Insert /Row"; you will insert new lines that will extend the vertical lenght of the named range.

To sort the new merged list, modify the last part of the macro as follows:

Next LName
ThisWorkbook.Activate: Sheets(MSheet).Select
Range(Cells(MFFRow, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, Range(CColumns).Columns.Count)).Select
    Selection.Sort Key1:=Range("A" & MFFRow), Order1:=xlAscending, _
        Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
        :=xlSortNormal
    Range("A" & MFFRow).Select
End Sub

(note the extra instructions between Next LName and End Sub)
But we need also to know on which line the merged list starts on Master; for this, add and adapt also this one to the initial definitions:

MFFRow = 4              '<<< First Row for data on Master

Make sure that column A is not empty on the line above the defined MFFRow.

Bye.