Find and replace with excel macros

Hi there, yes I have done that but it does not store any code except the copy past special values. So although whilst recording the macros I copy the cell, go to the tab and search for it (and find it) and then I copy past special values, what the macros records is me going to the cell it finds the first date in (C22) and copying and pasting it. It does not store any searching. I have pasted the macros below (it does other stuff as well which works fine so I have just copied the bit in question):

ActiveWindow.LargeScroll ToRight:=-1
Sheets("Input Sheet").Select
Range("B8").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Calculation").Select
Range("C22").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

It should have given you search code too. Wonder why it did not. Here is the break down of your code

'you scolled the window
ActiveWindow.LargeScroll ToRight:=-1

' you selected this shee
Sheets("Input Sheet").Select

'you selected this cell
Range("B8").Select

'internal excel prep for copy
Application.CutCopyMode = False
'actual copy
Selection.Copy

'this sheet is selected
Sheets("Calculation").Select

'this cell on calc sheet is select
Range("C22").Select

'from last selected cell, till the last used cell on rights, the cells are selected
Range(Selection, Selection.End(xlToRight)).Select

'from last selected range, till the last used cell on rights, the cells are selected
Range(Selection, Selection.End(xlToRight)).Select

'internal excel prep for copy
Application.CutCopyMode = False
'actual copy
Selection.Copy

'actual paste as values
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False



How ever, at the best this is only a template. I think this appoach would be a tad better

Sub doFindAndPasteSpecial()

   Dim sInputSheet            As String
   Dim rngInputRange          As Range
   Dim sOutputSheet           As String
   Dim rngSearchRange         As Range
   Dim rngOutputRange         As Range
   
   
   sInputSheet = "Input Sheet"   'name of the input sheet
   sOutputSheet = "Calculation"  'name of the output/paste sheet
   
   'this is the cell where the value to be search is always
   Set rngInputRange = Sheets(sInputSheet).Range(Cells(8, "B"))
   
   'if the lookup cell is blank, then exit sub
   If (rngInputRange.Value = vbNullString) Then Exit Sub
   
   'setting the range to be searched on output sheet
   Set rngSearchRange = Sheets(sOutputSheet).Range("C1:C" & Rows.Count)
   
   With rngSearchRange
      Set rngOutputRange = Nothing
      'attempting to find the cell that has the value
      Set rngOutputRange = .Find(What:=sInputRange.Value, _
                                 After:=.Cells(1, 1), _
                                 Lookat:=xlWhole, _
                                 SearchDirection:=xlNext, _
                                 MatchCase:=False)
      
      'no dice. getting out of routine
      If (rngOutputRange Is Nothing) Then Exit Sub
      
   End With
   
   'EUREKA!!!!!  FOUND IT
   With Sheets(sOutputSheet).Rows(rngOutputRange.Row)
      Application.CutCopyMode = False
      .Copy 'copying the row
      'pasting the row back
      .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
      Application.CutCopyMode = False
   End With
End Sub