Hi,
I wanted to copy a row from the sheet 1 to sheet 2 with a macro.
i.e. sheet 1 row a1 to f1 to sheet 2 row a6 to f6. And when macr
fires there should be a message box to appear about the selection o
row.So that i can select any of the row from the sheet 1 to sheet 2.
Any help will be highly appreciated.
thanks
nowfa

--
nowfa
-----------------------------------------------------------------------
nowfal's Profile: http://www.excelforum.com/member.php...fo&userid=1000
View this thread: http://www.excelforum.com/showthread.php?threadid=38621

Try this example

Dim sourceRange As Range
Dim destrange As Range
Set sourceRange = Sheets("Sheet1").Rows(1)
Set destrange = Sheets("Sheet2").Rows(6)
sourceRange.Copy destrange

You can use this to copy the ActiveCell.Row
Set sourceRange = ActiveCell.EntireRow

See also
http://www.rondebruin.nl/copy1.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"nowfal" wrote in message
...

Hi,
I wanted to copy a row from the sheet 1 to sheet 2 with a macro.
i.e. sheet 1 row a1 to f1 to sheet 2 row a6 to f6. And when macro
fires there should be a message box to appear about the selection of
row.So that i can select any of the row from the sheet 1 to sheet 2.
Any help will be highly appreciated.
thanks
nowfal


--
nowfal
------------------------------------------------------------------------
nowfal's Profile: http://www.excelforum.com/member.php...o&userid=10003
View this thread: http://www.excelforum.com/showthread...hreadid=386219

dim rng as Range, rng1 as Range
On error resume next
set rng = Application.InputBox("Select range to copy with mouse",Type:=8)
if not rng is nothing then
set rng1 = application.InputBox("Select destination with moust",Type:=8)
else
exit sub
End if
On Error goto 0
if not rng1 is nothing then
rng.copy destination:=rng1(1)
end if

--
Regards,
Tom Ogilvy

"nowfal" wrote in
message ...

Hi,
I wanted to copy a row from the sheet 1 to sheet 2 with a macro.
i.e. sheet 1 row a1 to f1 to sheet 2 row a6 to f6. And when macro
fires there should be a message box to appear about the selection of
row.So that i can select any of the row from the sheet 1 to sheet 2.
Any help will be highly appreciated.
thanks
nowfal


--
nowfal
------------------------------------------------------------------------
nowfal's Profile:
http://www.excelforum.com/member.php...o&userid=10003
View this thread: http://www.excelforum.com/showthread...hreadid=386219

HI,
Both of you, Mr.Ron and Mr.Tom both of the codes are working well. But
i am disturbing you again . If you help me again i am very thankful.
what i needed is in the in the input box i want a selection not by the
mouse but by specifying the row.
i mean like row 3 of first sheet to row 5 of next sheet
many thanks
with regards
nowfal


--
nowfal
------------------------------------------------------------------------
nowfal's Profile: http://www.excelforum.com/member.php...o&userid=10003
View this thread: http://www.excelforum.com/showthread...hreadid=386219

If you only want to prompt the user to enter a number

Dim num1 as Variant, num2 as Variant
num1 = InputBox("Enter the row number to copy")
num2 = InputBox("Enter the destination row")
if isnumeric(num1) and isnumeric(num2) then
Worksheets("Sheet1").Rows(num1).copy _
Destination:=Worksheets('Sheet2').rows(num2)
end if

--
Regards,
Tom Ogilvy


"nowfal" wrote in
message ...

HI,
Both of you, Mr.Ron and Mr.Tom both of the codes are working well. But
i am disturbing you again . If you help me again i am very thankful.
what i needed is in the in the input box i want a selection not by the
mouse but by specifying the row.
i mean like row 3 of first sheet to row 5 of next sheet
many thanks
with regards
nowfal


--
nowfal
------------------------------------------------------------------------
nowfal's Profile:
http://www.excelforum.com/member.php...o&userid=10003
View this thread: http://www.excelforum.com/showthread...hreadid=386219

Would it be suitable for you to place the value of the rows copied and
pasted to into a cell?


--
Losse
------------------------------------------------------------------------
Losse's Profile: http://www.excelforum.com/member.php...o&userid=24813
View this thread: http://www.excelforum.com/showthread...hreadid=386219

Hi Tom,
In your latest code some syntax error is coming on the last
two lines . If you are helping again, i have a small correction in the
requirements that only copying from the sheet 1 is varies the
destination is same always. So please if you find time let have a look
on it and help me.
thanks once again
with regards
nowfal


--
nowfal
------------------------------------------------------------------------
nowfal's Profile: http://www.excelforum.com/member.php...o&userid=10003
View this thread: http://www.excelforum.com/showthread...hreadid=386219

Hi Tom.
Your code was perfect, the sytax error appears because o
the last line you mentioned 'sheet2' instead of "sheet2" corrected i
and it works . thanks for the help. Now i am trying for little twis
that the destination row is a permanent place ie sheet 2 row(1). I
possible when you get time please consider that. Any how once agai
thanks a lot
with
regards
nowfa

--
nowfa
-----------------------------------------------------------------------
nowfal's Profile: http://www.excelforum.com/member.php...fo&userid=1000
View this thread: http://www.excelforum.com/showthread.php?threadid=38621

Hi,
I didn't get a hand from the experts, still waiting. I just repeat
the request:
the user wanted to copy one row from sheet 1 to a fixed row to the
second sheet.
Mr. Tom was given me the earlier code which is working well. i wanted
to remove the second input box and made it to a fixed say A20 of the
second sheet.
Please look into this matter.
thanks and regards
nowfal

Beow is Toms code:
Dim num1 as Variant, num2 as Variant
num1 = InputBox("Enter the row number to copy")
num2 = InputBox("Enter the destination row")
if isnumeric(num1) and isnumeric(num2) then
Worksheets("Sheet1").Rows(num1).copy _
Destination:=Worksheets("Sheet2").rows(num2)
end if


--
nowfal
------------------------------------------------------------------------
nowfal's Profile: http://www.excelforum.com/member.php...o&userid=10003
View this thread: http://www.excelforum.com/showthread...hreadid=386219

Hi Nowfal,

Try:

Dim num1 As Variant, num2 As Long

num1 = InputBox("Enter the row number to copy")
num2 = 20

If IsNumeric(num1) Then
Worksheets("Sheet1").Rows(num1).Copy _
Destination:=Worksheets("Sheet2").Rows(num2)
End If

---
Regards,
Norman



"nowfal" wrote in
message ...

Hi,
I didn't get a hand from the experts, still waiting. I just repeat
the request:
the user wanted to copy one row from sheet 1 to a fixed row to the
second sheet.
Mr. Tom was given me the earlier code which is working well. i wanted
to remove the second input box and made it to a fixed say A20 of the
second sheet.
Please look into this matter.
thanks and regards
nowfal

Beow is Toms code:
Dim num1 as Variant, num2 as Variant
num1 = InputBox("Enter the row number to copy")
num2 = InputBox("Enter the destination row")
if isnumeric(num1) and isnumeric(num2) then
Worksheets("Sheet1").Rows(num1).copy _
Destination:=Worksheets("Sheet2").rows(num2)
end if


--
nowfal
------------------------------------------------------------------------
nowfal's Profile:
http://www.excelforum.com/member.php...o&userid=10003
View this thread: http://www.excelforum.com/showthread...hreadid=386219

Thank you very much Mr.Norman Jones thats exactly what i wanted. it
works perfectly.
with regards
nowfal


--
nowfal
------------------------------------------------------------------------
nowfal's Profile: http://www.excelforum.com/member.php...o&userid=10003
View this thread: http://www.excelforum.com/showthread...hreadid=386219