Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Selected Range to new sheet
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy a selected Range to a Workbook | Excel Programming | |||
How do I edit a selected range then copy the range into an new sheet??? | Excel Programming | |||
How to copy selected listbox item to sheet | Excel Programming | |||
Nameing a range while still selected from copy | Excel Programming | |||
copy selected rows to new sheet | Excel Programming |