Urgent: macro to capture and print contents of cells that containing certain word in a workbook

  • Thread starter Thread starter paul
  • Start date Start date
P

paul

Dear All

Could anyone tell me how to write a Excel macro that capture and print
contents of all the cells that contain the word "temp" in the workbook.

Many thanks.

Please also reply to (e-mail address removed) (office email address)
 
Paul
I imagine that you want more than just the contents of all those cells.
Maybe the sheet name and maybe the cell address?
But giving you just what you asked for, the following macro will do
that.
I'm also sending you direct, a small file that contains this macro.
HTH Otto
Sub FindTemp()
Dim ws As Worksheet
Dim Target As Range
Dim AfterCell As Range
Dim FirstFound As Range
Dim Dest As Range
With Sheets("Destination")
Set Dest = .[A1]
End With
For Each ws In Worksheets
If ws.Name = "Destination" Then GoTo Nextws
With ws
Set AfterCell = .UsedRange(.UsedRange.Count)
Set Target = .UsedRange.Find(What:="temp", _
After:=AfterCell, LookIn:=xlValues, LookAt:=xlPart)
If Target Is Nothing Then GoTo Nextws
Set FirstFound = .Range(Target.Address)
Do
Set AfterCell = .Range(Target.Address)
Dest.Value = Target.Value
Set Dest = Dest.Offset(1)
Set Target = .UsedRange.Find(What:="temp", _
After:=AfterCell, LookIn:=xlValues, LookAt:=xlPart)
Loop Until Target.Address = FirstFound.Address
End With
Nextws:
Next ws
With Sheets("Destination")
.Range("A1", .Range("A" & Rows.Count).End(xlUp)).PrintOut
End With
End Sub
 
Back
Top