How can I set up a dynamic data entry form?

[Eric]

Hi everyone.

I am trying to create a form on Excel that does the following:

The user enters a piece of data unto 3-4 columns. The user then hits a
function start key. The data is then stored in a different sheet using the
next available line. I do not know VB except for BASIC Macros.

I tried creating a counter using the "counta" function. This worked. I
then created a Macro to cut and paste this value on the "Go to" command but
the issue there was it did not update the new value and always went to the
first cell that I originally selected.

Any thoughts how to do this in an uncomplicated way?

 

[ryguy7272]

This should give you what you want:
Sub ExportDatabaseToSeparateSheets()
'Export is based on the value in the desired column
Dim myCell As Range
Dim mySht As Worksheet
Dim myName As String
Dim myArea As Range
Dim myShtName As String
Dim KeyCol As Integer

myShtName = ActiveSheet.Name
KeyCol = InputBox("What column # within database to use as key?")

Set myArea = ActiveCell.CurrentRegion.Columns(KeyCol).Offset(1, 0).Cells

Set myArea = myArea.Resize(myArea.Rows.Count - 1, 1)

For Each myCell In myArea
On Error GoTo NoSheet
myName = Worksheets(myCell.Value).Name
GoTo SheetExists:
NoSheet:
Set mySht = Worksheets.Add(Before:=Worksheets(1))
mySht.Name = myCell.Value
With myCell.CurrentRegion
.AutoFilter Field:=KeyCol, Criteria1:=myCell.Value
.SpecialCells(xlCellTypeVisible).Copy _
mySht.Range("A1")
mySht.Cells.EntireColumn.AutoFit
.AutoFilter
End With
Resume
SheetExists:
Next myCell

End Sub


Regards,
Ryan--