Need to combine data from multiple worksheets in multiple workbooks into 1 worksheet

Post amended following feedback

Hello all

I have been dipping in and out of this forum for a while and generally find the answers I need from other posts. However I cant find a way to solve this issue.

I am collating a large amount of data for a payroll system change over. This involves requesting 6 locations to complete a data collection exercise each week. The data sheet is identical for each location with a separate tab for each week they are collating for.

The layout of the worksheet is as follows

Cells A to G contain employee details (Name, Job Code, Payroll Number, Location of work etc)
Cell H is unique to each worksheet and indicates the week commencing date
Cells I to Y contain the payroll data I am asking sites to complete.

I have several processes I need to go through in order to get this data from a format that is easy for the sites to complete to somthing i can migrate into the payroll system.

Step 1
I need to combine all the data across the multiple worksheets and workbooks into 1 worksheet

I have found a macro to combine multiple worksheets into 1 within the same workbook (given below). What I need however is to be able to combine the worksheets across all 6 workbooks into 1 worksheet.

The macro I am using currently is

Sub ConsolidateSheets()
'JBeaucaire (6/26/2009)
'Merge all sheets in a workbook into one summary sheet (stacked)
Dim cs As Worksheet, ws As Worksheet, LR As Long, NR As Long
Application.ScreenUpdating = False

If Not SheetExists("Consolidate") Then _
Worksheets.Add(Before:=Sheets(1)).Name = "Consolidate"

Set cs = Sheets("Consolidate")
cs.Cells.Clear
Sheets(2).Rows(1).Copy cs.Range("A1")

NR = 2

For Each ws In Worksheets
If ws.Name <> "Consolidate" Then
ws.Activate
LR = Range("A1").SpecialCells(xlCellTypeLastCell).Row
Range("A2:AA" & LR).Copy
cs.Range("A" & NR).PasteSpecial xlPasteValues
Application.CutCopyMode = False
NR = cs.Range("A" & Rows.Count).End(xlUp).Row + 1
End If
Next ws

cs.Activate
Columns("A:AA").AutoFit
Range("A1").Select
Application.ScreenUpdating = True
End Sub

Public Function SheetExists(SName As String, Optional ByVal Wb As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If Wb Is Nothing Then Set Wb = ThisWorkbook
SheetExists = CBool(Len(Wb.Sheets(SName).Name))
End Function

If I have to manually copy and paste each of these combined worksheets into 1 new workbook and then rerun the above Macro I can live with this but if anyone can adjust the above macro to work across multiple workbooks within the same Folder that would save alot of time.

Step 2

Once this is done I then need to take all the data in columns I to Y and put them in 1 column. Not combining them but stacking them at the same time I need to keep the relevant Employee details for each payment.

As an example of this if the spreadsheet was

A1 B1 C1 D1 E1 F1
A2 B2 C2 D2 E2 F2
A3 B3 C3 D3 E3 F3
A4 B4 C4 D4 E4 F4
A5 B5 C5 D5 E5 F5
A6 B6 C6 D6 E6 F6
A7 B7 C7 D7 E7 F7

with columns A to C being the employee data then I need it to become

A1 B1 C1 D1
A2 B2 C2 D2
A3 B3 C3 D3
A4 B4 C4 D4
A5 B5 C5 D5
A6 B6 C6 D6
A7 B7 C7 D7
A1 B1 C1 E1
A2 B2 C2 E2
A3 B3 C3 E3
A4 B4 C4 E4
A5 B5 C5 E5
A6 B6 C6 E6
A7 B7 C7 E7
A1 B1 C1 F1
A2 B2 C2 F2
A3 B3 C3 F3
A4 B4 C4 F4
A5 B5 C5 F5
A6 B6 C6 F6
A7 B7 C7 F7

I am thinking that a possible solution to this stage could be to separate my data so cells I to Y are extracted into separate worksheets with Cells A to H and then re run the above macro therefore re combining the data into 1 worksheet in the format I need but any ideas how to do this would be gratefully received.

Apologies if the above description is not clear enough please let me know if you need more information.

I have attached a sample file please note the actual file will have more tabs (roughly 24) and there are 6 of them each with different employee data.

Thankyou

Richard

I think you better split the question in several parts

1. combine all worksheets in one workbook in a summary-sheet (already solved?)
2. combine all workbooks in one workbook
3. Rearange the data to your use

In that case it's more clear which part has to be done.

P.s. an excel example without confidentional information, also will help.

I get a failure in row 344 if I use your macro.

It works for you?

See the attached file.

Please also add the desired (expected) result.