current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I need help improving my code below as it is a total mess. The correction is just a stop gap for something much bigger that I am trying to achieve. In short what I want to do is to create a pivot like table, based on report filter in Col D of Raw data

What I would have wanted to do is to automatically create summaries in Bristol or West Brom worksheets based on information from Raw Data. I did a pivot table on this and I will lay out my plan using the pivot example. In West Brom worksheet I want a report like a pivot table where the REPORT FILTER is driven by Col D:D, Row labels from G:G & values from AB,AC,AD,AE,AF,AG & AT. The report should then create a table for each item in the REPORT filter one after the other and thats it. The code in its form is rigid cause the variables in D:D & G:G are always changing. If there is a way please help. Pivot table with dynamic name range is the route I had gone for but the Head of Finance said no,(he's an idiot) so I'm stuffed. I will try to upload the spreadsheet after work as I cannot do it from here cause they blocked online file storage sites.

Sub EntitySummaries()
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Set ws1 = ActiveWorkbook.Sheets("RAW DATA")
Set ws2 = ActiveWorkbook.Sheets("BRISTOL")
Set ws3 = ActiveWorkbook.Sheets("West Brom")
Set BRng1 = ws2.Range("C16:C26,D16:D26,E16:E26,F16:F26,G16:G26,H16:H26")
Set BRng2 = ws2.Range("C32:C75,D32:D75,E32:E75,F32:F75,G32:G75,H32:H75")
Set BRng3 = ws2.Range("C81:C85,D81:D85,E81:E85,F81:F85,G81:G85,H81:H85")
Set BRng4 = ws2.Range("C91:C91,D91:D91,E91:E91,F91:F91,G91:G91,H91:H91")
Set BRng5 = ws2.Range("C97:C97,D97:D97,E97:E97,F97:F97,G97:G97,H97:H97")
Set BRng6 = ws2.Range("I16:I26,I32:I75,I81:I85,I91:I91,I97:I97")
With ws2
BRng1.Formula = "=SUMIF('RAW DATA'!C7,RC2, 'RAW DATA'!C[25])"
BRng2.Formula = "=SUMIF('RAW DATA'!C7,RC2, 'RAW DATA'!C[25])"
BRng3.Formula = "=SUMIF('RAW DATA'!C7,RC2, 'RAW DATA'!C[25])"
BRng4.Formula = "=SUMIF('RAW DATA'!C7,RC2, 'RAW DATA'!C[25])"
BRng5.Formula = "=SUMIF('RAW DATA'!C7,RC2, 'RAW DATA'!C[25])"
BRng6.Formula = "=SUMIF('RAW DATA'!C7,RC2, 'RAW DATA'!C[37])"
ws2.Calculate
Range("C16:I26").Copy
Range("C16:I26").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Range("C32:I75").Copy
Range("C32:I75").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Range("C81:I85").Copy
Range("C81:I85").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Range("C91:I91").Copy
Range("C91:I91").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Range("C97:I97").Copy
Range("C97:I97").PasteSpecial xlPasteValues
Application.CutCopyMode = False
End With
With ws3
Set WBRng1 = ws3.Range("C16:C16,D16:D16,E16:E16,F16:F16,G16:G16,H16:H16")
Set WBRng2 = ws3.Range("C22:C24,D22:D24,E22:E24,F22:F24,G22:G24,H22:H24")
Set WBRng3 = ws3.Range("C30:C45,D30:D45,E30:E45,F30:F45,G30:G45,H30:H45")
Set WBRng4 = ws3.Range("C51:C57,D51:D57,E51:E57,F51:F57,G51:G57,H51:H57")
Set WBRng5 = ws3.Range("C63:C76,D63:D76,E63:E76,F63:F76,G63:G76,H63:H76")
Set WBRng6 = ws3.Range("C82:C86,D82:D86,E82:E86,F82:F86,G82:G86,H82:H86")
Set WBRng7 = ws3.Range("C92:C103,D92:D103,E92:E103,F92:F103,G92:G103,H92:H103")
Set WBRng8 = ws3.Range("C109:C109,D109:D109,E109:E109,F109:F109,G109:G109,H109:H109")
Set WBRng9 = ws3.Range("C115:C115,D115:D115,E115:E115,F115:F115,G115:G115,H115:H115")
Set WBRng10 = ws3.Range("C121:C121,D121:D121,E121:E121,F121:F121,G121:G121,H121:H121")
Set WBRng11 = ws3.Range("CI16:I16,I22:I24,I30:I45,I51:I57,I63:I76,I82:I86,I92:I103,I109:I10,I115:I115,I121:I121,I97:I97")
WBRng1.Formula = "=SUMIF('RAW DATA'!C7,RC2, 'RAW DATA'!C[25])"
WBRng2.Formula = "=SUMIF('RAW DATA'!C7,RC2, 'RAW DATA'!C[25])"
WBRng3.Formula = "=SUMIF('RAW DATA'!C7,RC2, 'RAW DATA'!C[25])"
WBRng4.Formula = "=SUMIF('RAW DATA'!C7,RC2, 'RAW DATA'!C[25])"
WBRng5.Formula = "=SUMIF('RAW DATA'!C7,RC2, 'RAW DATA'!C[25])"
WBRng6.Formula = "=SUMIF('RAW DATA'!C7,RC2, 'RAW DATA'!C[25])"
WBRng7.Formula = "=SUMIF('RAW DATA'!C7,RC2, 'RAW DATA'!C[25])"
WBRng8.Formula = "=SUMIF('RAW DATA'!C7,RC2, 'RAW DATA'!C[25])"
WBRng9.Formula = "=SUMIF('RAW DATA'!C7,RC2, 'RAW DATA'!C[25])"
WBRng10.Formula = "=SUMIF('RAW DATA'!C7,RC2, 'RAW DATA'!C[25])"
WBRng11.Formula = "=SUMIF('RAW DATA'!C7,RC2, 'RAW DATA'!C[37])"
ws3.Calculate
Range("C16:I16").Copy
Range("C16:I16").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Range("C22:I24").Copy
Range("C22:I24").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Range("C30:I45").Copy
Range("C30:I45").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Range("C51:I57").Copy
Range("C51:I57").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Range("C63:I76").Copy
Range("C63:I76").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Range("C82:I86").Copy
Range("C82:I86").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Range("C92:I103").Copy
Range("C92:I103").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Range("C109:I109").Copy
Range("C109:I109").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Range("C115:I115").Copy
Range("C115:I115").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Range("C121:I121").Copy
Range("C121:I121").PasteSpecial xlPasteValues
Application.CutCopyMode = False
End With
End Sub
share|improve this question
2  
if i were you i would use loops - look at your rows and column, dim them with x, y with an easy logic loop through and stick formulas –  vba4all May 1 '13 at 11:22
    
Oh thanks for the reply. Believe me if I knew how to use loops, thats what I would have done. I have only been using VBA code for just over a month and with your help here have managed to create a few working codes. However I am still limited on what I can do and thats why I am here looking for help. –  Werra2006 May 1 '13 at 11:28

1 Answer 1

up vote 6 down vote
  1. This is a sample of what you can do to make your code shorter and more efficient

this: 

Set BRng1 = ws2.Range("C16:C26,D16:D26,E16:E26,F16:F26,G16:G26,H16:H26")
With ws2
    BRng1.Formula = "=SUMIF('RAW DATA'!C7,RC2, 'RAW DATA'!C[25])"
End With

could have easily be replaced with a simple and logical loop

for i = 16 to 26 ' rows 16 to 26
 for j = 3 to 8 ' columns C to H
     ws2.cells(i, j).Formula = "=SUMIF('RAW DATA'!C7,RC2, 'RAW DATA'!C[25])"
 next j 
next i

Just follow the same logic for a bit - and you will quickly realise how easy it is and how you can improve your loops to do more in one go


2. And instead of copying ranges ( btw you should toggle Application.ScreenUpdating ) assign values like this 

ws1.Range("A1").Value = ws2.Range("A1").Value
share|improve this answer
    
Thi is turning into a revealtion for me. I will have a go and post what I achieve. Thank you very much for the useful help. –  Werra2006 May 1 '13 at 11:58
4  
+1 nicely explained, teaching to fish. –  glh May 1 '13 at 21:43
    
@Werra2006 please consider accepting this answer (green checkmark) if its useful :) thanks –  vba4all Sep 11 '13 at 9:27
    
agree with @glh –  Siddharth Rout Dec 20 '13 at 1:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.