VBA to automatically loop through validation list with any change

mgerving

New Member
Joined
Mar 18, 2010
Messages
33
I have a validation list that when a specific date is selected, it runs a "PasteData" macro.
How do I insert code to automatically loop through all the dates in the list, any time there is a change in the Sheet?
The PasteData sub runs perfectly, just trying to make this more automatic.

Sub Worksheet_Change(ByVal Target As Range)
Set Target = Range("B3")
If Target.Value = "Nov 20" Then
Call PasteData
End If
Set Target = Range("B3")
If Target.Value = "June 21" Then
Call PasteData
End If
Set Target = Range("B3")
If Target.Value = "Nov 21" Then
Call PasteData
End If
Set Target = Range("B3")
If Target.Value = "June 22" Then
Call PasteData
End If
Set Target = Range("B3")
If Target.Value = "Nov 22" Then
Call PasteData
End If
Set Target = Range("B3")
If Target.Value = "June 23" Then
Call PasteData
End If
Set Target = Range("B3")
If Target.Value = "Nov 23" Then
Call PasteData
End If
End Sub
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Do you mean you want to run the PasteData sub whenever B3 is changed? or only if B3 is June or Nov?
 
Upvote 0
I already have that portion working, what I would like to do is cycle through all the date lists in B3, whenever there is another change on this sheet.
The way this works is that when PasteData is run, it copy and pastes all the cell values in Range AM6:AM205 and then pastes those values on another sheet.
The sheet that holds the PasteData has a number of other checkboxes that change the values in AM6:AM205, I want eliminate the user to cycle through all the B3 lists in order to update the values on the other sheet...make sense?
Metropolitan
Nov 20
0%
ClassificationBase RateTotal RateProfit on Total Rate
GF$ 58.00$ 92.50$ 92.50
GF 10% Shift$ 63.80$ 99.54$ 99.54
GF 15% Shift$ 66.70$103.06$103.06
 
Upvote 0
Maybe this Xl2BB is better
REI LU26 Labor Rate Worksheet - test.4.xlsm
ABALAMANAO
1
2MetropolitanTotal QtyLabor Rate
3Nov 200Add Workers
40%
5ClassificationBase RateTotal RateProfit on Total RateQtyExtension
6GF$ 58.00$ 92.50$ 92.500$ -
7GF 10% Shift$ 63.80$ 99.54$ 99.540$ -
8GF 15% Shift$ 66.70$103.06$103.060$ -
9GF Overtime$ 87.00$127.71$127.710$ -
10GF Overtime + 10% Shift$ 95.70$138.27$138.270$ -
11GF Overtime + 15% Shift$ 100.05$143.56$143.560$ -
12GF Double Time$ 116.00$162.92$162.920$ -
Metropolitan
Cells with Conditional Formatting
CellConditionCell FormatStop If True
AO3:AO4Cell Value="Add Workers"textNO
AO3:AO4Expression=$BJ$5>$BJ$4textNO
AO3:AO4Expression=$BJ$4>$BJ$5textNO
 
Upvote 0
How is the Data validation populated?
 
Upvote 0
From a list and I am using the following formula:
=INDEX(RATE!B3:H4,MATCH($B$2,Local,0),MATCH($B$3,Period,0))
Where 'Local' is B2 and 'Period' is B3
B2 has 2 choices - Metropolitan and Shenandoah
So if I select Metropolitan in B2, it will automatically loop through all the B3 items, and then copy paste values to another sheet.
Right now, if I cycle between the B2 choices, it will only copy paste the date that is shown in B3.
 
Upvote 0
Ok, how about
VBA Code:
   Dim Cl As Range, ListRng As Range
   Dim Ary As Variant
   Dim i As Long

   Set Cl = Range("B3")
   On Error Resume Next
   Set ListRng = Range(Replace(Cl.Validation.Formula1, "=", ""))
   On Error GoTo 0
   
   If ListRng Is Nothing Then
      MsgBox "No validation"
      Exit Sub
   End If
   ReDim Ary(1 To ListRng.Rows.Count)

   For i = 1 To ListRng.Rows.Count
       Ary(i) = Range(Replace(Cl.Validation.Formula1, "=", "")).Cells(i, 1)
   Next i
   For i = 1 To UBound(Ary)
      Cl.Value = Ary(i)
      Application.Calculate
      Call PasteData
   Next i
 
Upvote 0
It replaces the code you have in the Worksheet_Change event.
 
Upvote 0
I put the code here:
Sub Worksheet_Change(ByVal Target As Range)
Dim Cl As Range, ListRng As Range
Dim Ary As Variant
Dim i As Long

Set Cl = Range("B3")
On Error Resume Next
Set ListRng = Range(Replace(Cl.Validation.Formula1, "=", ""))
On Error GoTo 0

If ListRng Is Nothing Then
MsgBox "No validation"
Exit Sub
End If
ReDim Ary(1 To ListRng.Rows.Count)

For i = 1 To ListRng.Rows.Count
Ary(i) = Range(Replace(Cl.Validation.Formula1, "=", "")).Cells(i, 1)
Next i
For i = 1 To UBound(Ary)
Cl.Value = Ary(i)
Application.Calculate
Call PasteData
Next i

End Sub

When I select a new range in B3, I get "No Validation" error.?
 
Upvote 0

Forum statistics

Threads
1,212,938
Messages
6,110,788
Members
448,297
Latest member
carmadgar

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
Back
Top