Apologies if this has been covered previously, but have been unable to find a solution.
Col A on RANGES sheet lists three different meals. Cols B - D list the ingredients of each one.
Macro should be triggered if the DropList in C6 on the CALCULATOR sheet changes.
If the User clears the cell, it should delete any ingredients listed in Col D and exit the sub.
If the User selects a meal from the C6 Droplist, Macro should delete any ingredients in Col D, find the relevant list on the RANGES sheet, and copy those ingredients back to Col D on the CALCULATOR.
For whatever reason, the Macro is not "seeing" any change to C6, so nothing happens if you change or clear it.
Option Explicit
Dim c As Long, f As Long, m As Long
Dim rng As Range
Dim ws As Worksheet
Private Sub Worksheet_Change(ByVal Target As Range)
Set ws = Sheet1
'Run Macro only if C6 changes
With ws
If Target.Address = .Range("C6") Then
.Range(.Cells(6, 4), .Cells(23, 4)).ClearContents
'Stop macro if no meal selected
If .Range("C6") = "" Then
Exit Sub
Else
Application.EnableEvents = False
With Sheet2
'Find last col on Ranges sheet
c = .Cells(1, .Columns.Count).End(xlToLeft).Column
'Find col matching Meal
With .Range("1:1")
Set rng = .Find(What:=ws.Range("C6"), After:=.Cells(.Cells.Count), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
If Not rng Is Nothing Then
m = rng.Column
End If
End With
'Find last row in Meal column
f = .Cells(.Rows.Count, m).End(xlUp).Row
'Copy ingredients to CALCULATOR
.Range(.Cells(2, m), .Cells(f, m)).Copy Destination:=Sheet1.Cells(6, 4)
End With
End If
End If
End With
Application.EnableEvents = True
End Sub
All solutions, suggestions and alternatives welcome as ever
Ochimus
Bookmarks