Please help.
The attached workbook is designed to be used by my receptionists as and when required.
My problem is getting the dynamic ranges 'RCL' & 'LCL' to be accurate.
At the moment I have tried one method to name 'RCL' but that only gives me one line, and another method for 'LCL' which gives me all my options but then includes thousands of blank lines.
I have included the code below to save anyone trawling through everything.
This is my first project proper with VBA so am learning as I go along.
Private Sub cmdSCH_Click()
Dim ws As Worksheet, LR As Long
Set ws = Worksheets("CL Data")
Application.ScreenUpdating = False
Sheets("RCL").Select
Cells.Select
Selection.ClearContents
Sheets("CL Data").Select
ws.Cells(2, 3).Value = Me.cbMatR.Value
ws.Cells(2, 4).Value = Me.cbModR.Value
ws.Cells(2, 5).Value = Me.cbFormR.Value
ws.Cells(2, 6).Value = Me.cbDesR.Value
Range("A5:K132").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("'CL Data'!Criteria"), Unique:=False
Range("A6:K132").Select
Selection.Copy
Sheets("RCL").Select
Range("A1").Select
ActiveSheet.Paste
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("A1:A" & LR).Name = "RCL"
Sheets("LCL").Select
Cells.Select
Selection.ClearContents
Sheets("CL Data").Select
ws.Cells(2, 3).Value = Me.cbMatL.Value
ws.Cells(2, 4).Value = Me.cbModL.Value
ws.Cells(2, 5).Value = Me.cbFormL.Value
ws.Cells(2, 6).Value = Me.cbDesL.Value
Range("A5:K132").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("'CL Data'!Criteria"), Unique:=False
Range("A6:K132").Select
Selection.Copy
Sheets("LCL").Select
Range("A1").Select
ActiveSheet.Paste
Range("A1:A" & Rows.Count).Name = "LCL"
Sheets("Search Sheet").Select
Application.ScreenUpdating = True
End Sub
Bookmarks