VBA - Copy and Paste from one sheet to another based on drop down criteria

sdennant

New Member
Joined
Mar 17, 2020
Messages
34
Office Version
  1. 365
Platform
  1. Windows
hi there,

I was asked to repost this as a new thread. so here goes.

Basically i have multiple sheets by for the sake of time lets reduce this to two sheets.

Sheet one (Sainsbury's) has 17 columns through to Q.
Sheet two (All Orders) has 20 columns through to T

Sheet 1 (Sainsbury's) I want to copy just B, C, D, F, H, J, K, M

Paste into Sheet 2 ( All Orders)
- B is pasted into D
- C is pasted into E
- D is pasted into G
- H is pasted into F
- F is pasted into J
- J is pasted into I
- K is pasted into L
- M is pasted into P

I want this to copy as soon as the drop down in column O is marked "completed".

i also have formulas in sheet two which i dont want the paste to overwrite. i want the past to work with the formulas so the formulas continue to do their job in sheet 2.

i hope that makes sense. confused me writing it.

images below.

Sheet 1.PNG
sheet 2.PNG
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
You're a genius mate. all it needed was for me to restart excel and reopen everything and it worked beautifully. thank you very much.
You're welcome,
regards, JLG
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
It occurred to me that the reason the code did not work until you restarted your computer was because of the Application.EnableEvents = False statement having been executed without the Application.EnableEvents = True statement also having been executed to restore the event trigger. Those two statements are included in the code to prevent any other event code you might have from executing until the copy/paste action is completed. If this code should error midstream and the user does not advance the execution to the Application.EnableEvents = True statement, ;then
it leaves the even trigger disabled. So if you encounter a condition where the copy paste does not execute when you select the "Completed" option in your drop down, run the snippet below from one of your inserted code modules.

VBA Code:
Sub restoreEvents()
Application.EnableEvents = True
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,128,068
Messages
5,628,458
Members
416,318
Latest member
Mano130898

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top