Adding Data to diffrent worksheets from userform

[PMRetired2012]

I have a simple userform setup. i have written code to work on all the userform but what im havint trouble getting to work is: i have 8 diffrent worksheets that i can select what worksheet i place data into. My problem is i cant get the data to go into the first empty cell (Like A1) on the sheet i select and move on down the column as i enter new data on the sheet i select. Really what im asking i guess how do i enter data in multiple worksheets as i enter data and select the worksheet i want What i do have with the code i have written is i can get data into the first cell at the bottom of my form i have layed out on the worksheet. I will include the code i have to made that doesnt work.

Private Sub CommandButton1_Click()
TargetSheet = ComboBox1.Value
If TargetSheet = "" Then
Exit Sub
End If
Worksheets(TargetSheet).Activate
lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
ActiveSheet.Cells(lastrow + 1, 1).Value = TextBox1.Value
MsgBox ("Data is added successfully")
TextBox1.Value = ""
Worksheets("Master Sheet").Activate
Worksheets("Master Sheet").Cells(1, 1).Select
End Sub

 

[Fluff]

How about

Private Sub CommandButton1_Click()
   TargetSheet = ComboBox1.Value
   If TargetSheet = "" Then Exit Sub
   
   With Worksheets(TargetSheet)
      If .Range("A1") = "" Then
         LastRow = 1
      Else
         LastRow = .Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
      End If
      .Cells(LastRow, 1).Value = TextBox1.Value
      MsgBox ("Data is added successfully")
      TextBox1.Value = ""
   End With
   Worksheets("Master Sheet").Activate
   Worksheets("Master Sheet").Cells(1, 1).Select
End Sub

 

[PMRetired2012]

The problem is i wanted data to start in cell A3 on each worksheet i failed to tell you that. the Data shows up in cell A1 like the code is now. Also when i go to add another name to the same sheet i select it wont go on down to the next row with new name. the code replaces the name in Cell A1. so when adding a new name to the same sheet it wont adveance to the next row so i will have 2 names and so on down the sheet.

 

[Fluff]

It would have helped if you said that originally, rather than referring to A1.
How about

Private Sub CommandButton1_Click()
   TargetSheet = ComboBox1.Value
   If TargetSheet = "" Then Exit Sub
   
   With Worksheets(TargetSheet)
      LastRow = .Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
      If LastRow < 3 Then LastRow = 3
      .Cells(LastRow, 1).Value = TextBox1.Value
      MsgBox ("Data is added successfully")
      TextBox1.Value = ""
   End With
   Worksheets("Master Sheet").Activate
   Worksheets("Master Sheet").Cells(1, 1).Select
End Sub

 

[PMRetired2012]

The problem now is when i enter a name into user form it puts it at the bottom of my worksheet (like if i have 40 rows used it put the info in row 41 cell A1. But i still want my first name in cell A3 and as i add names that name will be added to next row. If it would help i could send you the file.

Sorry about not saying cell A3

Dennis

 

[Fluff]

I don't understand what you mean. One moment your are saying "as you add names it should go to the next row", but then you also say that "If 40 rows are used, the name should still go in A3"
To me those conflict with each other.

 

[PMRetired2012]

Sorry about the confusion.What I'm saying is when I use the user form to add data to a worksheet it puts the information from the 1 text box that the userform has in cell A41 instead of cell A3 of the selected sheet that i select from a combix.
If I need to send you the file I can. I think we are close to having this figured out. Thanks

 

[Fluff]

How will the code know where to put the value?
Should it be in A3 downwards as far as (for instance) A20?
If so what happens when all those cells are full?

 

[PMRetired2012]

When I show the user form I have a drop down combo box that has all the possible worksheets in it. I choose the sheet that I want to put the data in . On the worksheet the data can go downwards from cells A3 thru A24. Not sure what I will do when they get full

 

[Fluff]

Ok, if you will always have data in any row below row 23, try

Private Sub CommandButton1_Click()
   targetsheet = ComboBox1.Value
   If targetsheet = "" Then Exit Sub
   
   With Worksheets(targetsheet)
      On Error GoTo NoBlanks
      .Range("A3:A24").SpecialCells(xlBlanks)(1).Value = TextBox1.Value
      On Error GoTo 0
   End With
   Worksheets("Master Sheet").Activate
   Worksheets("Master Sheet").Cells(1, 1).Select
   Exit Sub
NoBlanks:
   MsgBox "No more blanks in sheet " & targetsheet & " range A3:A24"
End Sub