Tracking Pulldown List Activity Into A Seperate Tab

[sbecker61]

Hello,

I have an Excel 2007 document with four different tabs (Chicago, Cincinnati, St Louis and Detroit). In each tab, we track information about sales activity. In cells N30 thru N(x) we store a pull down item that is called "Forecast Category". It has the possible values of Swing - High, Swing - Low, Closed - Lost, Closed - Won, Closed - Disengage, Commit, and Fallout. The user has to choose one of those.

In each of those four different tabs, I would like to track all the information about the customer into a different tab (call it Audit) when a Forecast Category of "Commit" or "Closed - Lost" is chosen by the user. So that activity (pull down select) would then activate the VB app to write out the specific line of info about the customer to the Audit tab.

To net it out .... The Audit tab would just contain those customers where Forecast Category of Commit or Closed - Lost is chosen.

I know that I need to write a VB app for each tab, but what would the code look like? A deal may change its Forecast Category several times so the code would need to be able to find the first empty line in the Audit tab and write the information out. I would also want to time stamp when the Forecast Category was changed.

I am hoping I made sense with this? Thank you in advance.

Steve

 

[hiker95]

sbecker61,

You could use the Worksheet_Change Event in worksheets Chicago, Cincinnati, St Louis and Detroit.

When a cell in column N changes to either Commit or Closed - Lost, that row of data can be copied into the next available row in worksheet Audit.

What column in worksheet Audit, do you want the time stamp in?

What do you want the time stamp to look like (date and time like YYYYMMDD HH:MM:SS AM/PM)?


You will generally get much more help (and faster) in this forum if you can post your small samples (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

If you are not able to give us screenshots, see below in my Signature block: You can upload your workbook to Box Net

 

[sbecker61]

This is the code I have so far:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 14 Then Target.EntireRow.Copy Destination:=Sheets("Audit").Range("A" & Rows.Count).End(xlUp).Offset(1)
End Sub

Challenges I see with this code and where I could use help:

1) How do I ensure its column N30 and higher in the four different tabs? the 14 just refers to column "N".
2) I would like the Timestamp be the first column (A) in the Audit tab. I'm not sure how to do so.
3) The timestamp you referred to is fine.

Thank you for your help.

Steve

 

[hiker95]

sbecker61,

2) I would like the Timestamp be the first column (A) in the Audit tab. I'm not sure how to do so.

Can I put the timestamp in column A of the active worksheet (Chicago, Cincinnati, St Louis and Detroit) for the row that is being copied into worksheet Audit?

Or, put the timestamp in worksheet Audit, column A, where the latest row was just copied to?

 

[hiker95]

sbecker61,


Sample empty worksheets to begin with (some columns are hidden for brevity):

Excel Workbook
	A	G	N	O
1
2
3
4
5
6
7
8
9
10
Audit

Excel Workbook
	A	G	N	O
30	30	30		30
31	31	31		31
32	32	32		32
33	33	33		33
34	34	34		34
35	35	35		35
36	36	36		36
37	37	37		37
38	38	38		38
39	39	39		39
40
Cincinnati

I will make some choices from the validation list in column N:

Excel Workbook
	A	G	N	O
30	30	30	Swing - High	30
31	31	31	Swing - Low	31
32	32	32		32
33	33	33	Fallout	33
34	34	34	Closed - Disengage	34
35	35	35	Closed - Won	35
36	36	36		36
37	37	37	Commit	37
38	38	38		38
39	39	39	Closed - Lost	39
40
Cincinnati

And the result in worksheet Audit is:

Excel Workbook
	A	G	N	O
1
2	20110406 07:27:29 PM	37	Commit	37
3	20110406 07:27:35 PM	39	Closed - Lost	39
4
5
6
7
8
9
10
Audit

Please test this in a copy of your workbook, in one of the worksheets Chicago, Cincinnati, St Louis, or Detroit.



Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Select the worksheet in which your code is to run
3. Right click on the sheet tab and choose View Code, to open the Visual Basic Editor
4. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
5. Press the keys ALT + Q to exit the Editor, and return to Excel

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
' hiker95, 04/06/2011
' http://www.mrexcel.com/forum/showthread.php?t=541382
If Intersect(Target, Columns(14)) Is Nothing Then Exit Sub
If Target = "" Then Exit Sub
If Target.Row < 30 Then Exit Sub
Dim LR As Long
With Application
  .EnableEvents = False
  .ScreenUpdating = False
  If Target.Value = "Commit" Or Target.Value = "Closed - Lost" Then
    Target.EntireRow.Copy Destination:=Worksheets("Audit").Range("A" & Rows.Count).End(xlUp).Offset(1)
    LR = Worksheets("Audit").Range("A1").CurrentRegion.Rows.Count
    Worksheets("Audit").Range("A" & LR) = Format(Now(), "YYYYMMDD HH:MM:SS AM/PM")
  End If
  .ScreenUpdating = True
  .EnableEvents = True
End With
End Sub

Then make changes to the validation list in column N.

 

[sbecker61]

Hi, thank you for that. Its close but one thing not happening. I would like the timestamp to be put in column A of the Audit worksheet. I would then like the entire row where the change was made on the source sheet to start on column B. In the code you provided, my first column (A) is not being copied over. The name of the column from the source is called "Assigned To".

Thank you.

 

[hiker95]

sbecker61,

I thought that may happen.

What is the last used column in the worksheets Chicago, Cincinnati, St Louis, or Detroit?

 

[sbecker61]

column O is the last column where data is filled in on the source worksheets. The data starts in column A of the source worksheet.

Thanks.

Steve

 

[hiker95]

sbecker61,


Sample empty worksheets to begin with (some columns are hidden for brevity):

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2
3
4
5
6
7
8
9
10
11
Audit

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
30	A30	31	31	31	31	31	31	31	31	31	31	31	31		O30
31	A31	31	31	31	31	31	31	31	31	31	31	31	31		O31
32	A32	32	32	32	32	32	32	32	32	32	32	32	32		O32
33	A33	33	33	33	33	33	33	33	33	33	33	33	33		O33
34	A34	34	34	34	34	34	34	34	34	34	34	34	34		O34
35	A35	35	35	35	35	35	35	35	35	35	35	35	35		O35
36	A36	36	36	36	36	36	36	36	36	36	36	36	36		O36
37	A37	37	37	37	37	37	37	37	37	37	37	37	37		O37
38	A38	38	38	38	38	38	38	38	38	38	38	38	38		O38
39	A39	39	39	39	39	39	39	39	39	39	39	39	39		O39
40
Cincinnati

I will make some choices from the validation list in column N:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
30	A30	31	31	31	31	31	31	31	31	31	31	31	31		O30
31	A31	31	31	31	31	31	31	31	31	31	31	31	31	Swing - High	O31
32	A32	32	32	32	32	32	32	32	32	32	32	32	32		O32
33	A33	33	33	33	33	33	33	33	33	33	33	33	33	Fallout	O33
34	A34	34	34	34	34	34	34	34	34	34	34	34	34		O34
35	A35	35	35	35	35	35	35	35	35	35	35	35	35	Commit	O35
36	A36	36	36	36	36	36	36	36	36	36	36	36	36		O36
37	A37	37	37	37	37	37	37	37	37	37	37	37	37	Closed - Lost	O37
38	A38	38	38	38	38	38	38	38	38	38	38	38	38	Commit	O38
39	A39	39	39	39	39	39	39	39	39	39	39	39	39		O39
40
Cincinnati

And the result in worksheet Audit is:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2	20110407 09:44:50 AM	A35	35	35	35	35	35	35	35	35	35	35	35	35	Commit	O35
3	20110407 09:44:57 AM	A37	37	37	37	37	37	37	37	37	37	37	37	37	Closed - Lost	O37
4	20110407 09:45:03 AM	A38	38	38	38	38	38	38	38	38	38	38	38	38	Commit	O38
5
6
7
8
9
10
11
Audit

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Select the worksheet in which your code is to run
3. Right click on the sheet tab and choose View Code, to open the Visual Basic Editor
4. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
5. Press the keys ALT + Q to exit the Editor, and return to Excel

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
' hiker95, 04/07/2011
' http://www.mrexcel.com/forum/showthread.php?t=541382
If Intersect(Target, Columns(14)) Is Nothing Then Exit Sub
If Target = "" Then Exit Sub
If Target.Row < 30 Then Exit Sub
Dim LR As Long
With Application
  .EnableEvents = False
  .ScreenUpdating = False
  If Target.Value = "Commit" Or Target.Value = "Closed - Lost" Then
    Range("A" & Target.Row & ":O" & Target.Row).Copy Destination:=Worksheets("Audit").Range("B" & Rows.Count).End(xlUp).Offset(1)
    LR = Worksheets("Audit").Range("A1").CurrentRegion.Rows.Count
    Worksheets("Audit").Range("A" & LR) = Format(Now(), "YYYYMMDD HH:MM:SS AM/PM")
  End If
  .ScreenUpdating = True
  .EnableEvents = True
End With
End Sub

Then make changes to the validation list in column N.

 

[sbecker61]

this works great. Thank you VERY much for your help. I really appreciate it.