Macro Problem

[alucard]

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.

[Mel Arquiza]

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 Before:=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
>
>