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

"orquidea" wrote:

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

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
--
HTH...

Jim Thomlinson


"FSt1" wrote:

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

regards
FSt1

"orquidea" wrote:

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

Thanks for your help

"FSt1" wrote:

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

regards
FSt1

"orquidea" wrote:

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

Thanks for reading my mind. The macro below is exactly what I needed.

"Jim Thomlinson" wrote:

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
--
HTH...

Jim Thomlinson


"FSt1" wrote:

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

regards
FSt1

"orquidea" wrote:

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