Macro question

  • Thread starter Thread starter orquidea
  • Start date Start date
O

orquidea

Hi:

I am having difficulty writing a procedure which copy one row from sheet 1
in the first row available in sheet 2.
Part of the loop procedure is below.

Do
If ActiveCell.Value = "TOR" Then
ActiveCell.EntireRow.Copy
Sheets("Sheet2"). (paste in column A in the first row available)

Could anyone help me please.

Thanks,
Orquidea
 
hi
Sheets("sheet2").range("A65000").end(xlup).offset(1,0).pastespecial xlpasteall

regards
FSt1
 
I assume you are looking for all instances of TOR in a column on sheet 1 and
you want to copy those cells to the first available location on sheet 2. This
should do it all for you...

Public Sub CopyStuff()
Dim wksFrom As Worksheet
Dim wksTo As Worksheet
Dim rngFound As Range
Dim rngFoundAll As Range
Dim rngToSearch As Range
Dim strFirstAddress As String

Set wksFrom = Sheets("Sheet1") 'change sheet from
Set wksTo = Sheets("Sheet2") 'change destination sheet
Set rngToSearch = wksFrom.Columns("A") 'Change column to search
Set rngFound = rngToSearch.Find(What:="TOR", _
LookAt:=xlWhole, _
LookIn:=xlValues, _
MatchCase:=True)
If rngFound Is Nothing Then
MsgBox "TOR was not found"
Else
strFirstAddress = rngFound.Address
Set rngFoundAll = rngFound
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
rngFoundAll.EntireRow.Copy _
wksTo.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
End If
End Sub
 
Back
Top