Userforms. Adding values to multiple sheets using checkboxes

[chenyu768]

Hi
Here's my problem

I have a userform that someone fills out and when you hit submit it adds it to a "customer list" sheet and then sorts it A-Z. ok no problem.

But now i added 2 checkboxes, I want the same data that added to the "customer list" sheet to be added to one or two other sheets (depending on what boxes, if any, are checked)

I thought i was the cat's meow until my colleagues requested the checkbox feature. Please help

Private Sub CommandButton2_Click()





Dim LastRow As Long, ws As Worksheet


    Set ws = Sheets("CustomerList")
    Set ws1 = Sheets("GTSA")
    Set ws2 = Sheets("ECS")
    


    LastRow = ws.Range("C" & Rows.Count).End(xlUp).Row + 1 'Finds the last blank row
    LastRow1 = ws1.Range("C" & Rows.Count).End(xlUp).Row + 1 'Finds the last blank row
    LastRow2 = ws2.Range("C" & Rows.Count).End(xlUp).Row + 1 'Finds the last blank row
    
    If CheckBox1 = True Then
    
        ws1.Range("C" & LastRow).Value = "New"
        ws1.Range("E" & LastRow).Value = CustNameBox.Value
        ws1.Range("D" & LastRow).Value = MKTRBOX.Text
        ws1.Range("A" & LastRow).Value = Format(Now(), "MM/DD/YY")
        ws.Range("A" & LastRow).Value = CustNameBox.Text
        ws.Range("C" & LastRow).Value = MKTRBOX.Text
        ws.Range("G" & LastRow).Value = AcctRepBox.Text
        
        ws.Activate
        Columns("A:G").Select
         ActiveWorkbook.Worksheets("CustomerList").Sort.SortFields.Clear
         ActiveWorkbook.Worksheets("CustomerList").Sort.SortFields.Add Key:=Range( _
        "A2:A157"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("CustomerList").Sort
        .SetRange Range("A1:G157")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
      
Else


        ws.Range("A" & LastRow).Value = CustNameBox.Text
        ws.Range("C" & LastRow).Value = MKTRBOX.Text
        ws.Range("G" & LastRow).Value = AcctRepBox.Text
        
        ws.Activate
        Columns("A:G").Select
        ActiveWorkbook.Worksheets("CustomerList").Sort.SortFields.Clear
         ActiveWorkbook.Worksheets("CustomerList").Sort.SortFields.Add Key:=Range( _
        "A2:A157"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
         With ActiveWorkbook.Worksheets("CustomerList").Sort
        .SetRange Range("A1:G157")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
      
End If


If CheckBox2 = True Then
    
    ws2.Range("C" & LastRow).Value = "New"
    ws2.Range("E" & LastRow).Value = CustNameBox.Value
    ws2.Range("D" & LastRow).Value = MKTRBOX.Text
    ws2.Range("A" & LastRow).Value = Format(Now(), "MM/DD/YY")
    ws.Range("A" & LastRow).Value = CustNameBox.Text
    ws.Range("C" & LastRow).Value = MKTRBOX.Text
    ws.Range("G" & LastRow).Value = AcctRepBox.Text
    
        ws.Activate
    Columns("A:G").Select
    ActiveWorkbook.Worksheets("CustomerList").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("CustomerList").Sort.SortFields.Add Key:=Range( _
        "A2:A157"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("CustomerList").Sort
        .SetRange Range("A1:G157")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
      


Else
    ws.Range("A" & LastRow).Value = CustNameBox.Text
    ws.Range("C" & LastRow).Value = MKTRBOX.Text
    ws.Range("G" & LastRow).Value = AcctRepBox.Text
    ws.Activate
    Columns("A:G").Select
    ActiveWorkbook.Worksheets("CustomerList").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("CustomerList").Sort.SortFields.Add Key:=Range( _
        "A2:A157"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
        
    With ActiveWorkbook.Worksheets("CustomerList").Sort
        .SetRange Range("A1:G157")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
      
End If
    
    
   
    
    ws.Activate
    Columns("A:G").Select
    ActiveWorkbook.Worksheets("CustomerList").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("CustomerList").Sort.SortFields.Add Key:=Range( _
        "A2:A157"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("CustomerList").Sort
        .SetRange Range("A1:G157")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
      






    
Application.Calculate


    
Unload Me
End Sub

 

[mrmmickle1]

Based on what I'm looking at... I think your issue is this:


This line of code defines the last row for Sheets("CustomerList")

    LastRow = ws.Range("C" & Rows.Count).End(xlUp).Row + 1[COLOR=#008000] 'Finds the last blank row[/COLOR]

However, you use LastRow all through out your code..... so if you need the lastrow from other sheets you need to use the two variables you define at the top of the procedure rather than using the dame LastRow over and over again.


You need to swap out some of your LastRow variables for the other two you defined.....

    LastRow1 = ws1.Range("C" & Rows.Count).End(xlUp).Row + 1 [COLOR=#008000]'Finds the last blank row[/COLOR]
    LastRow2 = ws2.Range("C" & Rows.Count).End(xlUp).Row + 1[COLOR=#008000] 'Finds the last blank row[/COLOR]

 

[chenyu768]

OMG it was so simple i can't believe i missed that. thank you good sir.

 

[mrmmickle1]

Glad it helped you to solve your issue. Many times I find that if I just step away from the file for a few hours and come back....your issue will stand out and yes it's usually a very simple oversight.