Try the attached. I highlighted at the start of the code the array that determines which columns to match on.
Posts by rory
Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.
-
-
-
I note that you have posted similar vague requests in a few other forums too. Given the level of detail you have posted here, all I can say is yes, there probably is.
-
If you make the source data into a table, your formula can refer to the table and will not need changing.
-
There's none of that in your code, so I'm not really sure what you are currently doing...
-
-
No need on my part. I didn't even notice this was in the hire help section.
-
Subtract 1 and then round normally?
-
I suggest you post a sample workbook that accurately reflects your real data layout. There is not enough information currently to write accurate code for what you want.
-
Glad we could help.
-
-
So what happens when you type a 1 in one of the cells in column G of that sheet?
-
Which module did you put the code in? It must be in the code module of the sheet where the values are being changed, and the values must not be the result of formulas.
-
No. I think the only way would involve changing your windows settings, which would affect the title bar of every single application, but I can't be sure that would even work (and don't have 2007 to test).
-
I am aware of that - that is precisely my point. Why do you want that when it makes the value of the last day of any month different to the value of any other day in that month?
Regardless, I already told you how to work around it.
-
My point is that if you apply your average, then for February, every day is worth 1/30.41666 of the total apart from the last day of the month which is worth about 3 times as much. I can't see why you would prefer that to actually using the number of days in the relevant month.
Regardless, all you really need to do to your original formula is test if E7=EOMONTH(E7,0) to see if it's the last day of whichever month.
-
Also, it will mean that with your formula the last day of any month will equate to a different percentage of the total value than any other day in that month. I don't see why you would want that?
-
Why should you divide by 30.41666? No month has that number of days.
-
Your field gets renamed as soon as it's added to the data area (based on the function used). You already have a reference to the field thanks to your With block, so you don't need to refer to it by name anyway. Try:
Code
Display MoreSub CreatePivotTable() Dim wb As Workbook Dim ws As Worksheet Dim pc As PivotCache Dim pt As PivotTable Dim ptsheet As Worksheet Dim lr As Long Dim lc As Long Dim prange As Range Set ws = ActiveSheet Set wb = ActiveWorkbook If Left(ActiveSheet.Name, 2) <> "EK" Then MsgBox "This is not the active sheet, select EK daily tins ****** sheet and try again.", vbExclamation Exit Sub End If 'Find last row and column' lr = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row lr = lr - 5 'Exclude first 4 and last rows of data' lc = ws.Cells(5, ws.Columns.Count).End(xlToLeft).Column 'Set range to resize based on number od column and rows' Set prange = ws.Cells(5, 1).Resize(lr, lc) 'Create new worksheet for pivot table' Set ptsheet = Worksheets.Add(after:=ActiveSheet) ptsheet.Name = "PivotTable" 'Create pivot cache and pivot table' Set pc = wb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=prange) Set pt = pc.CreatePivotTable(TableDestination:=ptsheet.Range("A1"), TableName:="EK Tins") 'Add pivot fields' With pt .PivotFields("Resp Man").Orientation = xlRowField With .PivotFields("TRUST No") .Orientation = xlDataField .Function = xlCount .Position = 1 End With With .PivotFields("Minutes") .Orientation = xlDataField .Function = xlSum .Position = 2 End With End With End Sub
-
You've assigned the Rowsource property for that control. You have to clear that before you can use the List property to populate it, or just stick to using List instead.