Hi. Sorry if this question has been posted before, but I can't seem to
find any solution for this. I'm trying to create a macro (I think) that
would allow me to copy a whole worksheet and paste it to another new
worksheet in Excel. The problem is that everyday, there are new data
added to the worksheet with the current date. Hence I want to copy the
data from the current date and paste it to a new worksheet. Also there
are 49 databases with the new data each date and it needs to be copied
all into one worksheet. Any help would be appreciated.


--
alucard
------------------------------------------------------------------------
alucard's Profile: http://www.excelforum.com/member.php...o&userid=27764
View this thread: http://www.excelforum.com/showthread...hreadid=472702

Hi alucard,

Try this code below:

Sub NewSheet()
Dim shtName As String
Dim wSht As Worksheet

'Point the Cells(1,1).Value to dates you want to archive or type on Cell A1
"=Now()" for sample
'then test macro
'For combining all the sheets "UsedRange" and put it on a particular
sheetname.

shtName = Format(Cells(1, 1).Value, "ddmmmyy")
For Each wSht In Worksheets
If wSht.Name = shtName Then
MsgBox "Sorry! This sheet already exists."
Exit Sub
End If
Next wSht
Sheets.Add.Name = shtName
Sheets(shtName).Move Befo=Sheets(Sheets.Count)
Sheets("Sheet1").UsedRange.Copy Sheets(shtName).Range("A1")
Sheets("Sheet1").UsedRange.Copy

Sheets(shtName).Select
ActiveSheet.UsedRange.Select
Selection.PasteSpecial Paste:=xlPasteValues
Cells.Select
Cells.EntireColumn.AutoFit

Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
Range("A1").Select
End Sub


For combining all the sheets refer to weblink below.

http://www.ozgrid.com/forum/showthread.php?t=17158

"alucard" wrote:


Hi. Sorry if this question has been posted before, but I can't seem to
find any solution for this. I'm trying to create a macro (I think) that
would allow me to copy a whole worksheet and paste it to another new
worksheet in Excel. The problem is that everyday, there are new data
added to the worksheet with the current date. Hence I want to copy the
data from the current date and paste it to a new worksheet. Also there
are 49 databases with the new data each date and it needs to be copied
all into one worksheet. Any help would be appreciated.


--
alucard
------------------------------------------------------------------------
alucard's Profile: http://www.excelforum.com/member.php...o&userid=27764
View this thread: http://www.excelforum.com/showthread...hreadid=472702