R
RJG
I found the following code on this board. it searches for a word in a
set column on each worksheet and then copies each row to a new
location. Could anybody suggest how to make the following changes
please.
I need to change two minor items in the code;-
Firstly it starts to outputs to sheet3 A2 and i would like it to start
to output from C17.
Secondly each time the macro is run it adds to the bottom of the
previous run, befor it starts i would like it to delete anything on
sheet3 between C17 and C37.
I would also like to thank the original authorfor a great piece of
code.
Private Sub CommandButton1_Click()
Dim sAdd As String, v As Variant
Dim sh As Worksheet, rng As Range
Dim rng1 As Range, i As Long
v = Array("Sheet1", "Sheet2")
For i = LBound(v) To UBound(v)
Set sh = Worksheets(v(i))
Set rng = sh.Columns(3)
Set rng1 = rng.Find(ComboBox1)
If Not rng1 Is Nothing Then
sAdd = rng1.Address
Do
rng1.EntireRow.Copy Destination:= _
Worksheets("Sheet3").Cells(Rows.Count, 1).End(xlUp)(2)
Set rng1 = rng.FindNext(rng1)
Loop While rng1.Address <> sAdd
End If
Next
End Sub
set column on each worksheet and then copies each row to a new
location. Could anybody suggest how to make the following changes
please.
I need to change two minor items in the code;-
Firstly it starts to outputs to sheet3 A2 and i would like it to start
to output from C17.
Secondly each time the macro is run it adds to the bottom of the
previous run, befor it starts i would like it to delete anything on
sheet3 between C17 and C37.
I would also like to thank the original authorfor a great piece of
code.
Private Sub CommandButton1_Click()
Dim sAdd As String, v As Variant
Dim sh As Worksheet, rng As Range
Dim rng1 As Range, i As Long
v = Array("Sheet1", "Sheet2")
For i = LBound(v) To UBound(v)
Set sh = Worksheets(v(i))
Set rng = sh.Columns(3)
Set rng1 = rng.Find(ComboBox1)
If Not rng1 Is Nothing Then
sAdd = rng1.Address
Do
rng1.EntireRow.Copy Destination:= _
Worksheets("Sheet3").Cells(Rows.Count, 1).End(xlUp)(2)
Set rng1 = rng.FindNext(rng1)
Loop While rng1.Address <> sAdd
End If
Next
End Sub