Hi all,

I have the following code that consolidates data from individual sheets from a workbook into a summary sheet. In each sheet, column A contains a daily date field and column B contains the values associated for those dates, the data is consolidated in the summary sheet by date field placed in the first column of the summary sheet.

VBA:

[VBA]Option Explicit
Sub Consolidate()
Dim tgt As Range, Source As Range, CkRange As Range, Cel As Range
Dim Rw As Long, i As Long, Dt As Range
Dim c As Range
Application.ScreenUpdating = False
'Loop through each sheet after first
For i = 2 To Sheets.Count
'Find place to post result
Set tgt = Sheets("Summary").Cells(Rows.Count, 1).End(xlUp).Offset(1)
Sheets(i).Activate
'Find data to copy and copy to target
Set Source = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp)).Resize(, 2)
Rw = Source.Rows.Count
Source.Copy tgt
'Insert - at any blank cells
Set CkRange = tgt.Offset(, 1).Resize(Rw)
For Each Cel In CkRange
If Len(Cel) = 0 Then Cel = "-"
Next
'Move data to corresponding column
CkRange.Cut tgt.Offset(, i - 1)
Next
Sheets("Summary").Activate [/VBA]

Now, instead of consolidating the data from individual sheets as per above, I would like to change the macro so that it consolidates data from multiple workbooks that I would have opened rather than worksheets from the same workbook.

Each workbook contains data in the first two columns of the first sheet:

The set up of my files looks like this:

Workbook 1 - sheet 1

Col A Col B
11/11/2006 4
12/11/2006 5
13/11/2006 6

Workbook 2 –sheet 1
Col A Col B
11/11/2006 19
13/11/2006 20


Summary Workbook – Sheet 1 (effectively one that I would create that would store the consolidated data)

I'm assuming this is relatively easy change to make to the code, but not sure how, any help would be much appreciated.

Thanks,

Lucas