CODEWITHSUNDEEP
vbaexcel
Elias A. Stern-Rodriguez
Elias A. Stern-Rodriguez

Reputation: 19

Excel not keeping values as dates but changes them to numbers

This code is supposed to copy the sales sheet from a sales template, paste this template onto a new sheet, check column BI for a date, and if there is a date, to delete the row containing the date and move it to the new sheet. everything seems to be working except that excel is changing the formatting of column BI from a date to a string of numbers. does anybody know why this is? thanks so much, elias

Sheets("Sales").Select
Sheets("Salestemplate").visible = True
Sheets.Add After:=ActiveSheet
ActiveSheet.Select
ActiveSheet.Name = "Last Month's Sales"
Sheets("Salestemplate").Select
Cells.Select
Range("AI1").Activate
Selection.Copy
Sheets("Last Month's Sales").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Sales").Select
Range("BI8").ClearContents
Columns("BI:BI").Select
Application.CutCopyMode = False
Columns("BI:BI").Select
Selection.SpecialCells(xlCellTypeConstants).EntireRow.Select
Selection.Copy
Sheets("Last Month's Sales").Select
Range("A9").PasteSpecial Paste:=xlPasteValues
Sheets("Sales").Select
Columns("BI:BI").Select
Selection.SpecialCells(xlCellTypeConstants).EntireRow.Delete
Range("B8").Value = DTA

Upvotes: 1

Views: 47

Answers (1)

MatthewD
MatthewD

Reputation: 6761

Try formatting the columns that have a date.

Sheets("Sales").Range("A:A").NumberFormat = "yyyy-mm-dd"
Sheets("Sales").Range("E:E").NumberFormat = "yyyy-mm-dd"

If you have a header row and you want to not format that you can define start and end rows in your range

Dim lRow as Long
lRow = ws.UsedRange.Rows.count

Sheets("Sales").Range("A2:A" & lRow).NumberFormat = "yyyy-mm-dd"
Sheets("Sales").Range("E2:E" & lRow).NumberFormat = "yyyy-mm-dd"

Upvotes: 1

Related Questions