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

[sdennant]

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.

 

[JLGWhiz]

You cannot paste a value into a cell with a formula without destroying the formula..

 

[sdennant]

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]

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]

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.

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]

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?

 

[sdennant]

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]

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.

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]

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]

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.