sort sheets using macro ?

[rickyfish]

Hi again

made the changes you suggested

Thanks for taking the time to look at this.

I am still getting errors when I run this
see starred comment
I must be doing something wrong.

Your comment re bad data structure unfortunately the imported file comes that way I suppose I could add a line to the macro to insert a heading above the data on c4 called WSID

Sheets("2").Name = "NAR"
    Sheets("3").Name = "SRV"
    Sheets("4").Name = "SAR"
    Sheets("5").Name = "ERV"
    Sheets("6").Name = "WRV"
    Sheets("7").Name = "A92"
    Sheets("8").Name = "LAN"
    Sheets("9").Name = "OVH"
    Sheets("10").Name = "NEN"
    Sheets("11").Name = "SEN"
    Sheets("12").Name = "PLP"
    Sheets("13").Name = "M80"
    Sheets("15").Name = "NMI"
    Sheets("16").Name = "SMI"
    Sheets("19").Name = "NWC"
    Sheets("20").Name = "SWC"
    Sheets("23").Name = "NBR"
    Sheets("24").Name = "SBR"
    Sheets("26").Name = "NRV"
    Sheets("27").Name = "LIG"
        
     Dim wsEachSheet             As Worksheet
    Dim IngLastRow              As Long

    For Each wsEachSheet In Worksheets
        With wsEachSheet
            IngLastRow = .Range("A" & .Rows.Count).End(xlUp).Row
            
            .Range("AN:AP,AR:AT,AW:BA").Interior.ColorIndex = 34
            .Range("AN:AP,AR:AT,AW:BA").Pattern = xlSolid
  * Invalid use of property*   .Range ("BC:BE,BV:BV,BX:BZ,CC:CD,CG:CK,CM:CM")
                .FormatConditions.Delete
                .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="0"
                .FormatConditions(1).Font.ColorIndex = xlAutomatic
                .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="0"
                .FormatConditions(2).Font.ColorIndex = 5
                .FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="0"
                .FormatConditions(3).Font.ColorIndex = 3
            
           
    
            Columns ("BF:BF")
            FormatConditions.Delete
            FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
            Formula1:="=$BH5"
            FormatConditions(1).Font.ColorIndex = 10
            FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
            Formula1:="=$BH5"
            FormatConditions(2).Font.ColorIndex = 9
            

            .Range("C5:C" & IngLastRow).Formula = "=VLOOKUP(RC[-2],'[3G lOOKUP.xls]ALL'!C3:C5,3,FALSE)"
            .Range("U5:U" & IngLastRow).Formula = "=VLOOKUP(RC[-2],'[3G lOOKUPV.xls]ALL'!C3:C5,6,FALSE)"
            .Range("T5:T" & IngLastRow).Formula = "=IF(RC[-10]=0,IF(R[1]C[-3]=0,0,1),1)"
            .Range("A5:CM5" & IngLastRow).Sort Key1:=.Range("C5"), Order1:=xlAscending, Header:=xlNo, _
                OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
                DataOption1:=xlSortTextAsNumbers
            .Range("A5:CM5" & IngLastRow).Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(4, 5, 6, 7, _
                8, 9, 10, 11, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 24, 25, 26, 27, 28, 30, 32, 33, 34, 35, 36, 37, 38, 40, 41, 42, 43, 44, 45, 46, 47, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 60, 61, 62, 63, 64, 65, 66, 68, 69, 70, 71, 72, 73, 74, 76, 77, 78, 79, 80, 81, 82, 83, 85, 86, 87, 88, 89, 90, 91), _
                Replace:=True, PageBreaks:=False, SummaryBelowData:=True

            .Rows("4:4").AutoFilter
            .Range("T5:T" & IngLastRow).AutoFilter Field:=20, Criteria1:="1"
            
            Columns("D:AL").Select
    Selection.EntireColumn.Hidden = True
    
        End With
    Next
    
    End Sub

Seriously beginning to wonder if I am ever going to get this to work, I am positive it can be done but having spent hours and hours poring over this and still not getting the desired result am definately becoming disheartened.

Hats off to you guys who can understand and make this stuff work


Thanks again

Richard

[GTO]

Greetings,

You may have glossed over Zack's request...

I added CODE tags to your code. Please use them in the future for readability purposes.
...

The code tags really do make reading the post much easier

[code] '//Your code between the tags// [/code]

Mark

[rickyfish]

Sorry

code tags ???

I dont know what these are.

All and any help gratefully recieved

Richard

[GTO]

Hi Richard,

The code tags are simply "tags" between brackets that are interpreted when you submit the post. In the line before my name in my last post, I showed how you would type the tags to get your post to render correctly. Does that make sense?

Mark

Edit:

If my verbiage is a bit confusing, try this.

Click the "quick reply" icon, then the 'Go Advanced' button. See the little pound sign in the upper right? Click this and you'll see the CODE tags inserted. Your code simply goes between the start/end tags.

[rickyfish]

Thanks for info.

I understand now
will use them in future

Rickf

[rickyfish]

Hi again

below is further attempt at code
I have entered the error messages I get
all of these appear before anything is done on the sheets
I had to change it a bit cos I read that headings were required for subtotal to work which meant delete row 4 and rewrite all refs to row5 as 4
I am really struggling to get this working

I need it to do the following

Downloaded file from External source has 24 to 27 sheets (200+mb)
all data needs sorted by col c (info from vlookup)
all data then subtotalled by col c
data col u from vlookupv
formula entered into col t (current info =1)
autofilter col t by 1 to show only current data
various conditional formatting to highlight variances

all sheets have varying amounts (rows) of data

Thanks for help already given
Sorry to keep asking

 '//YSub step1()
'
' step1 Macro
' rename tabs and reformat columns
'
' Keyboard Shortcut: Ctrl+a
'
    
    Sheets("2").Name = "NAR" SUBSCRIPT OUT OF RANGE
    Sheets("3").Name = "SRV"
    Sheets("4").Name = "SAR"
    Sheets("5").Name = "ERV"
    Sheets("6").Name = "WRV"
    Sheets("7").Name = "A92"
    Sheets("8").Name = "LAN"
    Sheets("9").Name = "OVH"
    Sheets("10").Name = "NEN"
    Sheets("11").Name = "SEN"
    Sheets("12").Name = "PLP"
    Sheets("13").Name = "M80"
    Sheets("15").Name = "NMI"
    Sheets("16").Name = "SMI"
    Sheets("19").Name = "NWC"
    Sheets("20").Name = "SWC"
    Sheets("23").Name = "NBR"
    Sheets("24").Name = "SBR"
    Sheets("26").Name = "NRV"
    Sheets("27").Name = "LIG"
        
     Dim wsEachSheet             As Worksheet
    Dim IngLastRow              As Long

    For Each wsEachSheet In Worksheets
        With wsEachSheet
            IngLastRow = .Range("A" & .Rows.Count).End(xlUp).Row
            
            
            
                       

            .Range("C4:C" & IngLastRow).Formula = "=VLOOKUP(RC[-2],'[3G lOOKUP.xls]ALL'!C3:C5,3,FALSE)"
            .Range("U4:U" & IngLastRow).Formula = "=VLOOKUP(RC[-2],'[3G lOOKUPV.xls]ALL'!C3:C5,6,FALSE)"
            .Range("T4:T" & IngLastRow).Formula = "=IF(RC[-10]=0,IF(R[1]C[-3]=0,0,1),1)"
            .Range("A4:CM4" & IngLastRow).Sort Key1:=.Range("C3"), Order1:=xlAscending, Header:=xlNo, _
                OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
                DataOption1:=xlSortTextAsNumbers
 Run time error 1004
Subtotal method of range class failed           .Range("A4:CM4" & IngLastRow).Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(4, 5, 6, 7, _
                8, 9, 10, 11, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 24, 25, 26, 27, 28, 30, 32, 33, 34, 35, 36, 37, 38, 40, 41, 42, 43, 44, 45, 46, 47, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 60, 61, 62, 63, 64, 65, 66, 68, 69, 70, 71, 72, 73, 74, 76, 77, 78, 79, 80, 81, 82, 83, 85, 86, 87, 88, 89, 90, 91), _
                Replace:=True, PageBreaks:=False, SummaryBelowData:=True

            .Rows("4:4").AutoFilter
            .Range("T5:T" & IngLastRow).AutoFilter Field:=20, Criteria1:="1"
            
            With wsEachSheet
                .Range("AN:AP,AR:AT,AW:BA").Interior.ColorIndex = 34
                .Range("AN:AP,AR:AT,AW:BA").Pattern = xlSolid
                .Range ("BC:BE,BV:BV,BX:BZ,CC:CD,CG:CK,CM:CM") Compile Error Invalid use of property

                .FormatConditions.Delete
                .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="0"
                .FormatConditions(1).Font.ColorIndex = xlAutomatic
                .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="0"
                .FormatConditions(2).Font.ColorIndex = 5
                .FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="0"
                .FormatConditions(3).Font.ColorIndex = 3
                     
            With Columns("BF:BF")
            FormatConditions.Delete
            FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
            Formula1:="=$BH5"
            FormatConditions(1).Font.ColorIndex = 10
            FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
            Formula1:="=$BH5"
            FormatConditions(2).Font.ColorIndex = 9
            Columns ("D:AL") Compile Error Invalid use of Property

            .EntireColumn.Hidden = True
            End With
            End With
        End With
    Next
    
    End Sub//

[Zack Barresse]

This part...

            With Columns("BF:BF")
            FormatConditions.Delete
            FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
            Formula1:="=$BH5"
            FormatConditions(1).Font.ColorIndex = 10
            FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
            Formula1:="=$BH5"
            FormatConditions(2).Font.ColorIndex = 9
            Columns ("D:AL") Compile Error Invalid use of Property

            .EntireColumn.Hidden = True
            End With

Is not being referenced by the With statement, because you have no preceding periods. You would need a preceding period before each "Columns" and "FormatConditions" because they are With the data range specified. If you don't it will be referencing the active worksheet (whatever one is selected).

Also, your error line should be utilizing the Range object, not a Columns object, which is expecting a numerical value. You also need to do something with it. I'm assuming you're wanting to hide it, utilizing the next line of code, so combine them.

You would need to replace that code with something like this...

            With .Range("BF:BF")
                .FormatConditions.Delete
                .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=$BH5"
                .FormatConditions(1).Font.ColorIndex = 10
                .FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="=$BH5"
                .FormatConditions(2).Font.ColorIndex = 9
                .Range("D:AL").EntireColumn.Hidden = True            
            End With

[rickyfish]

Thank you again for your help.
I will adjust the code this afternoon and try it again.
Still cant get the Subtotal part to work though.
Also how do I copy the code to a different sheet I have several untouched versions of the worksheet for testing and dont know how to attach this code to them, are there any problems with doing this, there are also parts of the macro that would work for some of the other sheets we use.
Hopefully I can get this to work soon

Thanks
Rick

[Zack Barresse]

You're getting the error message because there is no sheet named "2" in the active workbook. Can you post a small subset of your data? If not, can you describe in detail your data structure? Include headers, data row start, etc. It's difficult to help you when we don't know anything about your data.

[rickyfish]

Hi

here is a small portion of one sheet, hope this helps

Excel 2003

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL
1	SE Planned Maint As of Rights			Period																				April  2007 To Date
2				SE	External		Nett				Total				Plant &		Sub -	Direct	Nett Gross	Gross	Gross	Original		SE	External					Total				Plant &		Sub -	Direct	Gross
3			WSID	Sales	Sales	PFF	Sales	Contribut	WIP	OIP	Sales		Labour	TCharge	Haulage	Materials	Contractors	Costs	Margin	after Adj	Margin	Value		Sales	Sales	PFF	Contribut	WIP	OIP	Sales		Labour	TCharge	Haulage	Materials	Contractors	Costs	Margin
4	Actual
5	BADDEBT	BADDEBT					0				0							0	0	0	0		0		-443					-443							0	-443
6	DTCP INCOME	DTCP INCOME					0				0							0	0	0	0		0		14					14							0	14
7	LUMP SUM	Lump Sum					0				0							0	0	0	0		0	404,532						404,532							0	404,532
8	LUMP SUM INCOM						0				0							0	0	0	0		0	20,471		1,650				22,120							0	22,120
9	LUMPSUM	Lump Sum					0				0							0	0	0	0		0	105,778						105,778							0	105,778
10	RELEASE						0				0							0	0	0	0		0							0						-18,500	-18,500	18,500
1213	SE78671	A720 Lasswade EB off slip patching TBA		61			61				61							0	61	61	61		1	11,526		2,742	-119			14,148						13,251	13,251	897
1214	SE78673	A1 Wallyford Southbound Off Slip  TBA Machine P					0				0							0	0	0	0		0	42,104		9,293	-264			51,134						42,121	42,121	9,013
1215	SE78694	A1 Heughhead Farm  Site Investigation					0				0							0	0	0	0		0	2,972		401	-707			2,666			338		3	853	1,194	1,472
1216	SE78695	A1 Heughhead Farm  Design structural inlay					0				0							0	0	0	0		0	706		95	-424			378			258				258	120
1217	SE78706	A720 Dreghorn WB Off Slip TBA Machine patching					0				0							0	0	0	0		0	30,330		6,519	-742			36,107						30,578	30,578	5,529
1218	SE78766	A1 Spott Roundabout to Little Pinkerton SB  Struc					0				0							0	0	0	0		0	135,580		22,293	278			158,151						107,863	107,863	50,288
1219	SE78767	A720 Old Craighall to Sheriffhall Mains WB  TBA					0				0							0	0	0	0		0	164,277		30,660	3,549			198,486						157,979	157,979	40,507
1220	SE78786	A702 Backsheils to Candymill  TBA 50mm Inlay 1					0				0							0	0	0	0		0	73,915		19,028	-1,596			91,347						77,390	77,390	13,957
1221	SE78842	A7 Hawick to Selkirk Road stud upgrade					0				0							0	0	0	0		0	13,446		2,753				16,199						15,716	15,716	483
1222	SE78843	A1 Cove  105 45mm Inlay					0				0							0	0	0	0		0	155,631		44,311	-7,490			192,452			52			164,414	164,466	27,986
1223	SE78866	Patching flexible roads  Reactive Category 1 and					0				0							0	0	0	0		0	2,084						2,084						2,033	2,033	51
1224	SE78887	A702 Wandel  Coring investigation					0				0							0	0	0	0		0	1,992		271	-379			1,884			115		3	519	638	1,247
1225	SE78888	A7 Eweslees Farm Pavement Investigation					0				0							0	0	0	0		0	1,693		230	-340			1,584			118		2	528	648	936
1226	SE78891	Design A68 Sorrowlessfield Resurfacing					0				0							0	0	0	0		0	208		29	-125			112			56				56	56
1227	SE78892	A68 Sorrowlessfield Site Investigation					0				0							0	0	0	0		0	1,455		198	-355			1,298			126			474	600	698
1228	SE78927	A68 Jedburgh 120mm Inlay 320m2 100mm patching		16,183			16,183				16,183						15,788	15,788	395	395	395		1	177,780		29,435	-17,298			189,917						165,230	165,230	24,687
1229	SE78929	Design A702 Wandel  Structural inlay					0				0							0	0	0	0		0	332		47	-199			179			90				90	89
1230	SE78930	Design A7 Eweslees Farm Inlay					0				0							0	0	0	0		0	272		38	-163			147			79				79	67
1231	SE78950	A1 Redhall and Ayton Junctions Red Surfacing and R					0				0							0	0	0	0		0	14,337		1,919	107			16,363						16,668	16,668	-305
1232	SE78973	A6091 Borders General Hospital Right Turn Prohibit					0				0							0	0	0	0		0	480		95	8			584						758	758	-174
1233	SE79002	Road markings and reflector studs Reactive Categ		641		134	774	-389			385						605	605	170	170	-219		1	641		134	-389			385						605	605	-219
1234	SE79183	A702 Hillend    TBA  100mm inlay  320m2 100mm patc		2,419		30	2,449	-8			2,441						2,212	2,212	237	237	230		1	140,071		35,137	-3,523			171,685						151,968	151,968	19,717
1235	SE79295	Road markings and reflector studs Reactive Categ		253		53	306	-48			258						493	493	-187	-187	-235		1	253		53	-48			258						493	493	-235
1236	SE79296	Road markings and reflector studs Reactive Categ		838		175	1,013	-106			907						907	907	106	106	0		1	838		175	-106			907						907	907	0
1237	SE79333	A702 Abington Roundabouts Inlay TBA		87,820		15,744	103,565	-1,545			102,020						83,452	83,452	20,113	20,113	18,568		1	87,820		15,744	-1,545			102,020						83,452	83,452	18,568
1238	SE79380	A720 Gilmerton EB off slip patching design		519		74	593	-312			281			141				141	452	452	140		1	519		74	-312			281			141				141	140
1239	SE79383	Road markings and reflector studs Reactive Categ		53		11	64	-11			53						250	250	-186	-186	-197		1	53		11	-11			53						250	250	-197
1240	SE80107	BEAR has identified outstanding works from their s					0				0							0	0	0	0		0					1,300		1,300							0	1,300
1241	SE90070	Installation of 10 New Gullies					0				0							0	0	0	0		0	2,265		59	431			2,755		3,494		1,260	717		5,471	-2,715
1242	SE90131	M8 Deans Road Bridge Design for bridge deck joints					0				0							0	0	0	0		0	2,579		365	-1,547	-2,579		-1,182			63				63	-1,245
1243	SE90174	Surface treatment					0				0							0	0	0	0		0							0		171					171	-171
1244	SE90175	not known					0				0							0	0	0	0		0							0		137					137	-137
1245	SE90176	Design of surface treatment for A68 Glendouglas					0				0							0	0	0	0		0							0		171					171	-171
1246	SE90177	Surface treatment					0				0							0	0	0	0		0							0		206					206	-206
1247	SE90205	Design for M9 Breastmill to J1A carriageway recons					0				0							0	0	0	0		0	2,536		78	-1,522			1,092			943				943	149
1248	SE90246	Construction of hatching   ladder markings between					0				0							0	0	0	0		0	12,230		386				12,616		73				18,448	18,521	-5,904
1249	SE90317	M8 Junction sA WB exit slip  Construction of remed					0				0							0	0	0	0		0	7,169		415	397			7,981						9,569	9,569	-1,588
1250	SE90326	A80 Old Inns to Castlecary  Temporary Widening Rei					0				0							0	0	0	0		0					192		192							0	192
1251	SE90359	A720 Dreghorn carriageway reprofiling					0				0							0	0	0	0		0	75,599		3,840	-3,224			76,216					1,440	78,000	79,440	-3,224
1252	SE90374	A80 Old Inns to Castlecary   Temporary Widening					0				0							0	0	0	0		0	-1,721		1,721		0		0							0	0
1253	SE90395	A68 Huntsford Bends   Construction of remedial Mea					0				0							0	0	0	0		0	37,474		1,484	1,912	5,225		46,095		2,051	88	1,702	33,947	11,174	48,962	-2,867
1254	SE90399	A1 Houndwood					0				0							0	0	0	0		0	31,733						31,733						31,733	31,733	0
1255	SE90416	A1 Spott Road Roundabout  Approaches to roundabout					0				0							0	0	0	0		0	69,780		5,080	-214			74,645			256			69,731	69,987	4,658
1256	SE90417	A1 Thistly Cross Roundabout  NB and SB Approaches					0				0							0	0	0	0		0	58,050		4,211	808			63,069			21			61,704	61,725	1,344
1257	SE90444	A68 Bends north of Earlston  Construction of AIP					0				0							0	0	0	0		0	13,898		1,268	-399			14,768				531		15,382	15,913	-1,145
1258	SE90623	A7 Opposite access to Branxholm Park Construction					0				0							0	0	0	0		0							0				7,341			7,341	-7,341
1259	SE90631	M9 Junction 10 Craigforth  slip roads accident re					0				0							0	0	0	0		0	89,767		11,021	-108			100,680					5	116,762	116,767	-16,086
1260	SE906331						0				0							0	0	0	0		0							0						-14,569	-14,569	14,569
1261	SE90666	A702 Crosshouses to Easter Howgate Resurfacing Des		287		41	328	-172			155			103				103	225	225	53		1	8,350		818	-5,010			4,158			3,006				3,006	1,153
1262	SE90691	A7 Commercial Road Hawick  PUFFIN Crossing Includ					0				0							0	0	0	0		0			-1,313	331			-982							0	-982
1263	SE90692	A7 Commercial Road Hawick PUFFIN Crossing Civils					0				0							0	0	0	0		0	6,730	8,000	2,481	311			17,522		965		152	131	12,955	14,203	3,319
1264	SE90741	A7 Opposite access to Branxholm Park Environmental					0				0							0	0	0	0		0	1,084		101	124	-1,084		226							0	226
1265	SE90748	M9 Cambusbarron Boundary Fencing Design					0				0							0	0	0	0		0	3,032		293	-1,819			1,506			1,032				1,032	474
1266	SE90769	A68 North of Pathhead Lining Signing and Road Stud					0				0							0	0	0	0		0	14,225		2,006	2,367			18,598			1,114			14,673	15,787	2,811

4

so this sheet has 1337 rows of data

it is in 3 sections A-AL BASE DATA
AN-BV is input section ie copy of D-AL where data in columns an-ap ar-at aw-ba can be changed the changes are reflected in bh-bm and bp-bt as these are cum figures
third section bx-cm is current movement from base d-q

as there are many rows with no data in current period d-q the sheets need sorted for current data re formula in col w
they then need sorted by look up value in col c
then subtotalled by code in col c
and a value needs input from lokup in col v

there are a number of blank columns am,av,bg,bo,bw,cf
purelyto split sections of the sheet up.

Does this help any

Thanks
Rick