VBA Date Conversion Issue

[Jake Peralta]

Hi all,

First time posting on a message board.. I have done an exhaustive search on this issue (literally been working on this since 8pm last night and it is now about 7am here) to no avail, which has led me here. I'm experiencing a common problem that a lot of people encounter when using VBA to copy and paste dates between workbooks, whereby 'ambiguous dates' in AUS/UK format such as 07/12/2020 get converted to US format i.e. 12/07/2020. To save time for those kindly assisting, I have tried countless fixes and here's what I have ruled out:

1. I've ensured my Windows regional settings are set to my region (Australia) with short date set to how I would like to see it ("dd/mm/yyyy")
2. I've done a full scale trial and error with Transition Formula Entry ("TFE") checked and unchecked between the source workbook and the destination workbook. Closest bet is to go with TFE checked in the destination workbook but then it doesn't register as a date (when I filter by date I get years grouped together for entries that were done before I had selected the TFE, but after this I get what look like dates but are not grouped within the relevant years and don't appear when I filter by a date range).
3. I've tried workarounds such as thinking perhaps I could concatenate with an apostrophe from the source workbook and then do a find and replace to remove it once in the destination workbook which didn't work, so I tried trim but this didn't work either - I just got a numerical string which doesn't convert to a date
4. I've tried Text to Columns but all my dates then just convert to 01/01/1900 or decimals depending on the settings (General, Text, Date etc)
5. I've tried setting the date format for export (and also the destination formatting) to multiple settings, including yyyy-mm-dd which I'd read had worked for some people and finally;
6. I've also tried inserting things into the code that I've found re changing formats to local etc but nothing seems to work although I feel like this is the most likely route for success.

I am very much a hack when it comes to VBA, I have literally cobbled together bits of code from all over in order to achieve what I wanted to without really understanding too many of the basics which is where things like this become a bit of a problem. Here is my code (I've posted the whole thing but the relevant part for exporting to the Master File is after the 'RDB code starts again' comment, and the specific source cell with the date in it is ThisWorkbook.Sheets("Import Export").Range("H10"):

Option Explicit

Sub UpdateTasks2()
Dim LRow As Long, i As Long
Dim varData As Variant
Dim c As Range
Dim Worksheet As Worksheet

Dim SourceRange As Range
    Dim DestRange As Range
    Dim DestWB As Workbook
    Dim DestSh As Worksheet
    Dim Lr As Long

'From here RDB Code'
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
'Inserted my code here. Checks if an authorised user has removed data validation and put in another authorised approver's name
With ThisWorkbook.Sheets("Template").Range("F7").Validation
    If Range("F7").Validation.ShowError = False Then
    MsgBox "You have removed the data validation error alert on the Authorised Approver cell. Please re-apply it for data to be saved.", vbCritical
    Range("F7").ClearContents
    Range("Copy5").Copy
Range("DATA5").PasteSpecial xlPasteFormulas
     Exit Sub
     Else
    End If
    End With
If ThisWorkbook.Sheets("Lookups").Range("PreventExport") = "Yes" Then
 MsgBox "You are attempting to change data that has already been finalised. It is now read only and cannot be amended.", vbCritical
Range("Copy1").Copy
Range("DATA1").PasteSpecial xlPasteFormulas
Range("Copy2").Copy
Range("DATA2").PasteSpecial xlPasteFormulas
Range("Copy3").Copy
Range("DATA3").PasteSpecial xlPasteFormulas
Range("Copy4").Copy
Range("DATA4").PasteSpecial xlPasteFormulas
Range("Copy5").Copy
Range("DATA5").PasteSpecial xlPasteFormulas
Range("Copy6").Copy
Range("DATA6").PasteSpecial xlPasteFormulas
Exit Sub
End If
    
   
    'RDB Code starts again
    If bIsBookOpen_RB("Master File.xlsm") Then
        Set DestWB = Workbooks("Master File.xlsm")
    Else
        Set DestWB = Workbooks.Open("C:\Users\pat\Desktop\Spreadsheets\Master File.xlsm")
    End If
    
    'RDB Code finishes here. 
 Set SourceRange = ThisWorkbook.Sheets("Import Export").Range("B10:H10")
With ThisWorkbook.Sheets("Import Export")

    LRow = .Cells(Rows.Count, 1).End(xlUp).Row
    varData = .Range("B10:H10" & LRow)
    
  
End With
    
With DestWB.Sheets("Master Data")
    For i = LBound(varData) To UBound(varData)
    
        Set c = .Range("B:B").Find(varData(i, 1), LookIn:=xlValues, LookAt:=xlWhole)
        If c Is Nothing Then
            .Cells(Rows.Count, 2).End(xlUp)(2).Resize(1, UBound(varData, 2)) _
                = Application.Index(varData, i)
        Else
            .Cells(c.Row, 2).Resize(1, UBound(varData, 2)) _
                = Application.Index(varData, i)
        End If
    Next
End With
DestWB.Close savechanges:=True

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
      MsgBox ("Master File has been updated")
    Range("NewEntryLookup").Copy
    Range("EntryNumber").PasteSpecial xlPasteValues
    Range("DATA1").ClearContents
    Range("DATA2").ClearContents
    Range("DATA3").ClearContents
    Range("DATA4").ClearContents
    Range("DATA6").ClearContents
    If Range("AuthorityLevel") = 1 Then
    Range("DATA5").ClearContents
    If Range("AuthorityLevel") <> 1 Then
If Worksheet.ProtectContents = True Then
Worksheet.Unprotect Password:="M!chael"
    Range("DATA5").ClearContents
    Worksheet.Protect Password:="M!chael"
    
  End If
  End If
  End If
  

End Sub

'Code Sources (combination of the below, with my edits)

'https://www.mrexcel.com/board/threads/update-a-master-sheet-with-changed-or-new-data-from-another-sheet.935174/page-2

'https://www.rondebruin.nl/win/s3/win001.htm

As I said, I'm very much a hack so the code above is probably highly inefficient and I've just done it all by trial and error to get it functioning properly, which it does, but the one thing I can't figure out is these dates. Any help on this would be greatly appreciated, thank you.

 

[yky]

I wonder what'd happen if you paste the serial number of a date from one region to the other? I think the serial number should be universal, regardless what region is in use.

 

[Jake Peralta]

I wonder what'd happen if you paste the serial number of a date from one region to the other? I think the serial number should be universal, regardless what region is in use.

You sir, are both a genius and a lifesaver. That one simple change in the Import Export tab and formatting the Master File with dd/mm/yyyy has worked. It works in the filter in the Master File and also when I pull it back in to the Source Workbook to filter and generate reports. Can't thank you enough - just wish I had messaged before staying up all night fiddling with it! Thanks again.

 

[yky]

You are welcome. Glad to be able to help.

 

[Jake Peralta]

I may have spoken too soon.. whilst the dates export and import properly and the filtering works when you do it manually, for some reason now when I then try to run the macro it runs my error handler which says there's no data in the date range I'm trying to filter. Obviously I've checked that there is actually data.. I'm not sure whether I should continue the query in this thread or post it to a new one but for now I'll post it here. Here is the code for the date filter which I used and please note I have also tried changing the DateBegin and DateEnd formats to Australian format but still with no luck:

Sub Between2Dates()
Dim Workbook As Workbook
Dim ws As Worksheet
Dim ws1 As Worksheet
Set ws = Sheets("Master Data")
Set ws1 = Sheets("Report Generator")
'declare the variables
Dim DateBegin
Dim DateEnd
Dim rng As Range
  
'set error handler
On Error GoTo errHandler:
'stop screen flicker
Application.ScreenUpdating = False
'set the variables
Set rng = ws.Range("B4")
DateBegin = Format(ws1.Range("C1").Value, "mm/dd/yy")
DateEnd = Format(ws1.Range("C2").Value, "mm/dd/yy")
'check the dates if all is OK run the filter
If ws1.Range("C1").Value >= ws1.Range("C2").Value Then
MsgBox " Your start date is later than your end date."
Exit Sub
Else
If Not IsEmpty(DateBegin) And Not IsEmpty(DateEnd) Then
'run the filter
With rng
.AutoFilter Field:=7, Criteria1:=">=" & DateBegin, _
Operator:=xlAnd, Criteria2:="<=" & DateEnd
'copy values
CopyFilter
'show all data
Showall
End With
End If
End If
'error block
On Error GoTo 0
Exit Sub
errHandler:
MsgBox "There is no data"
Showall
'ws1.Range("B5").Select
'https://www.onlinepclearning.com/auto-filter-dates-year-month-excel-vba/
End Sub

Hopefully the fix is just as simple as the last one!

 

[Jake Peralta]

I have figured this out!

I may have spoken too soon.. whilst the dates export and import properly and the filtering works when you do it manually, for some reason now when I then try to run the macro it runs my error handler which says there's no data in the date range I'm trying to filter. Obviously I've checked that there is actually data.. I'm not sure whether I should continue the query in this thread or post it to a new one but for now I'll post it here. Here is the code for the date filter which I used and please note I have also tried changing the DateBegin and DateEnd formats to Australian format but still with no luck:

Sub Between2Dates()
Dim Workbook As Workbook
Dim ws As Worksheet
Dim ws1 As Worksheet
Set ws = Sheets("Master Data")
Set ws1 = Sheets("Report Generator")
'declare the variables
Dim DateBegin
Dim DateEnd
Dim rng As Range
 
'set error handler
On Error GoTo errHandler:
'stop screen flicker
Application.ScreenUpdating = False
'set the variables
Set rng = ws.Range("B4")
DateBegin = Format(ws1.Range("C1").Value, "mm/dd/yy")
DateEnd = Format(ws1.Range("C2").Value, "mm/dd/yy")
'check the dates if all is OK run the filter
If ws1.Range("C1").Value >= ws1.Range("C2").Value Then
MsgBox " Your start date is later than your end date."
Exit Sub
Else
If Not IsEmpty(DateBegin) And Not IsEmpty(DateEnd) Then
'run the filter
With rng
.AutoFilter Field:=7, Criteria1:=">=" & DateBegin, _
Operator:=xlAnd, Criteria2:="<=" & DateEnd
'copy values
CopyFilter
'show all data
Showall
End With
End If
End If
'error block
On Error GoTo 0
Exit Sub
errHandler:
MsgBox "There is no data"
Showall
'ws1.Range("B5").Select
'https://www.onlinepclearning.com/auto-filter-dates-year-month-excel-vba/
End Sub

Hopefully the fix is just as simple as the last one!

I have figured this out thankfully. I realised that the data not showing was typically due to the Transition Formula Entry being checked in either the destination workbook (Master File) where the data initially gets sent to, or in the source workbook where the data initially gets pulled back in to a hidden tab, or in the actual data viewer and report generator tab. It was in the hidden tab that I had Transition Formula Entry still selected. Once this was unchecked the macro then worked properly. Thanks again