Excel VBA Guru's help needed.

[wutamess]

I'm getting a debug error in this code: At th e End with statement. I hit continue and it continues and processes the next section but the debug error is killing me.

Basicallym I'm trying to sort 2 different sets of columns inside the same actice sheet.

Sub SortBEandGJ()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+n
'
Columns("B:E").Select
ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:=Range("B2:B100"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.ActiveSheet.Sort
.SetRange Range("B1:E100")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Columns("G:J").Select
ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:=Range("G2:G100"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.ActiveSheet.Sort
.SetRange Range("G1:J100")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A2").Select
End Sub

[scho63]

I need Pivot Table / Data sort help and appreciate anyone who can assist. I've watched about 25 Youtube videos and cannot find the correct process on how to do this.

I'm a B- with Pivot Tables. I have one created and got a bunch of data I needed but one thing I am stuck with is how to group a range of numbers and then the sum of how MANY of those numbers

Exact need:
- I have list of 12,000 records with a list of states in one column and the number of Units they have in the other
- I want to group by States and THEN have a grouping / filter by the segments below to have a total

• Power Units 50-100
• Power Units 101-250
• Power Units 250-500
• Power Units 500-1000
• Power Units > 1001

The end result would be like this
Alaska
Power Units 50-100 12
Power Units 101-250 6
Power Units 250-500 4
Power Units 500-1000 1
Power Units 1001 0
Alabama
Power Units 50-100 44
Power Units 101-250 12
Power Units 250-500 3
Power Units 500-1000 5
Power Units 1001 2

Can anyone solve my dilemma?

[Mephistopheles Janx]

Originally Posted by scho63

I need Pivot Table / Data sort help and appreciate anyone who can assist. I've watched about 25 Youtube videos and cannot find the correct process on how to do this.

I'm a B- with Pivot Tables. I have one created and got a bunch of data I needed but one thing I am stuck with is how to group a range of numbers and then the sum of how MANY of those numbers

Exact need:
- I have list of 12,000 records with a list of states in one column and the number of Units they have in the other
- I want to group by States and THEN have a grouping / filter by the segments below to have a total

• Power Units 50-100
• Power Units 101-250
• Power Units 250-500
• Power Units 500-1000
• Power Units > 1001

The end result would be like this
Alaska
Power Units 50-100 12
Power Units 101-250 6
Power Units 250-500 4
Power Units 500-1000 1
Power Units 1001 0
Alabama
Power Units 50-100 44
Power Units 101-250 12
Power Units 250-500 3
Power Units 500-1000 5
Power Units 1001 2

Can anyone solve my dilemma?

Per Chat GPT:

To achieve the desired result with a Pivot Table in Excel, you can follow these steps:

Open your Excel file with the data.
Select the range of data, including the column headers. In your case, select both the "State" column and the "Units" column.
Go to the "Insert" tab in the Excel ribbon and click on "PivotTable" (in the "Tables" group). The "Create PivotTable" dialog box will appear.
Make sure the "Select a table or range" option is selected and that the correct range is displayed in the "Table/Range" field. Also, choose where you want to place the PivotTable (e.g., in a new worksheet or an existing one).
Click "OK" to create the PivotTable.
You should now see the PivotTable Field List on the right side of the Excel window.
In the PivotTable Field List, drag the "State" field to the "Rows" area.
Drag the "Units" field to the "Values" area.
In the "Values" area, you should see "Sum of Units" (or similar). Click on the drop-down arrow next to it and choose "Value Field Settings."
In the "Value Field Settings" dialog box, select the "Sum" function (which should be the default) and click "OK."
Now, to group the units into the desired segments, right-click on any value in the "Sum of Units" column in the PivotTable and select "Value Field Settings" again.
In the "Value Field Settings" dialog box, click on the "Number Format" button.
In the "Number" tab of the "Format Cells" dialog box, choose "Custom" from the Category list.
In the "Type" field, enter the following custom format: "Power Units 0-100;;Power Units 101-250;;Power Units 251-500;;Power Units 501-1000;;Power Units > 1000"
Click "OK" in the "Format Cells" dialog box, and then click "OK" again in the "Value Field Settings" dialog box.
Now, your PivotTable should display the states grouped by the specified segments, with the corresponding sums next to each segment.

Note: If your Excel version doesn't support the custom format specified in step 14, you may need to modify the format to suit your version.

[scho63]

Originally Posted by Mephistopheles Janx

Per Chat GPT:
To achieve the desired result with a Pivot Table in Excel, you can follow these steps:

Thanks-I'll give it a whirl.

[Mephistopheles Janx]

Originally Posted by wutamess

I'm getting a debug error in this code: At th e End with statement. I hit continue and it continues and processes the next section but the debug error is killing me.

Basicallym I'm trying to sort 2 different sets of columns inside the same actice sheet.

Sub SortBEandGJ()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+n
'
Columns("B:E").Select
ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:=Range("B2:B100"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.ActiveSheet.Sort
.SetRange Range("B1:E100")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Columns("G:J").Select
ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:=Range("G2:G100"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.ActiveSheet.Sort
.SetRange Range("G1:J100")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A2").Select
End Sub

The debug error you're encountering is likely due to the incorrect use of the Sort object. The Sort object is used to sort a range of data, but in your code, you're trying to sort columns directly without specifying a range.

Here's the corrected version of your code:

Sub SortBEandGJ()
Dim ws As Worksheet
Dim sortRange As Range

Set ws = ActiveWorkbook.ActiveSheet

' Sort columns B to E
Set sortRange = ws.Range("B1:E100")
With ws.Sort
.SortFields.Clear
.SortFields.Add Key:=sortRange.Columns(1), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
.SetRange sortRange
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

' Sort columns G to J
Set sortRange = ws.Range("G1:J100")
With ws.Sort
.SortFields.Clear
.SortFields.Add Key:=sortRange.Columns(1), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
.SetRange sortRange
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

ws.Range("A2").Select
End Sub


/also Chat GPT
//hope this info getting to you 12.5 years later wasn't too inconvenient