Macro needs some modification.

[ckmoied]

Hi All, am new to VBA coding.
Following is the VBA code for excel that I am gonna use as a macro, however it generates error for the 6th line where I wanna paste the date in the new sheet. Can anyone look at this bug and suggest a solution.
My second problem is with the name of the new sheet that am creating in the line 5 and name it with the current date. However if I run this macro two to three times a day, then it generates error saying that a sheet with the current name already exists. So what I want to do is to check the names of the previous sheets, and if the current date name already exists then add "a", "b" or "c" as a distinguishing mark at the end of the name.
Also in line 6, instead of pasting on sheet1, I wanna paste my data in newly generated sheet as named above.

I hope the problem is not that trivial, but I just need a push up support.

Sub Update()
Worksheets("Sheet1").UsedRange.Copy
Workbooks.Open "c:\ckmoied\Inv_Records.xls"
Worksheets.Add.Move after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Day(Date) & Month(Date) & Year(Date)
ActiveSheet.Paste Destination:=Worksheets("Sheet1")
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub

Thanx

 

[Bozacke]

CK,

Try:

Sub Update()
Application.DisplayAlerts = False
Workbooks.Open "c:\data\Inv_Records.xls"
Worksheets.Add.Move after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Day(Date) & Month(Date) & Year(Date) & "-" & Hour(Date)
Windows("testing.xls").Activate
Worksheets("Sheet1").UsedRange.Copy
Windows("Inv_Records.xls").Activate
ActiveSheet.Paste
ActiveWorkbook.Save
'ActiveWorkbook.Close
Application.DisplayAlerts = True
End Sub


as far as re-naming the sheets you can either also enter the time in the sheet name so all sheets are unique or try the following loop:

Xsuffix = ""
mySheet = Day(Date) & Month(Date) & Year(Date) & Xsuffix
ShFound = False
For Each sh In ActiveWorkbook.Sheets
If sh.Name = mySheet Then
Xsuffix = "-A"
Worksheets.Add.Move after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = mySheet
End If
Next

But if you are going to run this a few times a day you'd have to add in a few nested loops where Xsuffix = "-B", "-C", etc..

 

[Bozacke]

Here try this, where your original sheet which contains the macro Update is called testing.xls:

Sub Update()
Application.DisplayAlerts = False
Workbooks.Open "c:\data\Inv_Records.xls"
Worksheets.Add.Move after:=Worksheets(Worksheets.Count)
DayName = Format(Now(), "ddmmyy-hhmmss")
ActiveSheet.Name = DayName
Windows("testing.xls").Activate
Worksheets("Sheet1").UsedRange.Copy
Windows("Inv_Records.xls").Activate
ActiveSheet.Paste
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.DisplayAlerts = True
End Sub


This names each new sheet with the date & time including secods, so you are sure to always have a unique sheet name.

 

[ckmoied]

Thanks Bozacke,

Its working fine now!!!