Selected Answer
Maklil
I think there are 2 problems with column 1 in your code
- You used .Columns("1").,Insert.... but it should read .Columns(1).,Insert....
- You use Format(dDate, "dd/mm/yyyy") but that just makes a text version of the dDate
Also you get sheet names like Sheet1(2), from the Copy command.
From the file you attached latterly, in the revised Ive corrected those points (changes in bold). For the dates in column 1, note that I have two lines to first apply the date numeric value then (Range)..NumberFormat to set it as a date (not General format). Prior to that you'll see that the CopyOrgin is chnaged on inserting clumn 1 (to adopt fromatting for headers and borders).
Sub CreateNewSheet1()
Dim dDate As Long
Dim oNum As String
Dim lst As Long
sheet1.Copy After:=Sheets(Sheets.Count)
On Error Resume Next
With ActiveSheet
dDate = .Range("B3").Value
oNum = .Range("D3").Value
lst = .Range("A" & Rows.Count).End(xlUp).Row
.Name = "Stock " & Format(dDate, "ddmmmyyyy") ' name sheet with date
.Columns(3).Insert
.Columns(1).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
.Range("A4").Value = "Date"
.Range("D4").Value = "Order"
.Range("A5:A" & lst).Value = dDate ' add the value
.Range("A5:A" & lst).NumberFormat = "dd/mm/yyyy" ' set the cell format
.Range("D5:D" & lst).Value = oNum
.Rows("1:3").Clear
End With
End Sub
Note that I've given the new sheet a name including the date but if you add another sheet onthe same date, you'll still get a sheet name like
Sheet1(2) (without the macro crashing, thanks to the
On Error ...line), You could add logic to avoid that but I'm assuming in real life you won't run the macro more than once a day).,
Hope this works for you.
