Input box value to be found in column and entire column selected

[Eurekaonide]

Hi
I'm trying to use an input box to find a week number in row 2 of a worksheet, but it keeps coming back with either mismatch errors or with block errors. My code so far is below which looks like it finds the cell but I need it to then select the entire column and copy and paste it to remove the formulas, ready for the next input.
I set it as a variant because although the majority of weeks are number (i.e 45. on occasions they have to repeat in order to split out the months so I will have a 50a and 50b) and thought Variant would allow numbers and text.

Eventually I'd like this to loop through each visible worksheet in the workbook if possible. Rows will always be row 2.

Sub Test_InputBox()

Dim i As Variant
    i = InputBox("Enter Week Number to find")

    With ActiveSheet.Rows("2:2").Select
     Set Row = Selection.Find(What:=i, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate


ActiveCell.EntireColumn.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
cutcopypaste = False
   End With

If cell Is Nothing Then
MsgBox ("Week is not found")
End If
End Sub

 

[Eurekaonide]

Thank you so much for this it works perfectly.

I'm not sure if I need to start a new post but I need to do the same thing but instead of copy the data as values in the entire column I now need to filter like,
ActiveSheet.Range("2:2").AutoFilter Field:=2, Criteria1:="Actual"

and insert the this formula and copy it down on all the filtered cells. This also needs to loop through each worksheet.

ActiveCell.FormulaR1C1 = "=SUMIFS(Actuals!C9,Actuals!C4,R1C1,Actuals!C8,RC1)"
Selection.NumberFormat = "_-* #,##0.00_-;-* #,##0.00_-;_-* ""-""_-;_-@_-"
Selection.Copy
Range(Selection, Selection.End(xlDown).Offset(-1, 0)).Select
ActiveSheet.Paste

I keep getting the object or with block error again.

 

[Fluff]

That will need a new thread thanks.