Add week ending date to newly created and renamed sheet?

[Immatoity]

Hiya

I haven't used VBA properly for 2 years or so now( used to use it daily)..

I have a very simple problem that can be solved 2 ways, neither of which I know how to do lol

I have a macro that copies a sheet and forces user to rename it, which works fine (its a weekly sheet)

On the sheet is cell C3 which has the "Week Ending Date".

I have tried to

a) insert a formula that looks up the previous week and adds 7 ..BUT..the problem I have is that the formula in VBA specifies the sheet name..this will work once, but next time macro used it will still look at this "old" sheet rather than the previous weeks if that makes sense (ie always goes back to sheet "we310508" and adds 7 days to that date)

b) I have managed to get an input box up asking user to enter date in correct format in C3.. they can type in the date in this input box but it doesnt populate anything? I want what they type in the input box to populate cell c3?

sorry, easy stuff I know, but been a while lol

this is my amateurish code so far

[VBA]
Sheets.Add
ActiveSheet.Name = InputBox("Give name.")
ActiveSheet.Next.Select
Range("A1:U49").Select
Selection.Copy
ActiveSheet.Previous.Select
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Columns("C:C").Select
Columns("C:C").EntireColumn.AutoFit
Range("C3").Select
Selection.Delete
Value = UserEntry = InputBox("Please enter week end date in dd/mm/yy format.")[/VBA]

[Bob Phillips]

[vba]

Dim sh As Worksheet
Dim this As Worksheet
Dim inVal As Date
Set this = ActiveSheet
Set sh = Worksheets.Add(after:=Worksheets(Worksheets.Count))
sh.Name = InputBox("Give name.")
this.Range("A1:U49").Copy sh.Range("A1")
sh.Columns("C:C").AutoFit
inVal = CDate(InputBox("Please enter week end date in dd/mm/yy format."))
sh.Range("C3").Value = inVal
[/vba]

[mikerickson]

Perhaps this would work.

Dim oldSheet As Worksheet, newSheet As Worksheet
Dim userInput As String

Set oldSheet = ActiveSheet

With ThisWorkbook
    oldSheet.Copy after:=.Sheets(.Sheets.Count)
    Set newSheet = ActiveSheet
End With

userInput = Application.InputBox("New Sheet's Name?", Default:=newSheet.Name, Type:=2)
If userInput = "False" Then Exit Sub: Rem cancel pressed

With newSheet
    .Name = userInput
    .Range("c3").Value = Format(7 + oldSheet.Range("c3").Value, "mm/dd/yy")
    .Columns("C:C").EntireColumn.AutoFit
End With

[mdmackillop]

If you are normally dealing with the current week, you could add a default value
[vba]WkEnd = Date + (7 - Day(Date))
inval = CDate(InputBox("Please enter week end date in dd/mm/yy format.", , WkEnd))

[/vba]