row selecting
 

Hi
I have a matrix (1000 * 60), and column H is numbered 1 to 1000 irregularly.
I want to try copy and extract row for example number700 to sheet2 when i
select number of that row.in fact when i enter number of any rows, a formula
or macro copy entire of its row to sheet2.
Thank's and regards

row selecting
 

Try this macro... read the rows marked with '

Sub CopyToSheet()

'You will need to run this after entering the value in cell A1 of Sheet1
'Row with number in Col H equal to the number in cell A1 of sheet1
'will be copied to row 1 of Sheet2

'Cell Sheet1!A1 must contain a number otherwise this macro will fail

Dim myrange As Range
Dim LastRow As Long
Dim c
Dim i As Integer
Sheets("Sheet1").Activate
i = Cells(1, 1).Value
LastRow = Cells(Rows.Count, "H").End(xlUp).Row
Set myrange = Range("H1:H" & LastRow)
For Each c In myrange
If c.Value = i Then
Cells(i, 1).EntireRow.Copy
Sheets("Sheet2").Activate
Cells(1, 1).Select
ActiveSheet.Paste
MsgBox "Row " & i & " copied to Sheet1 Row 1"
Exit For
End If
Next c
End Sub


"climate" wrote:

Hi
I have a matrix (1000 * 60), and column H is numbered 1 to 1000 irregularly.
I want to try copy and extract row for example number700 to sheet2 when i
select number of that row.in fact when i enter number of any rows, a formula
or macro copy entire of its row to sheet2.
Thank's and regards

row selecting
 

If I understand you correctly, you want to specify a number that can be
found in Column H and copy its entire row from Sheet1 (you didn't specify
this sheet name, so I guessed it) to Sheet2. If so, give this a try...

Sub CopyRow()
Dim Answer As String
Dim LastRowOnSheet2 As Long
With Worksheets("Sheet2")
LastRowOnSheet2 = .Cells(.Rows.Count, "A").End(xlUp).Row
If LastRowOnSheet2 = 1 And .Cells(1, "A").Value = "" Then
LastRowOnSheet2 = 0
End If
Answer = InputBox("Find which number in Row H and copy it?")
Worksheets("Sheet1").Columns("H").Find(Answer).Ent ireRow. _
Copy .Range("A" & (LastRowOnSheet2 + 1))
End With
End Sub

--
Rick (MVP - Excel)


"climate" wrote in message
...
Hi
I have a matrix (1000 * 60), and column H is numbered 1 to 1000
irregularly.
I want to try copy and extract row for example number700 to sheet2 when i
select number of that row.in fact when i enter number of any rows, a
formula
or macro copy entire of its row to sheet2.
Thank's and regards

row selecting
 

Hi Rick
Your macro works correctly, thank's for your help.
best regards

"Rick Rothstein" wrote:

If I understand you correctly, you want to specify a number that can be
found in Column H and copy its entire row from Sheet1 (you didn't specify
this sheet name, so I guessed it) to Sheet2. If so, give this a try...

Sub CopyRow()
Dim Answer As String
Dim LastRowOnSheet2 As Long
With Worksheets("Sheet2")
LastRowOnSheet2 = .Cells(.Rows.Count, "A").End(xlUp).Row
If LastRowOnSheet2 = 1 And .Cells(1, "A").Value = "" Then
LastRowOnSheet2 = 0
End If
Answer = InputBox("Find which number in Row H and copy it?")
Worksheets("Sheet1").Columns("H").Find(Answer).Ent ireRow. _
Copy .Range("A" & (LastRowOnSheet2 + 1))
End With
End Sub

--
Rick (MVP - Excel)


"climate" wrote in message
...
Hi
I have a matrix (1000 * 60), and column H is numbered 1 to 1000
irregularly.
I want to try copy and extract row for example number700 to sheet2 when i
select number of that row.in fact when i enter number of any rows, a
formula
or macro copy entire of its row to sheet2.
Thank's and regards