simple steps for creating a pivot table using VBA?

**hotwheels** · 10-26-2010, 01:23 AM

Hi,

I have pretty stanard timestamped data in one sheet and I'd like to create a Pivot table from it in another sheet.

I'd hoped that VBA would just record the steps I was taking in Excel, but this doesn't seem to happen.

Could someone take me through creating a Pivot table using VBA?

**pr4t3ek** · 10-26-2010, 02:16 AM

paste your data and i will then provide you the code

**hotwheels** · 10-27-2010, 02:54 AM

Ok,

I have attached the data.

You'll see the two pivot tables I have created from the data on two separate sheets. I'd like to make these using VBA.

Also, as the time is in hours I'd like to group hours 24 and 0 as these are the same.

I can record all the steps I take until the pivots which is great. I have a macro to extract data from the pivots into a table. This is approximately that macro, though with diffferent references:

Sub Example2()
With Sheets("24 hour data BI")
With .Range(.Cells(2, "A"), Cells(.Rows.Count, "A").End(xlUp)).Offset(, 1).Resize(, 24)
.FormulaR1C1 = "=IF(ISERROR(GETPIVOTDATA(""time round2"",Sheet4!R1C1, ""Station"",RC1, ""time round2"",R1C)),0,GETPIVOTDATA(""time round2"",Sheet4!R1C1, ""Station"",RC1, ""time round2"",R1C))"
End With
End With
End Sub

Once the data is in the table - hours along the top and stations down the left I want to make graphs for each station. this might be another task..

Anyway any help you could lend me either on the pivots or the graphs would be awesome.

**royUK** · 10-27-2010, 04:09 AM

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

**hotwheels** · 10-27-2010, 07:30 PM

ok, I'll post better next time.

**pr4t3ek** · 10-27-2010, 08:18 PM

Hi there,

You're working for MelbourneBikeShare? Nice! I haven't used the bikes yet but a pretty nice idea.

code to create the BI Pivot:-

Sub BI_PT()
    Sheets("Adj Data BI").Select
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "'Adj Data BI'!A:H").CreatePivotTable TableDestination:="", TableName:= _
        "PT_BI", DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTables("PT_BI").AddFields RowFields:="Station", ColumnFields:="time"
    ActiveSheet.PivotTables("PT_BI").PivotFields("t").Orientation = xlDataField
    ActiveSheet.PivotTables("PT_BI").PivotFields("time").AutoSort xlAscending, "time"
    ActiveSheet.PivotTables("PT_BI").PivotFields("Station").AutoSort xlAscending, "Station"
    ActiveSheet.PivotTables("PT_BI").PivotSelect "", xlDataAndLabel, True
End Sub

Code to create the BO Pivot:-

Sub BO_PT()
    Sheets("Adj Data BO").Select
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "'Adj Data BO'!A:H").CreatePivotTable TableDestination:="", TableName:= _
        "PT_BO", DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTables("PT_BO").AddFields RowFields:="Station", ColumnFields:="time"
    ActiveSheet.PivotTables("PT_BO").PivotFields("t").Orientation = xlDataField
    ActiveSheet.PivotTables("PT_BO").PivotFields("time").AutoSort xlAscending, "time"
    ActiveSheet.PivotTables("PT_BO").PivotFields("Station").AutoSort xlAscending, "Station"
    ActiveSheet.PivotTables("PT_BO").PivotSelect "", xlDataAndLabel, True
End Sub

Let me know if there are any issues.

Cheers

**hotwheels** · 10-28-2010, 11:39 PM

Thanks for the code!

It is great I have been able to move leaps ahead.

you are right this is data from the MBS. Hopefully I get get something useful out of it.

I have another 3 (4) requests for assistance on this data.

1. In the time row at the top of the pivot table I'd like it to show all the hours of the day, regardless of whether or not someting happened at theat time. for large amounts of data all time periods will be shown but if, say, nothing happened at 5am then this would be skipped. I will be graphing this data so need 24 equal intervals.
2. I'd like to group the 0 and 24 hour data as it is in the same interval. I'd like to label this as '0'.
3. I'd like to group the days of the week into 'weekdays' and 'weekends'
4 And this might have to be a new post, I'd like to create graphs for each of the stations on each of 'weekdays' and 'weekends' ie 100 graphs. For this I just don't know at all where to start.

Help greatly appreciated,

HW
apologies if this is duplicated, I had some trouble posting..

**pr4t3ek** · 10-28-2010, 11:55 PM

1. In the time row at the top of the pivot table I'd like it to show all the hours of the day, regardless of whether or not someting happened at theat time. for large amounts of data all time periods will be shown but if, say, nothing happened at 5am then this would be skipped. I will be graphing this data so need 24 equal intervals.

In your 'Adj Data BI' and 'Adj Data BO' add numbers 1 to 24 in column G. You do not need to put anything in these any of the other columns for these 1-24 rows.

Add

ActiveSheet.PivotTables("PT_BI").PivotFields("time").ShowAllItems = True

to the BI code.

Add

ActiveSheet.PivotTables("PT_BO").PivotFields("time").ShowAllItems = True

to the BO code.

2. I'd like to group the 0 and 24 hour data as it is in the same interval. I'd like to label this as '0'.

You will have to perform an IF formula in your Data sheets for this.

3. I'd like to group the days of the week into 'weekdays' and 'weekends'

You would have to use the weekday and if formula's for this.

4 And this might have to be a new post, I'd like to create graphs for each of the stations on each of 'weekdays' and 'weekends' ie 100 graphs. For this I just don't know at all where to start.

Really? anyway yeah, after you have done the above 3, paste this as another thread.

I can do this for you but it's probably best for you to try these yourself, especially because these requests are relatively simple. Let me know otherwise