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

If they are coming in as "text", then what do these dates look like as
"text"... 12/8/2009, December 8 2009, 8Dec09, something else? I would think
the plan could be to parse the date text you have (after knowing its format)
into its month, day and year and put those pieces into the proper argument
location in the DateSerial function to create a date in you given system,
then assign that date to your cell.

--
Rick (MVP - Excel)


"Richard" <(E-Mail Removed)> wrote in message
news:921BE710-A095-482F-A525-(E-Mail Removed)...
>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

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