A Macro to collate my data into a single workbook

[Henrybukowski]

Hi all,

This forum has helped me a good many times through the posts of other people, but this time I think I need to post for myself.

I'm generally pretty good at IT and solving my own problems, but Visual Basic is a step beyond me, I feel. This said, the Macro I need to create may very well be straightforward for the guys on this forum.

Here's the situation:

1. Every month we collect data from a number of employees and average it out in a new excel workbook ready for analysis. Each employee sends in their monthly data, structured identically, with only the numbers in the data tables different.
2. At the moment, we just copy and paste every single data return into the separate worksheets of a single workbook, and on the front page of the workbook we create a simple formula which adds the equivalent cell in each sheet together to get a total. Its not hard to do, but its very lengthy as a process.
3. What I've been researching, is the possibility of a macro that can automatically send the outputs of separate workbooks into one central workbook, so that we don't need to go through the copy and pasting of each return every month.

E.g, Say I have 10 workbooks, named Book1, Book2, Book3 etc, which each have a number ranging from 1 - 10 in cell A2. Could A macro be designed to bring all those values together into cell A2 of an 11th workbook, e.g one entitled book11? If so, could somebody assist or advise me in creating one? I'm aware that said Macro may need to exist in each separate workbook in order for the process to be carried out.

I really appreciate the efforts of anyone who can help me - I cannot stress enough how this could change the way we do things at work.

Henry

 

[danzon]

I think this code should do exactly what you need... it has worked for me in similar scenarios

WBs to 1 Sheet - Jerry Beaucaire's - Excel Assistant​

 

[Henrybukowski]

Many thanks for this Danzon, it looks liek what I'm trying to achieve.

However, I need some extra clarification:

"path and filename (edit this section to suit)

'This is the section to customize, replace with your own action code as needed
LR = Range("A" & Rows.Count).End(xlUp).Row 'Find last row
Range("A1:A" & LR).EntireRow.Copy .Range("A" & NR)
wbData.Close False 'close file
NR = .Range("A" & .Rows.Count).End(xlUp).Row + 1 'Next row
Name fPath & fName As fPathDone & fName 'move file to IMPORTED folder
End If
fName = Dir 'ready next filename
Loop
End With

Which bits of the above do I customise?

fPath = "C:\2011\Files\</STRONG>" Does this refer to the folder in which my files are or to a specific file? In general, what does 'fPath' mean or do?

Set wsMaster = ThisWorkbook.Sheets ("Master") Do I need to replace the text "Master" with something else?