Copy Selected Range to new sheet

[richie c]

I have three drop down menus that i want to select like so. 1st drop down
select building type, 2nd (C6) is dependent upon 1st and selects specific
sheet. 3rd (D6) should select range within sheet by searching for string
(veh/cyc/ped). However ranges within the sheet are different sizes and so i
need to search for a blank cell or unique char.

However range and search functions are absolute and relative.

help. very confused

Sub Addsheet()

Dim WS As Worksheet
Set WS = Sheets.Add

End Sub





Sub CopyDataToPlan()

Dim LDate As String
Dim LColumn As Integer
Dim LFound As Boolean
Dim RangeStart As String
Dim RangeEnd As String


On Error GoTo Err_Execute

'Retrieve date value to search for
LDate = Sheets("FRONT PAGE").Range("D6").Value

Sheets("C6").Select

'Start at column B
LRow = 5
LColumn = 2
LFound = False
RangeStart = A1
RangeEnd = B2

While LFound = False

'Encountered blank cell in row 2, terminate search
If Len(Cells(LRow, LColumn)) = 0 Then
MsgBox "No matching date was found."
Exit Sub

'Found match in row 2
ElseIf Cells(LRow, LColumn) = LDate Then

'Select values to copy from Selected sheet
Sheets("D6").Select
Range("RangeStart:RangeEnd").Select
Selection.Copy

'Paste onto "Plan" sheet
Sheets("Plan").Select
Cells(LRow, LColumn).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False

LFound = True
MsgBox "The data has been successfully copied."

'Continue searching
Else
LRow = LRow + 1
End If

Wend

On Error GoTo 0

Exit Sub

Err_Execute:
MsgBox "An error occurred."

End Sub