VBA to copy data over in date format

[ItalianPlatinum]

Hello - I have my data that will present a date in 20200924 format. I am using the below code to transfer the data over. but i need the data after transferred over to be in 9/24/2020 format. Is there a modification I can do to the below to accomplish it or do i need to rewrite that all?

With Sheets("Compare")
  rws = .Range("H13:H13").End(xlDown).row - 12
  Sheets("D").Range("N2").Resize(rws, 1).Value = .Range("H13").Resize(rws).Value
End With

 

[MARK858]

Maybe test the below on a copy of your data

    With Sheets("Compare")
        rws = .Range("H13").End(xlDown).Row - 12
        
        With Sheets("D").Range("N2").Resize(rws)
            .Value = .Range("H13").Resize(rws).Value
            
            .TextToColumns Destination:=Range("N2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 5), TrailingMinusNumbers:=True
            
            .NumberFormat = "mm/dd/yyyy"
        End With
        
    End With

 

[ItalianPlatinum]

I

Maybe test the below on a copy of your data

    With Sheets("Compare")
        rws = .Range("H13").End(xlDown).Row - 12
       
        With Sheets("D").Range("N2").Resize(rws)
            .Value = .Range("H13").Resize(rws).Value
           
            .TextToColumns Destination:=Range("N2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 5), TrailingMinusNumbers:=True
           
            .NumberFormat = "mm/dd/yyyy"
        End With
       
    End With

I am getting error at the texttcolumn blurb

 

[MARK858]

What do you get with

    With Sheets("Compare")
        rws = .Range("H13").End(xlDown).Row - 12
        
      
            Sheets("D").Range("N2").Resize(rws).Value = .Range("H13").Resize(rws).Value
            
            Sheets("D").Range("N2").Resize(rws).TextToColumns Destination:=Range("N2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 5), TrailingMinusNumbers:=True
            
            Sheets("D").Range("N2").Resize(rws).NumberFormat = "mm/dd/yyyy"
      
        
    End With

 

[ItalianPlatinum]

What do you get with

    With Sheets("Compare")
        rws = .Range("H13").End(xlDown).Row - 12
       
     
            Sheets("D").Range("N2").Resize(rws).Value = .Range("H13").Resize(rws).Value
           
            Sheets("D").Range("N2").Resize(rws).TextToColumns Destination:=Range("N2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 5), TrailingMinusNumbers:=True
           
            Sheets("D").Range("N2").Resize(rws).NumberFormat = "mm/dd/yyyy"
     
       
    End With

Nice! Works so far