copy sheet with date

[james_arackal]

Hi everybody,

I have a sheet1 running some macros which copies data from row one and pastes to adjacent row at an interval of 15 minutes.

I'm trying to create one sheet with a macro, at the end of the day with sheet name as previous day's date and copy all the data from sheet1 to the new created sheet.

Hope I'm clear.

Thanx in advance

 

[jimboy]

Try this;

Sub new_sheet()
Sheets.Add.Name = Format(Date - 1, "dd-mm-yy")
Sheets("Sheet1").Range("A1:A" & Sheets("Sheet1").Range("A1").SpecialCells(xlCellTypeLastCell).Row).EntireRow.Copy Sheets(ActiveSheet.Name).Range("A1")
Sheets("Sheet1").Select
'Clears sheet1
'Sheets("Sheet1").Range("A1:A" & Sheets("Sheet1").Range("A1").SpecialCells(xlCellTypeLastCell).Row).EntireRow.ClearContents
End Sub

 

[james_arackal]

thanx jim its working perfect,

 

[james_arackal]

sorry to disturb you again ,

I have seen the delete code, but i need it in a different way. After copying the sheet. I want to clear contents of 3rd row of the new sheet.

Thanx in advance

 

[jimboy]

Hi,

try this;

Sub new_sheet()
Sheets.Add.Name = Format(Date - 1, "dd-mm-yy")
Sheets("Sheet1").Range("A1:A" & Sheets("Sheet1").Range("A1").SpecialCells(xlCellTypeLastCell).Row).EntireRow.Copy Sheets(ActiveSheet.Name).Range("A1")
Rows("3:3").ClearContents
Sheets("Sheet1").Select
'Clears sheet1
'Sheets("Sheet1").Range("A1:A" & Sheets("Sheet1").Range("A1").SpecialCells(xlCellTypeLastCell).Row).EntireRow.ClearContents
End Sub