Automatically copy the values from the active row from sheet [Solved/Closed]

BeanP 2 Posts Tuesday November 29, 2016Registration date December 4, 2016 Last seen - Nov 29, 2016 at 05:45 PM - Latest reply: vcoolio 1169 Posts Thursday July 24, 2014Registration dateModeratorStatus June 10, 2018 Last seen
- Dec 4, 2016 at 09:13 PM
Hello,
Struggling already for a couple of days.... so a help would be more than welcomed

Need to write a macro in Excel which would:
automatically copy the values from the active row from sheet "Inquires" into worksheet "Orders" when "Ordered" is entered in the column B of the active row

Can that be solved ?
See more 

6 replies

Best answer
vcoolio 1169 Posts Thursday July 24, 2014Registration dateModeratorStatus June 10, 2018 Last seen - Nov 30, 2016 at 07:58 PM
1
Thank you
Hello BeanP,

See if the following code works for you:-


Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False

If Intersect(Target, Columns("B:B")) Is Nothing Then Exit Sub
If Target.Value = "Ordered" Then
    Target.EntireRow.Copy Sheets("Orders").Range("A" & Rows.Count).End(3)(2)
End If

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub


The code is a Worksheet_Change event and needs to be placed into the worksheet module. To do this, right click on the "Inquires" sheet tab, select "view code" from the menu that appears and, in the big white field that then appears, paste the above code.

Now, every time that you enter "Ordered" in any cell in Column B and then click away (or press enter or down arrow), the code will copy the entire relevant row of data to the "Orders" sheet.

I hope that this helps.

Cheerio,
vcoolio.

Thank you, vcoolio 1

Something to say? Add comment

CCM has helped 1870 users this month

0
Thank you
Hello Vcoolio,
Thank you!!!
You are a fantastic man or woman !!!

It IS working !!!

Thank you very very much....

It`s not so important.... but what should I change to copy only values not formats ?

Many many thnx
Peter
Ambucias 52537 Posts Monday February 1, 2010Registration date June 19, 2018 Last seen - Dec 1, 2016 at 04:12 PM
man or woman, we all have some of both but vcoolio is Australian.
vcoolio 1169 Posts Thursday July 24, 2014Registration dateModeratorStatus June 10, 2018 Last seen - Dec 1, 2016 at 06:43 PM
0
Thank you
Hello Peter,

To copy/paste values only, change line 7 in the code to:-

Target.EntireRow.Copy
    Sheets("Orders").Range("A" & Rows.Count).End(3)(2).PasteSpecial xlPasteValues


Thanks for the appreciation, Peter. I'm glad that I was able to help.
Good luck with it all.

Cheerio,
vcoolio.
BeanP 2 Posts Tuesday November 29, 2016Registration date December 4, 2016 Last seen - Dec 4, 2016 at 05:16 PM
0
Thank you
Thank you very much VCoolio,

It`s working fantastically !!!
So, if you would visit Budapest (Hungary) in the future, let me know through this platform ..... own you a drink
:)

With kind regards
Peter
vcoolio 1169 Posts Thursday July 24, 2014Registration dateModeratorStatus June 10, 2018 Last seen - Dec 4, 2016 at 09:13 PM
0
Thank you
Hello Peter,

That's great! I'm glad that we've been able to help you solve your query.

As for the drink. My son currently lives in Berlin and before too long my wife and I hope to pay him a visit. So, you never know..........

Although I don't think that I could handle that palinka too well!

Cheerio,
vcoolio.