Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Adding Data to diffrent worksheets from userform
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Adding Data to diffrent worksheets from userform

    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

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,228
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Adding Data to diffrent worksheets from userform

    How about
    Code:
    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    New Member
    Join Date
    Aug 2019
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding Data to diffrent worksheets from userform

    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.

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,228
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Adding Data to diffrent worksheets from userform

    It would have helped if you said that originally, rather than referring to A1.
    How about
    Code:
    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    New Member
    Join Date
    Aug 2019
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding Data to diffrent worksheets from userform

    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

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,228
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Adding Data to diffrent worksheets from userform

    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.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    New Member
    Join Date
    Aug 2019
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding Data to diffrent worksheets from userform

    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

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,228
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Adding Data to diffrent worksheets from userform

    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?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #9
    New Member
    Join Date
    Aug 2019
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding Data to diffrent worksheets from userform

    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

  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,228
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Adding Data to diffrent worksheets from userform

    Ok, if you will always have data in any row below row 23, try
    Code:
    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •