Solved: How to call an action based on input

[agnesz]

:banghead:

I have an Excel spreadsheet which needs to have 4 columns copied and pasted each week from the previous week into the next. I would like to create a macro which first asks the user which week they want to copy formulas from, i.e. week 2, then the macro would go to the 4 columns designated for week 2 (in this case range k6:n100), copy formulas into week 3 columns (range o7:r100), AND THEN go back and paste values into k6:n100, to maintain the history. Any help would be greatly, immensely appreciated!

Additionally, there is a Last Week Average Retail $ column which also would need to get updated after user inputs their value. So again, if user enters week 2 as the value, that means the Last Week Average Retail $ will now need to reflect a formula calculating week 3's retail, in this case the formula would change from - n6/k6 to r6/o6, and then need to be copied down throughout the entire range of f6:f100.

Hope this makes sense... Again thank you for all your help in advance.
:mkay I'm saddened by my lack of skills in this department.
</IMG></IMG>

[Bob Phillips]

Public Sub Test()
Dim mpWeekNum As Long
Dim mpLast As Long

On Error Resume Next
mpWeekNum = InputBox("Which week number")
On Error GoTo 0
If mpWeekNum > 0 Then

'add some tests to test weeknum validity e.g <52
With ActiveSheet
mpLast = mpWeekNum * 4
.Cells(6, 3 + mpLast).Resize(94, 4).Copy .Cells(6, 7 + mpLast)
.Cells(6, 3 + mpLast).Resize(94, 4).Value = .Cells(6, 3 + mpLast).Resize(94, 4).Value
.Cells(6, "F").Resize(94).FormulaR1C1 = "=RC[" & -3 + mpLast & "]/RC" & 6 + mpLast
End With
End If

End Sub

[agnesz]

This is great!Thank you... one more question...
I need to make an adjustment to

If mpWeekNum > 0 Then.... to ....
If mpWeekNum < 5 Then

And add a msgbox to say "Please enter value less than 5", when a value higher than 4 is entered.

Where would I put that code?
Thanks again

[Bob Phillips]

Public Sub Test()
Dim mpWeekNum As Long
Dim mpLast As Long

On Error Resume Next
Do
mpWeekNum = InputBox("Enter a week number 1-5")
If mpWeekNum = 0 Then Exit Sub
Loop Until mpWeekNum >= 0 And mpWeekNum <= 5
On Error GoTo 0

With ActiveSheet
mpLast = mpWeekNum * 4
.Cells(6, 3 + mpLast).Resize(94, 4).Copy .Cells(6, 7 + mpLast)
.Cells(6, 3 + mpLast).Resize(94, 4).Value = .Cells(6, 3 + mpLast).Resize(94, 4).Value
.Cells(6, "F").Resize(94).FormulaR1C1 = "=RC[" & -3 + mpLast & "]/RC" & 6 + mpLast
End With

End Sub

[agnesz]

BRILLIANT!
THANK YOU