Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Macro "If Then"
I want to use a macro to cut a range of data and place it into a different
worksheet. It looks like this: Cell A6 is a Validation list with 3 names. Each name represents a set of data which each have their own named worksheet. The data is keyed into A7:A15. The 3 types of data need to be sent to the correct worksheet and pasted at the bottom of the data base, and the cursor needs to be returned to cell A6 (validation list)of the first sheet. I can get the macro to run one "If Then arguement", but I do not know how to tie in the remainding 2 worksheets. Hope I explaine dmy problem clearly. Thanks for the help. |
#2
|
|||
|
|||
Can I use column A of the other worksheet to find the next available cell?
If yes, then maybe something like this will work for you: Option Explicit Sub testme() Dim otherWks As Worksheet Dim RngToCopy As Range Dim DestCell As Range Dim CurWks As Worksheet Dim keyCell As Range Set CurWks = ActiveSheet With CurWks Set keyCell = .Range("a6") Set otherWks = Nothing On Error Resume Next Set otherWks = Worksheets(keyCell.Value) On Error GoTo 0 If otherWks Is Nothing Then MsgBox "Invalid entry in: " & keyCell.Address(0, 0) Exit Sub End If Set RngToCopy = .Range("a7:A15") With otherWks Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) End With RngToCopy.Copy _ Destination:=DestCell RngToCopy.ClearContents '??? keyCell.ClearContents 'maybe ??? End With End Sub I'd put a button from the Forms toolbar near A6 and then after I made my choice, added my data, I'd click the button. The code does expect the worksheet names to match the entries in the Data|Validation list in A6. "Benny Benny via OfficeKB.com" wrote: I want to use a macro to cut a range of data and place it into a different worksheet. It looks like this: Cell A6 is a Validation list with 3 names. Each name represents a set of data which each have their own named worksheet. The data is keyed into A7:A15. The 3 types of data need to be sent to the correct worksheet and pasted at the bottom of the data base, and the cursor needs to be returned to cell A6 (validation list)of the first sheet. I can get the macro to run one "If Then arguement", but I do not know how to tie in the remainding 2 worksheets. Hope I explaine dmy problem clearly. Thanks for the help. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro and sub rountines | Excel Discussion (Misc queries) | |||
Playing a macro from another workbook | Excel Discussion (Misc queries) | |||
automatic macro update | Excel Worksheet Functions | |||
Date macro | Excel Discussion (Misc queries) | |||
Macro and If Statement | Excel Discussion (Misc queries) |