JazzSP8
Well-known Member
- Joined
- Sep 30, 2005
- Messages
- 1,227
- Office Version
- 2013
- 2010
- Platform
- Windows
Hey All
Weird one here, I've had this before but I've always been able to bodge my way around it somehow, but this time I can't and it's finally time to try and solve the problem once and for all.
I'm from the UK - When I'm using VBA to copy and paste a worksheet from a workbook on our company network drive to a workbook on my local desktop that has dates in it, the dates that can be changed to US format are being.
So, for example;
<colgroup><col></colgroup><tbody>
</tbody>Becomes...
<colgroup><col></colgroup><tbody>
</tbody>
What was the 11th June 2018 becomes 6th November 2018, the other dates that can't be swapped around aren't.
It's not a formatting issue I don't think as the value is being changed from 43262 (11/06) to 43410 (06/11)
I've checked the "Locale" information in both Excel and Control Panel, both are set correctly to English (United Kingdom)
If I just open the Workbook on the network drive and copy and paste the sheet into a new workbook manually, the dates remain correct.
It's not just these dates that are being swapped around, any time the day / month can be swapped and made into a US format date it is being doing 11/12 becomes 12/11 and 06/07 becomes 07/06 etc.
There is nothing fancy about the code I'm using to copy and paste the worksheet;
(Fname is just the variable part of the filename defined by a Cell earlier in the code)
This makes no sense to me
Anyone come across anything like this before?
Weird one here, I've had this before but I've always been able to bodge my way around it somehow, but this time I can't and it's finally time to try and solve the problem once and for all.
I'm from the UK - When I'm using VBA to copy and paste a worksheet from a workbook on our company network drive to a workbook on my local desktop that has dates in it, the dates that can be changed to US format are being.
So, for example;
| 26/06/2018 |
| 26/06/2018 |
| 11/06/2018 |
| 11/06/2018 |
| 25/06/2018 |
| 25/06/2018 |
<colgroup><col></colgroup><tbody>
</tbody>
| 26/06/2018 |
| 26/06/2018 |
| 06/11/2018 |
| 06/11/2018 |
| 25/06/2018 |
| 25/06/2018 |
<colgroup><col></colgroup><tbody>
</tbody>
What was the 11th June 2018 becomes 6th November 2018, the other dates that can't be swapped around aren't.
It's not a formatting issue I don't think as the value is being changed from 43262 (11/06) to 43410 (06/11)
I've checked the "Locale" information in both Excel and Control Panel, both are set correctly to English (United Kingdom)
If I just open the Workbook on the network drive and copy and paste the sheet into a new workbook manually, the dates remain correct.
It's not just these dates that are being swapped around, any time the day / month can be swapped and made into a US format date it is being doing 11/12 becomes 12/11 and 06/07 becomes 07/06 etc.
There is nothing fancy about the code I'm using to copy and paste the worksheet;
Code:
Workbooks.Open Filename:="Z:\Pricing Sheets\Pricing Analyser - " & FName & ".xlsm", ReadOnly:=True, UpdateLinks:=xlUpdateLinksAlways
ActiveSheet.AutoFilterMode = False
Cells.EntireColumn.Hidden = False
Cells.Copy
ActiveWorkbook.Close savechanges:=False
ActiveSheet.Paste(Fname is just the variable part of the filename defined by a Cell earlier in the code)
This makes no sense to me
Anyone come across anything like this before?
