I have a workbook with multiple tabs, each of which take data from mutlipe
spreadsheets using a Query. I then use the code below, to consolidate all the
data on the tabs into one table, so i can PivotReport on it. The problem
comes from the columns with dates in it - they import using the Query in UK
format, but after the code has brought the tabs into one, they become US
format as text. Can anyone help correct this, as i'm having to use a
workaround formula to rearrange the cells at the moment.

Sub collate2()
Dim firstRow As Double, lastRow As Double, srcRng As Range, destRng As
Range, ws As Worksheet
firstRow = 2
For Each ws In Sheets
'goes through the workbook grabbing data, if the sheet isn't called
"orders"
With ws
If LCase(.Name) < "analysis" Then
'finds last used row by selecting last row and simulating
ctrl-up arrow
lastRow = .Cells(Rows.Count, "B").End(xlUp).Row
If lastRow = firstRow Then 'makes sure there's some data to
copy
'gets range of data in current sheet
Set srcRng = .Range(.Cells(firstRow, "A"),
..Cells(lastRow, "AL"))
'gets range of first cell on row below data already on
sheet
Set destRng = Sheets("MasterSheet").Cells(Rows.Count,
"B").End(xlUp).Resize(lastRow - firstRow + 1, 40).Offset(1, -1)
destRng.Value = srcRng.Value
End If
End If
End With
Next ws
End Sub
--
Richard