Adding code to sort ALL spreadsheets to a UserForm I have a UserForm which adds

Question

Adding code to sort ALL spreadsheets to a UserForm

I have a UserForm which adds a the Name, Initials and age to the first 3 columns of 13 spreadsheets. One for each month and one as a Nominal list.

Private Sub CommandButton1_Click()

Dim LRJan As Long, lrFeb As Long, lrMar As Long, lrApr As Long, lrMay As Long, lrJun As Long, lrJul As Long, lrAug As Long, lrSep As Long, lrOct As Long, lrNov As Long, lrDec As Long, lrNominal As Long


LRJan = Sheets("Jan").Range("A" & Rows.Count).End(xlUp).Row
Sheets("Jan").Cells(LRJan + 1, "A").Value = TextBox1.Text
Sheets("Jan").Cells(LRJan + 1, "B").Value = TextBox2.Text
Sheets("Jan").Cells(LRJan + 1, "C").Value = TextBox3.Text

lrFeb = Sheets("Feb").Range("A" & Rows.Count).End(xlUp).Row
Sheets("Feb").Cells(lrFeb + 1, "A").Value = TextBox1.Text
Sheets("Feb").Cells(lrFeb + 1, "B").Value = TextBox2.Text
Sheets("Feb").Cells(lrFeb + 1, "C").Value = TextBox3.Text

etc

Me.Hide

End Sub

I am trying to add some code to thte abovve so thatt beforre Me.Hide ALL 13 spreadsheets (Columns A to AJ) are sorted by "Name" Column A in asscending order.

Any ideas???

Explanation / Answer

Or a different way using a single process for all sheets named in strSheets (separated by spaces):

Private Sub CommandButton1_Click()

Dim LastRow As Long
Dim LastCol As Long
Dim lngSheet As Long
Dim xlSheet As Worksheet
Dim vSheet As Variant
Const strSheets As String = "Jan Feb Mar Apr May Jun Jul Aug Sep Act Nov Dec Nominal"
    vSheet = Split(strSheets)
    For Each xlSheet In Sheets
        For lngSheet = 0 To UBound(vSheet)
            If xlSheet.Name = vSheet(lngSheet) Then
                With xlSheet
                    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
                    LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
                    .Cells(LastRow + 1, "A").Value = TextBox1.Text
                    .Cells(LastRow + 1, "B").Value = TextBox2.Text
                    .Cells(LastRow + 1, "C").Value = TextBox3.Text
                    .Range("A1").CurrentRegion.Sort _
                            key1:=.Range("A1"), _
                            order1:=xlAscending, _
                            Header:=xlYes
                End With
            End If
        Next lngSheet
    Next xlSheet
    Me.Hide
End Sub

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.