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