Editing VBA code for data transfer

[stef]

Hello All,
I have a workbook with two spreadsheet tabs, I need to automatically transfer data from the first spreadsheet (CEC) to the second (Timeline Status). The data transfer code is already setup however i need it to be modified to represent a few more columns of data and removal of a few.

First.
When data is put in column A in the CEC tab this should populate into the Timeline tab column A.

Second
Based on the change in 'application current status' and 'date in status change' on the CEC tab (column C and D)this data will be transferred to the TIMELINE tab into the respective headings as in the drop downmenu (a listing of these headings are in the Settings tab). Below are the corresponding data transfers in both sheets

CECs tab - Column A <---->Timelines tab - Column A

------------ Column C <----> ---------------- Column B-S ( so the date of the status change (column D) will be in these cells)
(If you look at the VBA current code you will have a better idea)


------------ Column H <----> ----------------- Column T

------------ Column I<----> ------------------- Column U

The worksheet is attached to be edited is 'test edit VBA'. i just did an example and the data is populating in the wrong way.
the second workbook 'TEST6' is an example of a correctly populating workbook but does not have all the data columns I want.

Can anyone help please? I hope I made this clear and understandable and is in the correct forum to post.

 

[NARAYANK991]

Hi ,

See if this is OK.

Narayan

 

[stef]

It's working perfectly, everything looks great. Just one question. If an incorrect value is entered into a cell, this error come up and if the user clicks on debug they will have access to the code. Can we prevent this from happening? See below. Thanks



ll

 

[NARAYANK991]

Hi ,

Can you post a screenshot of the line of code which is highlighted when this error comes up ?

Which worksheet cell contains the erroneous data ?

Narayan

 

[stef]

Here is the screenshot for example when i enter an incomplete date in D3 of CEC tab.

.

 

[NARAYANK991]

Hi ,

It is not a matter of an incomplete date though that is certainly a data-entry error ; the problem actually is that C3 has been left blank , and so when we try and find a match for the status , there is none.

It is only based on the match that we decide in which column the date has to be placed. When there is no match , it generates an error.

See the uploaded file for the revised code.

Narayan

 

[stef]

Okay I understand and this works thanks.

Also one more thing, I am trying to edit the formula to have two columns with multiple selections from the drop down menu. The formula already works for the second column, how do I get it to work for the 11th column? I believe this is the related part of the code.

LastStep:
On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler

If rngDV Is Nothing Then GoTo exitHandler
If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal

If Target.Column = 4 Then
If oldVal = "" Then
Else
If newVal = "" Then
Else
Target.Value = oldVal & ", " & newVal
End If
End If
End If
End If

 

[NARAYANK991]

Hi ,

Column number 4 refers to column D.

Column number 11 will refer to column K.

Try replacing the 4 by 11 if this is what you want.

Narayan

 

[stef]

I want it to work for both columns 4 and 11

 

[NARAYANK991]

Hi ,

Replace this statement :

If Target.Column = 4 Then

by this one :

If ((Target.Column = 4) Or (Target.Column = 11)) Then

Narayan

 

[stef]

Great this works. I was entering If Target.Column = 4 And 11 Then

woops!
Great thanks!