Just to add to Rick's notes...

The problem I've had with stuff like this is that some dates come as text--and
others come in as dates--just the wrong dates.

12/31/2009 looks like a date in my USA settings and is brought in as Dec 31,
2009.

31/12/2009 doesn't look like a date in my USA settings and is brought in as
text.

But the problem is these kind of things...

06/07/2009 It looks like a date and is brought in as Jun 7, 2009. But the real
value for this date is July 6, 2009.

So if I convert the "text dates" to real dates, I've still got all those real
dates that don't reflect what they should.

If it's at all possible, I'd try to bring this data in as text--and then parse
it myself.



Richard wrote:

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

--

Dave Peterson