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
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
You cannot paste a value into a cell with a formula without destroying the formula..
 

sdennant

New Member
Joined
Mar 17, 2020
Messages
34
Office Version
  1. 365
Platform
  1. Windows
The cells I want to paste won’t be in the same ones that the formulas are. What I mean is I don’t want to copy a whole row because then that will ruin the formulas in the destination row.

I want to copy from a row in sheet 1 but only copy individual cell content of that row so the formulas in sheet 2 still work when pasted over. For now, don’t think about the formulas if they will cause an issue. It’s the principle I hope to get working.
 

sdennant

New Member
Joined
Mar 17, 2020
Messages
34
Office Version
  1. 365
Platform
  1. Windows
Further to add, imagine I have formulas in B sheet 2 but want to copy A and C from sheet 1 over leaving formula in b intact. That’s basically what I hope to get working. Copy specific cells in a target row from one sheet to another.

I don’t think I can upload the actual spreadsheet which would help explain further
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

You did not specify where on sheet 2 you wanted the data to be pasted other that the column, but I assumed that you would want to find the row with the same value in column A and paste to that row. This is Change Event code and will run when any change is made on sheet 1 but will only execute when the change occurs in column O, at which point it will evaluate if the changed cell equals "Completed". If the changed cell equals "Completed" then it will copy the data to sheet 2 as specified. Install the code into the sheet SAINSBURY'S code module.

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
If Not Intersect(Target, Range("O:O")) Is Nothing Then
Dim sh1 As Worksheet, sh2 As Worksheet, fn As Range
Set sh1 = Sheets("SAINSBURY'S")
Set sh2 = Sheets("ALL ORDERS")
    If Target.Value = "Completed" Then
        Set fn = sh2.Range("A:A").Find(Target.Offset(, -14).Value, , xlValues, xlWhole)
            If Not fn Is Nothing Then
                fn.Offset(, 3) = sh1.Range("B" & Target.Row).Value
                fn.Offset(, 4) = sh1.Range("C" & Target.Row).Value
                fn.Offset(, 6) = sh1.Range("D" & Target.Row).Value
                fn.Offset(, 5) = sh1.Range("H" & Target.Row).Value
                fn.Offset(, 9) = sh1.Range("F" & Target.Row).Value
                fn.Offset(, 8) = sh1.Range("J" & Target.Row).Value
                fn.Offset(, 11) = sh1.Range("K" & Target.Row).Value
                fn.Offset(, 15) = sh1.Range("M" & Target.Row).Value
            End If
    End If
Application.EnableEvents = True
End Sub
 

sdennant

New Member
Joined
Mar 17, 2020
Messages
34
Office Version
  1. 365
Platform
  1. Windows
thanks. i wanted it to start on the next available blank row. so from row A5 if that's what you meant?

So i tried your code but i get the module pop up. I'm new to VBA so how should i get around that popup and be able to run the code?

And i saved the code in the Sainsbury's Module as opposed to inserting a new one. i think that's what you meant?

Module.PNG
 

sdennant

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

ADVERTISEMENT

Sorry, to clarify the Macro dialog box keeps popping up when I run it.

I know it's based on a change, but changing the dropdown in column O to completed doesn't seem to action the code.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
The worksheet module is the correct module and the sheet should be the SAINSBURY'S sheet, since it is the one where the Column O entries will be made.
Here is the revised code to post the values to the next available row.

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
If Not Intersect(Target, Range("O:O")) Is Nothing Then
Dim sh1 As Worksheet, sh2 As Worksheet, rw As Long
Set sh1 = Sheets("SAINSBURY'S")
Set sh2 = Sheets("ALL ORDERS")
    If Target.Value = "Completed" Then
        rw = sh2.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row + 1
            With sh2
                .Cells(rw, 4) = sh1.Range("B" & Target.Row).Value
                .Cells(rw, 5) = sh1.Range("C" & Target.Row).Value
                .Cells(rw, 7) = sh1.Range("D" & Target.Row).Value
                .Cells(rw, 6) = sh1.Range("H" & Target.Row).Value
                .Cells(rw, 10) = sh1.Range("F" & Target.Row).Value
                .Cells(rw, 9) = sh1.Range("J" & Target.Row).Value
                .Cells(rw, 12) = sh1.Range("K" & Target.Row).Value
                .Cells(rw, 16) = sh1.Range("M" & Target.Row).Value
            End With
    End If
End If
Application.EnableEvents = True
End Sub

I don't know why you were getting the Macro dialog box pop up. It usually does that if it thinks the user is attempting to call a procedure. You cannot run this procedure like one in an inserted module.. If you want to step through it, you will have to set a break point on one of the lines and then make a change in any cell on the sheet. The code will then stop at the break point and you can step trough the rest of it. But the code has to be activated with a change on the worksheet.
 

sdennant

New Member
Joined
Mar 17, 2020
Messages
34
Office Version
  1. 365
Platform
  1. Windows
thanks for the update. but the code doesnt seem to copy or paste when i toggle the dropdown in column O to completed. it's in the correct place in the sainsbury's module and i'm using the dropdown on the sainsbury's sheet to no avail.

tried breakpoints too. is there a way i can send you my workbook to have a look at?
 

sdennant

New Member
Joined
Mar 17, 2020
Messages
34
Office Version
  1. 365
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,583
Messages
5,625,622
Members
416,124
Latest member
DeMoNloK

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