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

sdennant

New Member
Joined
Mar 17, 2020
Messages
41
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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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
 
Upvote 0
Hi, I tried to apply this code to my workbook, where i need to return all matches from one workbook to another. but its not working.

see code below:

Private Sub CommandButton1_Click()

'1 declare variables
'2 clear old search results
'3 find records that match cell c1 from account dashboard sheet

If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
If Not Intersect(Target, Range("c:c")) Is Nothing Then
Dim sh1 As Worksheet, sh2 As Worksheet, rw As Long
Set sh1 = Sheets("Account_Dashboard")
Set sh2 = Sheets("Survey Data Lookup")

Name = Sheets("account_dashboard").Range("c1").Value
If Target.Value = Name Then
rw = sh2.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row + 1
With sh2

Set fn = sh2.Range("A:A").Find(Target.Offset(, -74).Value, , xlValues, xlWhole)
If Not fn Is Nothing Then
.Cells(rw, 2) = sh1.Range("l" & Target.Row).Value 'account email
.Cells(rw, 3) = sh1.Range("r" & Target.Row).Value 'role in company
.Cells(rw, 4) = sh1.Range("cs" & Target.Row).Value 'survey type
.Cells(rw, 5) = sh1.Range("CB" & Target.Row).Value 'division
.Cells(rw, 6) = sh1.Range("cc" & Target.Row).Value 'sub-division
.Cells(rw, 7) = sh1.Range("cl" & Target.Row).Value 'wrk contact name
.Cells(rw, 8) = sh1.Range("A" & Target.Row).Value 'survey date
End With
End If
End If

Application.EnableEvents = True
End Sub





A picture of the workbook is below:
1652996168862.png
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

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
Back
Top