Data to populate from one WS to another based on data entered in a cell

[teamexcel777]

I have 2 worksheets; LEADS & SALES

When any date is entered into LEADS H6:H1005, The information in LEADS B, C, D, E, F, & H6:1005
Will populate into SALES B, C, D, E, F & G6:1005 (The last column letter is different on LEADS vs SALES)

I do not want the info to auto sort based on any dates. I just want it to populate the info row by row
based on any date being entered into LEADS H6:H1005. I need to be able to sort the info on the sales
page so this is why I do not want the formula to sort based on a date. Just whenever a date is entered
then that’s the next to populate and so on.

 

[JLGWhiz]

copy this code to the sheet code module for sheet LEADS. To access the code module, right click the sheet name tab, then click 'View Code' in the pop up menu. After you have pasted the code into the code module, save your workbook as a macro enabled workbook, if not already so, to preserve the code when the workbook closes. Close the vb editor and the code will run when you make an entry into column H of sheet LEADS.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, r As Long
If Target.Cells.Count > 1 Or Target = "" Then Exit Sub
    If Not Intersect(Target, Range("H6:H1005")) Is Nothing And IsDate(Target) Then
        r = Target.Row
        Set rng = Union(Range("B" & r).Resize(1, 5), Range("H" & r))
        rng.Copy Sheets("SALES").Range("B" & r)
    End If
End Sub

Note: If you have existing worksheet_change code, this will need to be merged to one macro.

 

[teamexcel777]

Thanks for the reply. Here is the only issue with the code you provided. In LEADS H6:H1005 is where I am entering random dates to activate the code to populate onto SALES.
Problem is, Lets say on the LEADS WS I have information on the first 5 rows, #6-10. I might need to enter a date on H8 before I have entered a date on H6. Whats happening is when I enter a date on H8, it is populating on row 8 of SALES instead of starting at the top with H6 and going in order, H7, H8, H9, etc. based on when I enter a date into LEADS. The reason for this is some of my leads do not mature in the order in which I first acquire and enter them into the leads sheet, however I need them to populate on SALES in order and still allow for me to sort them as needed. Make sense? Please let me know if you have any further questions. Thanks again.

 

[JLGWhiz]

See if this works more to your needs.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, r As Long
If Target.Cells.Count > 1 Or Target = "" Then Exit Sub
    If Not Intersect(Target, Range("H6:H1005")) Is Nothing And IsDate(Target) Then
        r = Target.Row
        Set rng = Union(Range("B" & r).Resize(1, 5), Range("H" & r))
        rng.Copy Sheets("SALES").Cells(Rows.Count, 2).End(xlUp)(2)
    End If
End Sub

 

[teamexcel777]

for some reason its not working at all now. when I enter a date into LEADS H6:H1005, nothing populates onto SALES.

 

[JLGWhiz]

for some reason its not working at all now. when I enter a date into LEADS H6:H1005, nothing populates onto SALES.

Did you delete the original code and replace it in the sheet LEADS code module with the new code? To check if the code is trying to run, open the vbEditor with the process in post #2 . In the small margin immediately left of the code pane, left click next to the first 'If' statement. A brown highlight indicating a break point should appear there. Close the vb editor and then make a date entry into column H. If the code is running, Excel will display open the vb editor and display the code with a yellow highlight at the line where you put the break point. That means the code is running. If it still does not copy, then we have another problem to look for.

 

[JLGWhiz]

Also make sure you are not in design mode when you enter the date in column H. If the Triangle&Ruler icon is highlighted then you are in design mode and you need to click the icon to exit design mode. The event code will not run in design mode.

 

[teamexcel777]

part of the code does highlight in yellow so thats correct. As far as triangle and ruler, I have 2013 excel and do not see it. I never touched it and it was working fine with the old code with the exception of the sorting so its probably not in design mode. I just cant find the triangle/ruler tab to be sure though.

 

[JLGWhiz]

part of the code does highlight in yellow so thats correct. As far as triangle and ruler, I have 2013 excel and do not see it. I never touched it and it was working fine with the old code with the exception of the sorting so its probably not in design mode. I just cant find the triangle/ruler tab to be sure though.

The icon might not be on your Excel sheet ribbon menu, so you would need to open the vb editor and check it there. This link will show you what it looks like.
https://www.bing.com/images/search?q=Design+Mode+VBA&FORM=RESTAB

But if you got the yellow highlight, you are not in design mode. The only other thing I can think of is that the code was not installed in the correct sheet code module. The code worked fine in test set up.

 

[teamexcel777]

can i send you the WS so you can try it? I did all the steps over again, I first did it with the original code and it worked but had the issue sorting. Then I reopened and inserted the new code and nothing at all happens when I enter a date in LEADS H.