Refining Code

J

jadie_munchkin

New Member
Joined
Dec 18, 2011
Messages
23
Hi,

Can someone plese help me. I have created a macro to do some formatting for me however it is very long and inneffective.(I am only new to macro writing) it takes ages to cycle through i have copied and pasted it below if someone can please help me it would be a life saver.


I have a fantastic macro that was written for me by another member and it works seemlessly. it is just in the wrong formatt. so I created the below macro to formatt it. I have formatted it for the whole 80 segments that is what is the max number of segments aviable at this stage however our estimates team may only need one or two or the whole 80. i would really appricate some help i have tried a couple of macros that insert lines for me and transpose the data as well however they are even less efficent than what the one i have created is. Help would really be appricated

Range("B42:H120").Select
Selection.Cut
Range("B47").Select
ActiveSheet.Paste

Range("B48:H125").Select
Selection.Cut
Range("B53").Select
ActiveSheet.Paste

Range("B54:H130").Select
Selection.Cut
Range("B59").Select
ActiveSheet.Paste

Range("B60:H135").Select
Selection.Cut
Range("B65").Select
ActiveSheet.Paste

Range("B66:H140").Select
Selection.Cut
Range("B71").Select
ActiveSheet.Paste

Range("B72:H145").Select
Selection.Cut
Range("B77").Select
ActiveSheet.Paste

Range("B78:H150").Select
Selection.Cut
Range("B83").Select
ActiveSheet.Paste

Range("B84:H155").Select
Selection.Cut
Range("B89").Select
ActiveSheet.Paste

Range("B90:H160").Select
Selection.Cut
Range("B95").Select
ActiveSheet.Paste

Range("B96:H165").Select
Selection.Cut
Range("B101").Select
ActiveSheet.Paste

Range("B102:H170").Select
Selection.Cut
Range("B107").Select
ActiveSheet.Paste

Range("B108:H175").Select
Selection.Cut
Range("B113").Select
ActiveSheet.Paste

Range("B114:H180").Select
Selection.Cut
Range("B119").Select
ActiveSheet.Paste

Range("B120:H185").Select
Selection.Cut
Range("B125").Select
ActiveSheet.Paste

Range("B126:H190").Select
Selection.Cut
Range("B131").Select
ActiveSheet.Paste

Range("B132:H195").Select
Selection.Cut
Range("B137").Select
ActiveSheet.Paste

Range("B138:H200").Select
Selection.Cut
Range("B143").Select
ActiveSheet.Paste

Range("B144:H205").Select
Selection.Cut
Range("B149").Select
ActiveSheet.Paste

Range("B150:H210").Select
Selection.Cut
Range("B155").Select
ActiveSheet.Paste

Range("B156:H215").Select
Selection.Cut
Range("B161").Select
ActiveSheet.Paste

Range("B162:H220").Select
Selection.Cut
Range("B167").Select
ActiveSheet.Paste

Range("B168:H225").Select
Selection.Cut
Range("B173").Select
ActiveSheet.Paste

Range("B174:H230").Select
Selection.Cut
Range("B179").Select
ActiveSheet.Paste

Range("B180:H235").Select
Selection.Cut
Range("B185").Select
ActiveSheet.Paste

Range("B186:H240").Select
Selection.Cut
Range("B191").Select
ActiveSheet.Paste

Range("B192:H245").Select
Selection.Cut
Range("B197").Select
ActiveSheet.Paste

Range("B198:H250").Select
Selection.Cut
Range("B203").Select
ActiveSheet.Paste

Range("B204:H255").Select
Selection.Cut
Range("B209").Select
ActiveSheet.Paste

Range("B210:H260").Select
Selection.Cut
Range("B215").Select
ActiveSheet.Paste

Range("B216:H265").Select
Selection.Cut
Range("B221").Select
ActiveSheet.Paste

Range("B222:H270").Select
Selection.Cut
Range("B227").Select
ActiveSheet.Paste

Range("B228:H275").Select
Selection.Cut
Range("B233").Select
ActiveSheet.Paste

Range("B234:H280").Select
Selection.Cut
Range("B239").Select
ActiveSheet.Paste

Range("B240:H285").Select
Selection.Cut
Range("B245").Select
ActiveSheet.Paste

Range("B246:H290").Select
Selection.Cut
Range("B251").Select
ActiveSheet.Paste

Range("B252:H295").Select
Selection.Cut
Range("B257").Select
ActiveSheet.Paste

Range("B258:H300").Select
Selection.Cut
Range("B263").Select
ActiveSheet.Paste

Range("B264:H305").Select
Selection.Cut
Range("B269").Select
ActiveSheet.Paste

Range("B270:H310").Select
Selection.Cut
Range("B275").Select
ActiveSheet.Paste

Range("B276:H315").Select
Selection.Cut
Range("B281").Select
ActiveSheet.Paste

Range("B282:H320").Select
Selection.Cut
Range("B287").Select
ActiveSheet.Paste

Range("B288:H325").Select
Selection.Cut
Range("B293").Select
ActiveSheet.Paste

Range("B294:H330").Select
Selection.Cut
Range("B299").Select
ActiveSheet.Paste

Range("B300:H335").Select
Selection.Cut
Range("B305").Select
ActiveSheet.Paste

Range("B306:H340").Select
Selection.Cut
Range("B311").Select
ActiveSheet.Paste

Range("B312:H345").Select
Selection.Cut
Range("B317").Select
ActiveSheet.Paste

Range("B318:H350").Select
Selection.Cut
Range("B323").Select
ActiveSheet.Paste

Range("B324:H355").Select
Selection.Cut
Range("B329").Select
ActiveSheet.Paste

Range("B330:H360").Select
Selection.Cut
Range("B335").Select
ActiveSheet.Paste

Range("B336:H365").Select
Selection.Cut
Range("B341").Select
ActiveSheet.Paste

Range("B342:H370").Select
Selection.Cut
Range("B347").Select
ActiveSheet.Paste

Range("B348:H375").Select
Selection.Cut
Range("B353").Select
ActiveSheet.Paste

Range("B354:H380").Select
Selection.Cut
Range("B359").Select
ActiveSheet.Paste

Range("B360:H385").Select
Selection.Cut
Range("B365").Select
ActiveSheet.Paste

Range("B366:H390").Select
Selection.Cut
Range("B371").Select
ActiveSheet.Paste

Range("B372:H395").Select
Selection.Cut
Range("B377").Select
ActiveSheet.Paste

Range("B378:H400").Select
Selection.Cut
Range("B383").Select
ActiveSheet.Paste

Range("B384:H405").Select
Selection.Cut
Range("B389").Select
ActiveSheet.Paste

Range("B390:H410").Select
Selection.Cut
Range("B395").Select
ActiveSheet.Paste

Range("B396:H415").Select
Selection.Cut
Range("B401").Select
ActiveSheet.Paste

Range("B402:H420").Select
Selection.Cut
Range("B407").Select
ActiveSheet.Paste

Range("B408:H425").Select
Selection.Cut
Range("B413").Select
ActiveSheet.Paste

Range("B414:H430").Select
Selection.Cut
Range("B419").Select
ActiveSheet.Paste

Range("B420:H435").Select
Selection.Cut
Range("B425").Select
ActiveSheet.Paste

Range("B426:H440").Select
Selection.Cut
Range("B431").Select
ActiveSheet.Paste

Range("B432:H445").Select
Selection.Cut
Range("B437").Select
ActiveSheet.Paste

Range("B438:H450").Select
Selection.Cut
Range("B443").Select
ActiveSheet.Paste

Range("B444:H455").Select
Selection.Cut
Range("B449").Select
ActiveSheet.Paste

Range("B450:H460").Select
Selection.Cut
Range("B455").Select
ActiveSheet.Paste

Range("B456:H465").Select
Selection.Cut
Range("B461").Select
ActiveSheet.Paste

Range("B462:H470").Select
Selection.Cut
Range("B467").Select
ActiveSheet.Paste

Range("B468:H475").Select
Selection.Cut
Range("B473").Select
ActiveSheet.Paste

Range("B474:H480").Select
Selection.Cut
Range("B479").Select
ActiveSheet.Paste

Range("B480:H485").Select
Selection.Cut
Range("B485").Select
ActiveSheet.Paste

Range("B486:H490").Select
Selection.Cut
Range("B491").Select
ActiveSheet.Paste

Range("B492:H495").Select
Selection.Cut
Range("B497").Select
ActiveSheet.Paste

Range("B498:H500").Select
Selection.Cut
Range("B503").Select
ActiveSheet.Paste

Range("B504:H505").Select
Selection.Cut
Range("B509").Select
ActiveSheet.Paste

Range("B510:H510").Select
Selection.Cut
Range("B515").Select
ActiveSheet.Paste
Range("L11:L16").Select
Selection.Copy
Range("G41").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("G41:G520"), Type:=xlFillDefault
Range("E41:H41").Select
Selection.Copy
Range("H42").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E41:H41").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E47:H47").Select
Selection.Copy
Range("H48").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E47:H47").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E53:H53").Select
Selection.Copy
Range("H54").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E59:H59").Select
Application.CutCopyMode = False
Selection.Copy
Range("H60").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E59:H59").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E53:H53").Select
Selection.ClearContents
Range("E65:H65").Select
Selection.Copy
Range("H66").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E65:H65").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E71:H71").Select
Selection.Cut
Range("E71:H71").Select
Application.CutCopyMode = False
Selection.Copy
Range("H72").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E71:H71").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E77:H77").Select
Selection.Copy
Range("H78").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E77:H77").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E83:H83").Select
Selection.Copy
Range("H84").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E83:H83").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E89:H89").Select
Selection.Copy
Range("H90").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E89:H89").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E95:H95").Select
Selection.Copy
Range("H96").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E95:H95").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E101:H101").Select
Selection.Copy
Range("H102").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E101:H101").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E107:H107").Select
Selection.Copy
Range("H108").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E107:H107").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E113:H113").Select
Selection.Copy
Range("H114").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E113:H113").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E119:H119").Select
Selection.Copy
Range("H120").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E119:H119").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E125:H125").Select
Selection.Copy
Range("H126").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E125:H125").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E131:H131").Select
Selection.Copy
Range("H132").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E131:H131").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E137:H137").Select
Selection.Copy
Range("H138").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E137:H137").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E143:H143").Select
Selection.Copy
Range("H144").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E143:H143").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E149:H149").Select
Selection.Copy
Range("H150").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E149:H149").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E155:H155").Select
Selection.Copy
Range("H156").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E155:H155").Select
Application.CutCopyMode = False
Range("E155:H155").Select
Selection.ClearContents
Range("E161:H161").Select
Selection.Copy
Range("H162").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E161:H161").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E167:H167").Select
Selection.Copy
Range("H168").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E167:H167").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E173:H173").Select
Selection.Copy
Range("H174").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E173:H173").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E179:H179").Select
Selection.Copy
Range("H180").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E179:H179").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E185:H185").Select
Selection.Copy
Range("H186").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E185:H185").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E191:H191").Select
Selection.Copy
Range("H192").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E191:H191").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E197:H197").Select
Selection.Copy
Range("H198").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E197:H197").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E203:H203").Select
Selection.Copy
Range("H204").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E203:H203").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E209:H209").Select
Selection.Copy
Range("H210").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E209:H209").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E215:H215").Select
Selection.Copy
Range("H216").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Selection.ClearContents
Range("E215:H215").Select
Selection.Copy
Range("H216").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E215:H215").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E221:H221").Select
Selection.Copy
Range("H222").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E221:H221").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E227:H227").Select
Selection.Copy
Range("H228").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E227:H227").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E233:H233").Select
Selection.Copy
Range("H234").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E233:H233").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E239:H239").Select
Selection.Copy
Range("H240").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E239:H239").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E245:H245").Select
Selection.Copy
Range("H246").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E245:H245").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E251:H251").Select
Selection.Copy
Range("H252").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E251:H251").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E257:H257").Select
Selection.Copy
Range("H258").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E257:H257").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E263:H263").Select
Selection.Copy
Range("H264").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E263:H263").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E269:H269").Select
Selection.Copy
Range("H270").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E269:I269").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E275:H275").Select
Selection.Copy
Range("H276").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E275:H275").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E281:H281").Select
Selection.Copy
Range("H282").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E281:H281").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E287:H287").Select
Selection.Copy
Range("H288").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E287:H287").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E293:H293").Select
Selection.Copy
Range("H294").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E293:H293").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E299:H299").Select
Selection.Copy
Range("H300").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E299:H299").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E305:H305").Select
Selection.Copy
Range("H306").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E305:H305").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E311:H311").Select
Selection.Copy
Range("H312").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E311:H311").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E317:H317").Select
Selection.Copy
Range("H318").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E317:H317").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E323:H323").Select
Selection.Copy
Range("H324").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E323:H323").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E329:H329").Select
Selection.Copy
Range("H330").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E329:H329").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E335:H335").Select
Selection.Copy
Range("H336").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E335:H335").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E341:H341").Select
Selection.Copy
Range("H342").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E341:H341").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E347:H347").Select
Selection.Copy
Range("H348").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E347:H347").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E353:H353").Select
Selection.Copy
Range("H354").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E353:H353").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E359:H359").Select
Selection.Copy
Range("H360").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E359:H359").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E365:H365").Select
Selection.Copy
Range("H366").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E365:H365").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E371:H371").Select
Selection.Copy
Range("H372").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E371:H371").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E377:H377").Select
Selection.Copy
Range("H378").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E377:I377").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E383:H383").Select
Selection.Copy
Range("H384").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E383:H383").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E389:H389").Select
Selection.Copy
Range("H390").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E389:H389").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E395:H395").Select
Selection.Copy
Range("H396").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E395:H395").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E401:H401").Select
Selection.Copy
Range("H402").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E401:H401").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E407:H407").Select
Selection.Copy
Range("H408").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E407:H407").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E413:H413").Select
Selection.Copy
Range("H414").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E413:H413").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E419:H419").Select
Selection.Copy
Range("H420").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E419:H419").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E425:H425").Select
Selection.Copy
Range("H426").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E425:H425").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E431:H431").Select
Selection.Copy
Range("H432").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E431:H431").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E437:H437").Select
Selection.Copy
Range("H438").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E437:H437").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E443:H443").Select
Selection.Copy
Range("H444").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E443:H443").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E449:H449").Select
Selection.Copy
Range("H450").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E449:H449").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E455:H455").Select
Selection.Copy
Range("H456").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E455:H455").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E461:H461").Select
Selection.Copy
Range("H462").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E461:H461").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E467:H467").Select
Selection.Copy
Range("H468").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E467:H467").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E473:H473").Select
Selection.Copy
Range("H474").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E473:H473").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E479:H479").Select
Selection.Copy
Range("H480").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E479:H479").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E485:H485").Select
Selection.Copy
Range("H486").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E485:H485").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E491:H491").Select
Selection.Copy
Range("H492").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E491:H491").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E497:H497").Select
Selection.Copy
Range("H498").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E497:H497").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E503:H503").Select
Selection.Copy
Range("H504").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E503:H503").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E509:H509").Select
Selection.Copy
Range("H510").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E509:H509").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E515:H515").Select
Selection.Copy
Range("H516").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E515:H515").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("C515").Select
ActiveWindow.ScrollRow = 510
ActiveWindow.ScrollRow = 383
ActiveWindow.ScrollRow = 256
ActiveWindow.ScrollRow = 129
ActiveWindow.ScrollRow = 2
Range("C41:G41").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlLTR
.MergeCells = False
End With
Selection.Merge
Range("C42:F45").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Merge
Range("B41:B45").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlLTR
.MergeCells = False
End With
Selection.Merge
Range("B41:H45").Select
Selection.Copy
Range("B47:H51").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("B41:I52").Select
Selection.Copy
Range("B53:I520").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("H519:I519").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Selection.Copy
Range("H40:I519").Select
Range("H519").Activate
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("B46:I46").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Merge
Range("B46:I51").Select
Selection.Copy
Range("B52:I520").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("B40:I520").Select
Range("B520").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Range("C42:F45").Select

Range("H37").Select
ActiveCell.FormulaR1C1 = _
"=SUM(R[8]C,R[14]C,R[20]C,R[26]C,R[32]C,R[38]C,R[44]C,R[50]C,R[56]C,R[62]C,R[68]C,R[74]C,R[80]C,R[86]C,R[92]C,R[98]C,R[104]C,R[110]C,R[116]C,R[122]C,R[128]C,R[134]C,R[140]C,R[146]C,R[152]C,R[158]C,R[164]C,R[170]C,R[176]C,R[182]C,R[188]C,R[194]C,R[200]C,R[206]C,R[212]C,R[218]C,R[224]C,R[230]C,R[236]C,R[242]C,R[248]C,R[254]C,R[260]C,R[266]C,R[272]C,R[278]C,R[284]C,R[290]C,R[296]C,R[302]C,R[308]C,R[314]C,R[320]C,R[326]C,R[332]C,R[338]C,R[344]C,R[350]C,R[356]C,R[362]C,R[368]C,R[374]C,R[380]C,R[386]C,R[392]C,R[398]C,R[404]C,R[410]C,R[416]C,R[422]C,R[428]C,R[434]C,R[440]C,R[446]C,R[452]C,R[458]C,R[464]C,R[470]C,R[476]C,R[482]C)"
Range("H38").Select

the bottom section of the code is a sum of all 80 components that may or may not be there. if this could be changed so it inserts its self at the bottom of the quotation and sums the totals of each segement it would save me time. is someone please able to help?

thanks
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Sort by date Sort by votes
without looking at all to code without tags
I'd suggest you go through and cleanup your code
from
Code: 
Range("B42:H120").Select
Selection.Cut
Range("B47").Select
ActiveSheet.Paste
TO
Code: 
Range("B42:H120").Copy Destination: Range ("B47")

Also, use code tags in you post....this [ code] at the begginning of your code
AND this [ /code] at the end of the code...Make sure to remove the spaces inside the code boxes first.

I'm sure that will help you get moer responses as well.
 
Last edited:
Upvote 0
Hi

when i try to clean up the code i and run the macro i get the following message

Microsoft visual Basic

Compile Error:

Invalid use of property

im not sure what you mean when you say "Code tags" sorry I am just very new at VBE code. what the code does is the following
<TABLE style="WIDTH: 334pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=444 border=0><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" span=2 width=86><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2925" width=80><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 38.25pt" height=51><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 38.25pt; BACKGROUND-COLOR: transparent" width=64 height=51> </TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64> </TD><TD class=xl67 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=86>Parts Sub Total </TD><TD class=xl67 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=86>Labour Sub Total</TD><TD class=xl67 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 60pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=80>Misc</TD><TD class=xl67 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64>Total for Segment</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> </TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 38.25pt" height=51><TD class=xl68 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 38.25pt; BACKGROUND-COLOR: transparent" align=right width=64 height=51>2</TD><TD class=xl68 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>RECONDITION ENGINE </TD><TD class=xl69 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=86> $00.00 </TD><TD class=xl69 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=86> $00.00 </TD><TD class=xl69 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 60pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=80> $00.00 </TD><TD class=xl69 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64> $00.00 </TD></TR></TBODY></TABLE>

i need it to look like this

<TABLE style="WIDTH: 538pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=715 border=0><COLGROUP><COL style="WIDTH: 139pt; mso-width-source: userset; mso-width-alt: 6765" width=185><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" span=2 width=86><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2925" width=80><COL style="WIDTH: 81pt; mso-width-source: userset; mso-width-alt: 3949" width=108><COL style="WIDTH: 32pt; mso-width-source: userset; mso-width-alt: 1536" width=42><TBODY><TR style="HEIGHT: 25.5pt" height=34><TD class=xl69 dir=ltr style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 538pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent" width=715 colSpan=8 height=34> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl69 dir=ltr style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; WIDTH: 139pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 102.75pt; BACKGROUND-COLOR: transparent" width=185 height=137 rowSpan=5>2</TD><TD class=xl69 dir=ltr style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 286pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=380 colSpan=5>RECONDITION ENGINE </TD><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl69 dir=ltr style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 226pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 90pt; BACKGROUND-COLOR: transparent" width=300 colSpan=4 height=120 rowSpan=4> </TD><TD class=xl67 dir=ltr style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; WIDTH: 60pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" align=left width=80>Parts Sub Total </TD><TD class=xl68 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 81pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=left width=108> $00.00 </TD><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl67 dir=ltr style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; WIDTH: 60pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: white" align=left width=80 height=34>Labour Sub Total</TD><TD class=xl68 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 81pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=left width=108> $00.00 </TD><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 13.5pt; mso-height-source: userset" height=18><TD class=xl67 dir=ltr style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; WIDTH: 60pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" align=left width=80 height=18>Misc</TD><TD class=xl68 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 81pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=left width=108> $00.00 </TD><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl67 dir=ltr style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; WIDTH: 60pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: white" align=left width=80 height=34>Total for Segment</TD><TD class=xl68 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 81pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=left width=108> $00.00 </TD><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl69 dir=ltr style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 538pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent" width=715 colSpan=8 height=34> </TD></TR></TBODY></TABLE>

I get when i need out of the code i posted before however it does take time to cycle through. i know all code takes time however i have had to formatt and insert lines/cells for the max number of segments that i know is going to be populated at this stage. is there a way with out using a pivot table to code the data to go from the raw data to the way i need it to look with out having to do it manually like i have done.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
VBA optimization advice
Replies
3
Views
95
bobsan42
bobsan42
P
  • Solved
Counter in For Each loop
Replies
2
Views
195
Possum17
P
P
  • Solved
Need help with macro Dynamic Range??
Replies
6
Views
172
pennbowl
P
R
  • Solved
How to loop a macro
Replies
2
Views
371
rlcurlee
R
P
  • Question
Help with Vba Code
Replies
3
Views
159
Coyotex3
C

Forum statistics

Threads
1,214,905
Messages
6,122,174
Members
449,071
Latest member
cdnMech

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top