“Dates” set as General, should be set to DATES

[Unicode]

I have my VBA script working correctly, however, I do not understand why the dates on column "C" are set and changed to "General" when they should remain as Dates. If my dates do not land on Sheet 2 as Dates then formula on column "E" will not display Days Outstanding as sheet should display with formulas.




VBA Script below:


Sub deleteRowswithSelectedText4()


Dim lr As Long
Dim i As Long
Dim szRange As String
Dim rng As Range


lr = Cells(Rows.Count, 1).End(xlUp).Row


For i = lr - 1 To 2 Step -1
If Cells(i, "B") = "LLC" Then
Cells(i, "B").EntireRow.Delete
End If
Next i




szRange = "A118:D300"


Worksheet("Sheet1").Range("A16:B300,J16:J300,R16:R300").Copy_
Destination:=Worksheets("Sheet2").Range("A118:D300")




Worksheets("Sheet2").Range("C118:C300").NumberFormat = "m/d/yy"




End Sub

 

[DanteAmor]

Maybe when you copy the paste with general format, try the following
change this

[COLOR=#333333]Worksheet("Sheet1").Range("A16:B300,J16:J300,R16:R300").Copy_ [/COLOR]
[COLOR=#333333]Destination:=Worksheets("Sheet2").Range("A118:D300")
[/COLOR]

by

[COLOR=#333333]Worksheet("Sheet1").Range("A16:B300,J16:J300,R16:R300").Copy [/COLOR]
[COLOR=#333333]Destination:=Worksheets("Sheet2").Range("A118").pastespecial xlpastevalues[/COLOR]

 

[Unicode]

Hello, your VBA code works, however, next column "E" has the following Formula.
After VBA runs, next column with formula did not update, it remains "0".

=IF(TODAY()>C120,TODAY()-C120,0)

Maybe when you copy the paste with general format, try the following
change this

[COLOR=#333333]Worksheet("Sheet1").Range("A16:B300,J16:J300,R16:R300").Copy_ [/COLOR]
[COLOR=#333333]Destination:=Worksheets("Sheet2").Range("A118:D300")
[/COLOR]

by

[COLOR=#333333]Worksheet("Sheet1").Range("A16:B300,J16:J300,R16:R300").Copy [/COLOR]
[COLOR=#333333]Destination:=Worksheets("Sheet2").Range("A118").pastespecial xlpastevalues[/COLOR]

 

[DanteAmor]

But is it correct to paste values?
That is, you do not need to copy formulas, only values?


Before executing the macro, change the format of column C to date.

 

[Unicode]

I just noticed that Sheet1 is set to "General", after copy paste into Sheet2, it remains set as General not Date.

But is it correct to paste values?
That is, you do not need to copy formulas, only values?


Before executing the macro, change the format of column C to date.

 

[Unicode]

There is no way for me to change Sheet1 to "Date".

The formulas are only being used with right side columns on Sheet2, such as the following:


Column E Column F Column G Column H Column I Column J Column K
[TABLE="width: 686"]
<tbody>[TR]
[TD="class: xl68, width: 103"]DAYS OUTSTANDING[/TD]
[TD="class: xl67, width: 88"] NOT DUE [/TD]
[TD="class: xl67, width: 83"] 0-30 DAYS [/TD]
[TD="class: xl67, width: 77"]31-60 DAYS[/TD]
[TD="class: xl67, width: 77"]61-90 DAYS[/TD]
[TD="class: xl67, width: 135"]>90 DAYS[/TD]
[TD="class: xl67, width: 123"]GRAND TOTAL[/TD]
[/TR]
</tbody>[/TABLE]

 

[DanteAmor]

But I mean column C