Posts by rory

Try the attached. I highlighted at the start of the code the array that determines which columns to match on.

Try using:

If ActiveSheet.Cells(1).currentregion.Columns.Count > 1 Then

rather than usedrange, which doesn't always reset properly. You could also use:

If not activesheet.range("A2").SpillingToRange is nothing then

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...

You could test:

If activesheet.usedrange.columns.count > 1 then
' rest of your processing code here

End If

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.

Your code is determining the last copy row based on the active sheet at the time. You should specify the CP sheet:

Sheets("CP").Range("A2:A" & Sheets("CP").Cells(Rows.Count, "A").End(xlUp).Row).Copy Destination:=Sheets("DATA").Range("C" & Rows.Count).End(xlUp).Offset(1, 0)

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:

Sub 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.