extremely new to VBA coding and trying to figure out how to edit the code on my macro that I recorded.
Background:
Recorded some steps to my macro that filters and sorts through data
Here is the code for what was recorded:
Sub Macro6()
'
' Macro6 Macro
'
' Keyboard Shortcut: Ctrl+l
'
Range("A2:L2").Select
Selection.AutoFilter
ActiveSheet.Range("$A$2:$L$18").AutoFilter Field:=1, Criteria1:="NW EMER"
ActiveSheet.Range("$A$2:$L$18").AutoFilter Field:=7, Criteria1:="CBC7"
ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add2 Key:= _
Range("L2:L18"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
Now to this code, I want to add another “step” to the macro in which it would then analyze the data that it just filtered through and on a new sheet report to me how many values there are in column L AND how many of those values are equal to or less than a value. I tried asking AI to write the code for me (ChatGPT and Claude), but regardless of how many times I ask to the AI to fix the code it wrote my macro does not function correctly and just does this after asking me to select the data:
PHOTO OF ERROR
Here is the code that Claude last wrote:
Sub Macro6()
' Filter the data
Range("A3:L3").AutoFilter
ActiveSheet.Range("$A$3:$L$19").AutoFilter Field:=1, Criteria1:="NW EMER"
ActiveSheet.Range("$A$3:$L$19").AutoFilter Field:=7, Criteria1:="CBC7"
' Sort the filtered data
ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add2 Key:=Range("L3:L19"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
' Count the number of values less than or equal to 45 in column L
Dim countLessEqualTo45 As Long
countLessEqualTo45 = 0
Dim totalCount As Long
totalCount = 0
Dim lastRow As Long
lastRow = ActiveSheet.Cells(Rows.Count, "L").End(xlUp).Row
For i = 3 To lastRow
If ActiveSheet.Cells(i, "L").Value <= 45 Then
countLessEqualTo45 = countLessEqualTo45 + 1
End If
totalCount = totalCount + 1
Next i
' Create a new sheet and record the counts
Dim newSheet As Worksheet
Set newSheet = ActiveWorkbook.Sheets.Add(After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count))
newSheet.Name = "Counts"
newSheet.Range("A1").Value = "Count of values less than or equal to 45 in column L"
newSheet.Range("A2").Value = countLessEqualTo45
newSheet.Range("B1").Value = "Total count of values in column L"
newSheet.Range("B2").Value = totalCount
End Sub
What I don’t understand is how does this even happen? I don’t even have anything within my code that would change table design????
Here is also a screenshot of the raw data within my excel spreadsheet:
RAW DATA
I tried to double check the range of the rows and everything seemed like it matched?
GreenPen74 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.