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.

 

[Anthony47]

Assuming that you have some knowledge on vba, I suggest this:
Open Master, then record a macro while:
-select a "wrong" sheet, select the right sheet, clear all the cells of the active sheet (in case the file has more than one sheet)
-open the first log file, select a wrong sheet, select the right sheet
-copy the valid data
-select Master, select cella A20 paste the data after the headers
-return to logfile and close it
-stop recording the macro.

Now on Master, on sheet2, create the list of all the log filenames (including extension .xls) and assign to these cells the name LogList, set in a cell the Directory where these files are stored (ex C:\Documents and Settings\NetUser\Log\ including the last "backslash") and assign to this cell the name Director.
At this point you need to modify the recorded macro:
-embed the useful code in a For each LName in Range("LogList") / Next LName
-modify the File open instruction to open the file

(LName.Value & Range("Director").value

-modify the instruction that select range("A20") with

Cells(Rows.count,1).end(xlup).Offset(1,0).Select

-modify the instruction that activate the first log file (to close it) to

Windows(LName.Value).Activate

This should be sufficient....
As usual, backup your data before any try.

Bye.

 

[kgkev]

I can understand what VBA does but I have not every written any and wouldn't know where to start.

My macro has recorded and it works fine bar 1 problem. When i close the worksheet at the end of the macro I get a msg box asking if I want to keep the data pasted on the clipboard, i have alread pasted it to the MASTER so I don't need it anymore. I clicked no.

When I run the macro it works fine except this msg box still appears - I feel this will cause problems when I have more than one worksheet to open.

I have added a new sheet to the MASTER and included the location of the files along with the file names. I have assigned the names as requested to these cells. Please can you give me hand in adding the script to my code so it does all the work books.

I have probably overkilled on the selection.end usage but I figure I may as well gaurantee that nothing is missing.

Sub Data_Merge()
'
' Data_Merge Macro
' Macro recorded 24/06/2008 by Kevin Gill
'
'
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.ClearContents
    Range("A1").Select
    Workbooks.Open Filename:= _
        "C:\Documents and Settings\keving\Desktop\Enquiry Log A.xls"
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("A2:A65535").Select
    Range(Selection, Selection.End(xlUp)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Range("A2:Q16").Select
    Selection.Copy
    Windows("Enquiry Log MASTER.xls").Activate
    Range("A2").Select
    ActiveSheet.Paste
    Range("A2").Select
    Selection.End(xlDown).Select
    Range("A17").Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Selection.End(xlUp).Select
    Range("A17").Select
    Windows("Enquiry Log A.xls").Activate
    ActiveWindow.Close
End Sub

Thanks for your help so far - I feel like I'm making progress with this now.


Just out of interest

I assumed

    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Selection.End(xlUp).Select

Would definatly select the bottom cell however with the added

Range("A17").Select

does that make the whole thing pointless - Should I delete all the specific select(CELL REF)?

 

[Anthony47]

Based on what you recorded:

Sub Data_Merge()
'
'
CColumns = "A1:H1"    '<<< Columns to copy from log files
FFRow= 5                  '<<< First free Row (headers are above)
MSheet = "Sheets1"     '<<< Sheet to use on Master
LSheet ="Sheet1"        '<<< Sheet to copy from, in Log

For each LName in Range("LogList") 
ThisWorkbook.Activate 
Sheets(MSheet).Select 
Range(Cells(FFRow,1), Cells(Rows.count,255)).Clear
    Range("A1").Select
FFName = LName.Value & Range("Director").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

Paste the macro in your "Master" file, then modify the instructions marked <<< to cope with your situation.
Then create the list with the names of the files to open and the cell with the Directory that these files are stored in, according the instruction of my previous message. Cross your fingers and start the macro.

And don't forget to backup your files before any test...

Bye.

 

[kgkev]

ange(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)

This is highlighted red and gives a syntax error. Is there a dot in the wrong place?

 

[Anthony47]

What about the missing "R" (Range vs ange)?
Did you type the instructions or just copy/past them? Does the error apper when you load the code or when you type it?

Bye.

 

[kgkev]

do you not sleep??

I copied and pasted it and missed the R - this was on the way back though - it does have it on the document.

As soon as I copied it it turned the text red.

Then when i run the macro it give the syntax error.

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)

thanks for your help

 

[Anthony47]

THERE IS A MISSING parenthesys, see this detail:
)).copy Destination:=

and not ").copy Destination:="

Sorry

 

[Anthony47]

Maybe I was sleepping while writing the code...

 

[kgkev]

Thanks - Turned black and removed the error.

It clears the sheet now. leaving the header row.

then it give runtime error 1004

Enquiry Log A.xlsC:\Documents and settings\keving\desktop.xls

Could not be found.

I have the
C:\Documents and settings\keving\desktop.xls stores in sheet 2 (A1) assigned the name director

and the list of file names in sheet 2 (b1:b3) assigned the name CellList.

I have changed

FFName = LName.Value & Range("Director").value

to

FFName = Range("Director").value & LName.Value

and now it gets one stage further. It opens the first sheet copies the data - Perfect.

the it opens the 2nd sheet and copies the date - only it copies it over the top of the first data. I think we need a "go to end of data" statement in the loop.