Copying Recreating, date Stamping & re Calc

[middow]

Hi Guys

Having some major problems with this macro for me this is a huge one so here goes:

This is the Macro:

Sub Backup_All()
'
' BackupWeekly_Input Macro
'
'
Application.ScreenUpdating = False
Call BackupSummary

Dim dateStamp As String
Dim backUpSheet As Worksheet
Dim newSheet As Worksheet

Sheets("Weekly_Input").Select
dateStamp = Range("X1").Text

'Create copy of current Weekly_Input to be used as Weekly_Input
ActiveSheet.Copy After:=Sheets(2)
Set newSheet = ActiveSheet

'Rename current Weekly_Input to dateStamp ('Summary_Week' formulas will follow to new named sheet)
Sheets("Weekly_Input").Select
Set backUpSheet = ActiveSheet
backUpSheet.Name = "Weekly_Input " & dateStamp
Sheets("Weekly_Input " & dateStamp).Move After:=Sheets(3)


'Rename new Weekly_Input accordingly
newSheet.Name = "Weekly_Input "
Sheets("Weekly_Input ").Select
Sheets("Weekly_Input ").Move After:=Sheets(1)

Sheets("Summary").Select
Range("A1").Select

'Replace all formulas in "Summary" looking for the backup to look at the Weekly_Input
ActiveSheet.UsedRange.Select
Selection.Replace What:="'Weekly_Input" & dateStamp & "'!", Replacement:="Weekly_Input !", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Range("A1").Select

Sheets("Weekly_Input " & dateStamp).Select
'Replace all formulas in "Summary_Week" looking for the new Weekly_Input to look at the backup
ActiveSheet.UsedRange.Select
Selection.Replace What:="Weekly_Input !", Replacement:="Weekly_Input " & dateStamp & "!", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Range("A1").Select

Sheets("Weekly_Input").Select
Range("G1").Select

Application.ScreenUpdating = True

MsgBox "Input and summary backed-up successfully."

End Sub

Sub BackupSummary()
'
' BackupSummary Macro
'
'
Dim dateStamp As String
Dim newSheet As Worksheet

Sheets("Weekly_Input").Select
dateStamp = Range("X1").Text

Sheets("Summary").Copy After:=Sheets(2)
Set newSheet = ActiveSheet
newSheet.Name = "Summary_Week" & dateStamp

Range("A1").Select
End Sub

I Want this formula to copy both the Summary & Weekly_Input pages, re name them with the date stamp in "X1" then re calculate the formulas linked on same worksheets e.g. Summary_week "20" & Weekly_Input "20"

i can get it all to copy over fine but i am having the main problem with the re calculation part and for the life of me can't figure it out.

The line I'm super stuck on is :

Sheets("Weekly_Input " & dateStamp).Select
'Replace all formulas in "Summary_Week" looking for the new Weekly_Input to look at the backup
ActiveSheet.UsedRange.Select
Selection.Replace What:="Weekly_Input !", Replacement:="Weekly_Input " & dateStamp & "!", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Range("A1").Select

I have Attached The Actually Spread Sheet for you all with Data Removed so have fun editing and saving my bacon

Thank you for any help in advance I'm really pulling my hair out on this one!

[middow]

Any ideas?