copy a row from one excel sheet to another 1

[nubees]

In my Excel spreadsheet there is a cell which has a String called “Issued Server(s)” but I don’t know where it is, so I have to search it. After searching that, I would like to copy content of the row directly under the cell into separate sheet.

Example:

Let us assume the String “Issued Server(s)” can be found in cell A6. In this case I would like to copy content of cells A7,B7,C7, D7, E7, F7, G7, H7, I7, J7, K7, L7, M7, N7, P7, Q7, R7, S7….etc in other words I want to copy 7th row that is just under the row 6th row.

Or: Let us assume, the String “Issued Server(s) can be found in cell D2. In this case I would like to copy contents of cells A3,B3,C3, D3, E3, F3, G3, H3, I3, J3, K3, L3, M3, N3, P3, Q3, R3, S3…
In other words I want to copy 3rd row that is under 2nd row.

please help me with any sample code or website links where I can find help.

Any help is greatly appreciated.
thanks.

 

[Molby]

Once you have found the cell that contains the string use:

ActiveCell.Offset(1, 0).Rows("1:1&quot.EntireRow.Select
Selection.Copy
Sheets("Sheet2&quot.Select
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Select
Sheets("Sheet1&quot.Select

To paste the rows.

Regards,

Ian

 

[SkipVought]

Hi,

Here's a mor compact way that does NOT use Activate or Select which eats up processing time...

    Cells(1,1).EntireRow.Copy _
      Destination:=Sheets("Sheet2").Cells(1,1)

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[xlbo]

With Cells
Set fCell = .Find("Issued Server(s)", LookIn:=xlValues, lookat:=xlWhole)
If Not fCell Is Nothing Then
Cells(fCell.Row + 1, 1).EntireRow.Copy Destination:=Sheets("Sheet2&quot.Cells(fCell.Row + 1, 1)
Else
MsgBox "Not found"
End If
End With

this should find the cell and copy the row below it to the same row in sheet2

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244