Creating New sheets

[mdkinsey]

I have the following macro:

Sheets("3-13-11").Select
Sheets("3-13-11").Copy Before:=Sheets(1)
Sheets("3-13-11 (2)").Select
Sheets("3-13-11 (2)").Name = "3-20-11"
Rows("3:81").Select
Selection.EntireRow.Hidden = False
Range("B4:M81").Select
Selection.Replace What:="3-13-11", Replacement:="3-20-11", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="$13", Replacement:="$14", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Every week I have to create a new sheet with the same formulas and links as the previous one. Right now I go into the code and change the info I need to manually. How would I do this with out having to change the macro every week????

 

[GK039]

Hi

You could use an Input Box to get the date and set a Date variable to pass the value given to the input box

George

I have the following macro:

Sheets("3-13-11").Select
Sheets("3-13-11").Copy Before:=Sheets(1)
Sheets("3-13-11 (2)").Select
Sheets("3-13-11 (2)").Name = "3-20-11"
Rows("3:81").Select
Selection.EntireRow.Hidden = False
Range("B4:M81").Select
Selection.Replace What:="3-13-11", Replacement:="3-20-11", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="$13", Replacement:="$14", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Every week I have to create a new sheet with the same formulas and links as the previous one. Right now I go into the code and change the info I need to manually. How would I do this with out having to change the macro every week????

 

[mdkinsey]

I understand input box but I have little understanding of variables.

 

[GK039]

Try this to see the results

Sub sheetsname()

Dim answer As String

answer = InputBox("place your date here")

Sheets("sheet1").Name = answer

End Sub

I understand input box but I have little understanding of variables.

 

[GK039]

Sorry forgot..

to a new workbook

Try this to see the results

Sub sheetsname()

Dim answer As String

answer = InputBox("place your date here")

Sheets("sheet1").Name = answer

End Sub

 

[jbeaucaire]

Something like this:

Option Explicit

Sub NewFromTemplate()
Dim Date1 As Date
Dim Date2 As Date

Date1 = Application.InputBox("Enter date of sheet to duplicate from", "Source Sheet", "3-13-11", Type:=1)
If Date1 = 0 Then Exit Sub
Date2 = Application.InputBox("Enter date for new sheet", "New Sheet", Format(Date, "M-D-YY"), Type:=1)
If Date2 = 0 Then Exit Sub

Sheets(Format(Date1, "M-D-YY")).Copy Before:=Sheets(1)

With ActiveSheet
    .Name = Format(Date2, "M-D-YY")
    .Rows("3:81").Hidden = False
    .Range("B4:M81").Replace What:=Format(Date1, "M-D-YY"), _
        Replacement:=Format(Date2, "M-D-YY"), LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    .Replace What:="$13", Replacement:="$14", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End With

End Sub

 

[mdkinsey]

Got it. Thanks much for your help