VBA - Transfer data range from 1 sheet to another by row

Hi

I have this excel form where i have from column B to J some values.

What i would like to do it to be able to transfer the data from B to J from Sheet1 into Sheet2 in on row 2 from column A to I when i double click on a specific row in column A.

Ex:

If on Sheet1 Range A3, i double click in it, it will transfer copy the values from B3 to I3 and paste it on sheet2 from A2 to I2.

How can i do that?

Thanks again for your help.
Test-to-transfer.xlsx
LVL 11
Wilder1626Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Haris DulicCommented:
Attached is the requested ..
Test-to-transfer.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gowflowCommented:
I have a question for you:
do you always want to paste the values in Sheet2 on Row2 ? what if there is data in row2 the new doubleclik items will go on row 2 or row3 ?

The proposed solution will find the available row and paste the new data at the last available row.

here is the code for that:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim WS2 As Worksheet
Dim WS As Worksheet
Dim MaxRow2 As Long

Set WS = ActiveSheet
Set WS2 = Sheets("Sheet2")
MaxRow2 = WS2.Range("A" & WS.Rows.Count).End(xlUp).Row + 1

If Not Intersect(Target, WS.Range("A:A")) Is Nothing Then
    WS.Range("B" & Target.Row & ":J" & Target.Row).Copy WS2.Range("A" & MaxRow2)
End If
End Sub

Open in new window


Please chk the file attached.
gowflow
Test-to-transfer-V01.xlsm
0
Wilder1626Author Commented:
Hi gowflow

wow, this is very good.

To answer your question, yes, it should always be on row 2 only.
0
gowflowCommented:
The here it is:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim WS2 As Worksheet
Dim WS As Worksheet
Dim MaxRow2 As Long

Set WS = ActiveSheet
Set WS2 = Sheets("Sheet2")
'MaxRow2 = WS2.Range("A" & WS.Rows.Count).End(xlUp).Row + 1
MaxRow2 = 2

If Not Intersect(Target, WS.Range("A:A")) Is Nothing Then
    WS.Range("B" & Target.Row & ":J" & Target.Row).Copy WS2.Range("A" & MaxRow2)
End If
End Sub

Open in new window


and worksheet updated.
gowflow
Test-to-transfer-V01.xlsm
0
Wilder1626Author Commented:
Hi samo4fun and gowflow.
this is perfect.

Thanks for your help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.