Pivot tables and VBA (looked at other threads!)

[slay0r]

Hi Guys,I'm getting really confused over VBA and pivot tables.My data will change week in and week out so the sizes of the spreadsheet will be different. Haven't got a clue how to do that with my current code that I have! I also don't want to end up with any "N/A columns in it!"

Sub createpivot()
'
' createpivot Macro
' Macro recorded 23/11/2011 by jmartin
'
'
   ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
                                  "Sheet3!R1C1:R8471C44").CreatePivotTable TableDestination:="", TableName:= _
                                  "PivotTable2", DefaultVersion:=xlPivotTableVersion10
   ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
   ActiveSheet.Cells(3, 1).Select
   ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:="Alphaname ", _
                                                    ColumnFields:="Import/Export"
   ActiveSheet.PivotTables("PivotTable2").PivotFields("Over 45 Days").Orientation _
         = xlDataField
   Application.CommandBars("PivotTable").Visible = False
   ActiveWorkbook.ShowPivotTableFieldList = False
End Sub

I haven't really got a lot of knowledge so I don't know how the above is working on that, do I need to change the range and that's it or is there more to it? Also Pivottable2? Is that because I tested one out before, do I need to change this as I won't be saving the spreadsheet and the first one won't be there tomorrow? Need a VBA course baaaadly!

 

[slay0r]

Jeez that was stupid of me. Need more sleep I guess.

I'm not sure it will always be in sheet 3, what would I need to change it to for it to use the active sheet? That will help me with a few little teething problems I've gained. (when I told you about the numbering system, 1-10 all use sheets because I didn't know what I was doing!)

Is it just activesheet.select or am I going down the wrong road there?

 

[RoryA]

Try this:

Sub createpivot7()
'
' createpivot Macro
' Macro recorded 23/11/2011 by jmartin
'
'
   Dim PC                As Excel.PivotCache
   Dim PT                As Excel.PivotTable
   Dim wksPT             As Excel.Worksheet
   DIm wksData as excel.worksheet
   ' add sheet for pivot table
   set wksdata = activesheet
   Set wksPT = Worksheets.Add
   ' create cache using table starting in A1
   With wksdata
      Set PC = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
                                              "'" & .Name & "'!" & .Range("A1").CurrentRegion.Address(ReferenceStyle:=xlR1C1))
   End With
   ' create pivot table from cache, on new sheet at A3
   Set PT = PC.CreatePivotTable(TableDestination:=wksPT.Cells(3, 1), TableName:= _
                                "PivotTable2", DefaultVersion:=xlPivotTableVersion10)
   ' add one row field and one column field
   PT.AddFields RowFields:="Alphaname ", ColumnFields:="Import/Export"
   ' add data field
   PT.PivotFields("Over 45 Days").Orientation = xlDataField
   Application.CommandBars("PivotTable").Visible = False
   ActiveWorkbook.ShowPivotTableFieldList = False
End Sub

 

[slay0r]

Just so I get the hang of this particular bit that you've touched on above..:

Sub CopyARacross2()
()
'
' CopyARacross Macro
' Macro recorded 18/11/2011 by jmartin
'
'
    Sheets("AR Report").Select
    Sheets.Add
    Windows("Current AR Report HSUK.XLS").Activate
    Cells.Select
    Selection.Copy
    Windows("HSUK test with rows deleted.XLS").Activate
    ActiveSheet.Paste
    Windows("Current AR Report HSUK.XLS").Activate
    ActiveWindow.Close
End Sub

For this one, it is trying to bring it into a set of data off another sheet, then bringing it across to the one I'm automating. (it opens off another process) the thing I want it to do is to name the sheet afterwards that it's copying to. Once this is done can I set this sheet as a dimension or something or am I overcomplicating again? There's a later macro that needs to look at this sheet and I don't want to call it sheet1 but it won't be the activesheet at the time. Does that make sense or is it a big wall of confusing text?

Thanks for your time by the way, my understanding is increasing by the minute I'd guess..

 

[RoryA]

I would use a variable, though you could also just name it the way you want:

DIm wksData as Worksheet
Sub CopyARacross2()
()
'
' CopyARacross Macro
' Macro recorded 18/11/2011 by jmartin
'
'
    Sheets("AR Report").Select
    Set wksData = Sheets.Add
    With Workbooks("Current AR Report HSUK.XLS")
       .Activesheet.Usedrange.Copy Destination:=wksData.Range("A1")
       .Close
    End With
End Sub

you can then refer to wksData in other subs in the same module.

 

[slay0r]

Oh you legend, I didn't realise you could use the same thing within a module! That's really handy from some of the other routines so I'll give that a go.

Does that effectively mean that if I want to reference to any of the data in that sheet, I can now just put something like:

With wksdata

then just do my usual thing?

 

[RoryA]

Yes. I would personally prefer to pass the worksheet as an argument to any routines that need it, but you can also use module level variables like this, as long as the code gets run in the right order!

 

[slay0r]

So just to clarify, it can be in the same module but I can run it in different order? I.e. I can put everything that's involving this part of the automation in one module and call them in different orders? I.e. with my number system, 1 4 and 9 might all use the same data, as long as they're in the same module and get run in the correct order it will work?

Thanks for that by the way it's worked rather nicely.

 

[RoryA]

As long as you have run the macro that creates the sheet and assigns it to the variable before you try and use the variable, all will be fine.

 

[slay0r]

Sweeeeet! It's a bit wrong I'm getting a bit excited about a bit of code but that's life..

Have a good weekend anyway mate, I've used up enough of your time for one day!