Beginner needs code to be proof read (there ARE problems)

I everyone. New here and learning the ropes of VBA for excel.

I'm dealing with two obstacles in two separate spreadsheets/subroutines, which are really bugging me. I've attempted to write out the code for each subroutine, but it doesn't do what I want it to do. Maybe you guys can help?

I've attached a zip file to this post. If possible, and I say this with the greatest amount of humility possible, can you guys just proofread and amend my current code where necessary. There are still things I need to learn, but if you can correct each subroutine while preserving my initial approach or design (that is, if possible), I would be eternally grateful.

Cities.xls - This lists where a company has offices. I'm want the subroutine to 1) Sort the cities in alphabetical order (this occurs at the end of the code), 2) for each of the cities listed in "AllCities" worksheet, check whether there is a additional corresponding worksheet of the same city name, and if there isn't one, the subroutine would automatically add it, and 3) delete any worksheet of a city name that is NOT found in the listings found in the "AllCities" worksheet.

Hope that didn't sound too complicated. Check the spreadsheet to fully understand. My code will probably look juvenile and even laughable. I had problems with ranges and establishing variable objects, I guess.

HiSpenders.xls - This is an array situtation, which attempts to filter the higher spenders out of a list of people. The spreadsheet contains a list of customers and the amounts they spent in a certain period of time. I wrote a subroutine in trying capture the existing list then create two arrays of customer names and amounts spent for customers over a certain amount of money. I added an input box which asks the user to input the lowest spending limit that they want to filter out of the list. The filtered arrays are then transfered to the nearby columns.

There might be some redundant code in there - please feel free to delete as necessary. I've included comments to show my thought processes. For some reason, the arrays just never show up in the new columns. I can't figure it out.

Thank you one and all. I look forwards to your responses!

~Dcase

Hi

I don't if this helps for your workbook named cities.xls and will get back to you on the other issue

Sub list_worksheet()
'Clear column a
rowcount = Cells(Cells.Rows.Count, "a").End(xlUp).Row
Range("a3:a" & rowcount).ClearContents
Range("a3").Select
'loop each worksheet, write info starting range a3
Set ArrFeuil = Sheets("AllCities")
j = 3
For i = 2 To ActiveWorkbook.Sheets.Count
ArrFeuil.Cells(j, 1).Value = Sheets(i).Name
j = j + 1
Next i
'sort name
rowcount = Cells(Cells.Rows.Count, "a").End(xlUp).Row
Range("A3:A" & rowcount).Select
Selection.Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlNo, _
orderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A2").Select
'set range
Range("A3:A" & rowcount).Select
ActiveWorkbook.Names.Add Name:="Cities", RefersToR1C1:="=AllCities!R3C1:R" & rowcount & "C1"
Range("a2").Select
End Sub

Thank you! I'll plug this in and see if it helps!

As for as for the HiSpenders.xls
Try this code and use the spreadsheet attached for the layout
Sub autofilter()
'check to see if filter is on
If ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If
rowcount = Cells(Cells.Rows.Count, "a").End(xlUp).Row
line1: ' go back to this line if if entry is not numeric
strname = InputBox(Prompt:="Enter the lowest spending limit for your search.", _
Title:="Spending", Default:="Enter your limit here")
If IsNumeric(strname) Then
strname1 = ">" & strname
Range("b2").Select
ActiveCell.Value = strname1
Range("A1:B" & rowcount).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("A1:B2"), Unique:=False
'ActiveSheet.ShowAllData
Else
MsgBox "You must entered a numeric value"
GoTo line1
End If
End Sub