Posting to next available of variable worksheet

[crafter85]

Hello

I've got code which creates a new worksheet with the name of your choice (an employee's name) with a view to creating a staff database with individual records.

The next stage of the process is to input all their details. At the moment, these details are inputted on to the next available row on the "Master" spreadsheet. I've decided what I actually need to do is input them on to Row 42 or the next available row (same difference) on the new record which will change each time so doesn't necessarily have to be next available row if the row number can be fixed.

At this stage, the user is currently on the new record worksheet so rather than instruct to move to a specific named worksheet - as this will change each time for each new employee - I'd like to input the data into row 42/ next available row into the current or active worksheet but can't get it to work. This is what I have so far but need the final destination of the data to be the active worksheet (which is also the same name as textbox_recordname in case that helps):

Private Sub Cmdbutton_add_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Master")


'find first empty row in database
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1


'check for a Name number
If Trim(Me.textbox_name.Value) = "" Then
Me.textbox_name.SetFocus
MsgBox "Please complete the form"
Exit Sub
End If


'copy the data to the database
ws.Cells(iRow, 1).Value = Me.textbox_name.Value
ws.Cells(iRow, 2).Value = Me.textbox_surname.Value
ws.Cells(iRow, 3).Value = Me.textbox_dob.Value
ws.Cells(iRow, 4).Value = Me.textbox_address1.Value
ws.Cells(iRow, 5).Value = Me.textbox_address2.Value
ws.Cells(iRow, 6).Value = Me.textbox_address3.Value
ws.Cells(iRow, 7).Value = Me.textbox_address4.Value
ws.Cells(iRow, 8).Value = Me.textbox_postcode.Value
ws.Cells(iRow, 9).Value = Me.textbox_jobtitle.Value
ws.Cells(iRow, 10).Value = Me.textbox_startdate.Value
ws.Cells(iRow, 11).Value = Me.textbox_employmenttype.Value
ws.Cells(iRow, 12).Value = Me.textbox_enddate.Value
ws.Cells(iRow, 13).Value = Me.textbox_salary.Value
ws.Cells(iRow, 14).Value = Me.textbox_payscale.Value
ws.Cells(iRow, 15).Value = Me.textbox_hours.Value
ws.Cells(iRow, 16).Value = Me.textbox_holiday.Value
ws.Cells(iRow, 17).Value = Me.textbox_recordname.Value


MsgBox "Data added", vbOKOnly + vbInformation, "Data Added"
'clear the data
Me.textbox_name.Value = ""
Me.textbox_surname.Value = ""
Me.textbox_dob.Value = ""
Me.textbox_address1.Value = ""
Me.textbox_address2.Value = ""
Me.textbox_address3.Value = ""
Me.textbox_address4.Value = ""
Me.textbox_postcode.Value = ""
Me.textbox_jobtitle.Value = ""
Me.textbox_startdate.Value = ""
Me.textbox_employmenttype.Value = ""
Me.textbox_enddate.Value = ""
Me.textbox_salary.Value = ""
Me.textbox_payscale.Value = ""
Me.textbox_hours.Value = ""
Me.textbox_holiday.Value = ""
Me.textbox_recordname.Value = ""
Me.textbox_name.SetFocus
End Sub

 

[dmt32]

I was being an idiot (or it cooled down...), it works!!

Thank you so much

your welcome

glad got there in the end

Dave

 

[crafter85]

Ok, one tiny variation (it really is too hot now...!), on the Master spreadsheet, I now need for the data to go in to Row 3 rather than the next available row (I'm trying something out) but I can't work out which bit to change of your code!

I'll leave you alone after this!

 

[dmt32]

Ok, one tiny variation (it really is too hot now...!), on the Master spreadsheet, I now need for the data to go in to Row 3 rather than the next available row (I'm trying something out) but I can't work out which bit to change of your code!

I'll leave you alone after this!

Do you mean start in Row 3?

If so, then you just add line after GetRow Function returns Value to mRow variable

'find next empty row in master database
    mRow = GetRow(sh:=wsMaster)
    If mRow < 3 Then mRow = 3

Dave