Macro to paste special all values in a spreadsheet with specific criteria

[Don0401]

Hi, I apologize in advance if this is a simplistic question, but I've just started with VBA and am still learning. Basically I have a compilation worksheet where I am linking to data from another worksheet entitled "income statement". What I would like to do is create a macro that will search the entire worksheet and find every cell which is linked to the "income statement" worksheet and copy and then paste special (values) - and I would like the macro to do this for every cell in the worksheet that is linked to the "income statement" worksheet - the goal is for me to be able to delete the "income statement" worksheet once all the values have been pasted in to the main sheet. I recorded a macro doing this for just a few cells which I pasted below hoping I could figure out how to make the macro search for every single cell with a link to the "income statement" sheet, but as of yet I haven't been bale to figure it out. What I really need is some sort of a statement that says "search for all cells that include the reference "income statement" and then paste special-values until there are no such cells left on the main sheet. I would really appreciate any help anyone could give me with this - thank you very much. - Don


Macro I recorded:


Sub Paste_Special_Replacement()
'
' Paste_Special_Replacement Macro
' Macro recorded 12/30/2011 by Don
'

'
Cells.Find(What:="income statement", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Cells.FindNext(After:=ActiveCell).Activate
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Cells.FindNext(After:=ActiveCell).Activate
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Cells.FindNext(After:=ActiveCell).Activate
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

[Don0401]

Sorry - I forgot to post the macro I recorded on a few cells as I described in my original post - sorry - here it is:

Sub Paste_Special_Replacement()
'
' Paste_Special_Replacement Macro
' Macro recorded 12/30/2011 by Don
'

'
Cells.Find(What:="income statement", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Cells.FindNext(After:=ActiveCell).Activate
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Cells.FindNext(After:=ActiveCell).Activate
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Cells.FindNext(After:=ActiveCell).Activate
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub