Pivot table creation via Macro: a greyed rectangle appears

steackbrit

New Member
Joined
Sep 21, 2006
Messages
6
Hi guys and girls :LOL:

Well, first message on this forum... I figured after consulting it for a very long time, I am as well ask the questions directly!

So here's my problem:
I need to update a Pivot table via VBA given new settings in a SQL Query (basically date and code settings). My process is as follows: I erase the previous PivotTable, then create a new one. It works fine but I systematically get a grey rectangle in the middle of the field, I guess it's the table field list which just pops up for a fraction of a second. Not very serious but annoying as I'm running the codes from a usf with a progressbar, and it looks messy (my USF becomes transparent and I see the rectangle behind it ^^)

Any idea or experience on this issue?

I've tried application.screenupdating=false, application.displayalerts=false and activeworkbook.showpivottablefieldlist=false

here's my code


Sub GET_ALLOCATION_HISTORY()


Application.ScreenUpdating = False
Application.DisplayAlerts = False
ActiveWorkbook.ShowPivotTableFieldList = False

Worksheets("ALLOC_HISTORY").Select
Cells.ClearContents
Dim code As String, endd As String
code = Worksheets("settings").Range("C3").Value
endd = Worksheets("settings").Range("C6").Value

Application.ScreenUpdating = False

With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
.Connection = Array(Array( _
"ODBC;DSN=FUND_DATA_TEST;Description=dd;UID=jesab;APP=Microsoft Office XP;WSID=NWKR0033;DATABASE=FUND_DATA_TEST;Trusted_Connection=Ye" _
), Array("s"))
.CommandType = xlCmdSql
.CommandText = Array( _
"SELECT VW_AssetType.Date, VW_AssetType.FundCode, VW_AssetType.Weight, VW_AssetType.AssetType" & Chr(13) & "" & Chr(10) & "FROM FUND_DATA_TEST.""CA-AM\jesab"".VW_AssetType VW_AssetType" & Chr(13) & "" & Chr(10) & "WHERE (VW_AssetType.FundCode='" & code & "') AND (VW_AssetType.Date<='" & endd & "')" & Chr(13) & "" & Chr(10) & "ORDER " _
, "BY VW_AssetType.Date")
.CreatePivotTable TableDestination:= _
"ALLOC_HISTORY!R3C1", TableName:="PivotTable8", _
DefaultVersion:=xlPivotTableVersion10
End With

ActiveWorkbook.ShowPivotTableFieldList = False

With ActiveSheet.PivotTables("PivotTable8").PivotFields("Date")
.Orientation = xlRowField
.Position = 1
End With

ActiveWorkbook.ShowPivotTableFieldList = False

Application.ScreenUpdating = False
With ActiveSheet.PivotTables("PivotTable8").PivotFields("AssetType")
.Orientation = xlColumnField
.Position = 1
End With

ActiveWorkbook.ShowPivotTableFieldList = False

ActiveSheet.PivotTables("PivotTable8").AddDataField ActiveSheet.PivotTables( _
"PivotTable8").PivotFields("Weight"), "Sum of Weight", xlSum
ActiveWorkbook.ShowPivotTableFieldList = False
Range("D10").Select
With ActiveSheet.PivotTables("PivotTable8")
.ColumnGrand = False
.DisplayErrorString = True
.RowGrand = False
End With

ActiveWorkbook.ShowPivotTableFieldList = False

Cells.Select
Selection.Copy
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("G13").Select
Application.CutCopyMode = False

Worksheets("settings").Select

End Sub
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
Hi steackbrit, welcome to the Board!

I've taken the liberty of cleaning up your code a little, mostly by assigning your pivot table to an object variable, but also by removing repeating instructions.

You should find that the ManualUpdate property does the job for you: set it to TRUE to prevent automatic refreshes, and FALSE to force a refresh at the end.

Got a question though: Why are you selecting all, then pasting values over itself at the end? Do you want to transfer the data to another location once the PT has done its job, or is there another reason?


Code:
Sub GET_ALLOCATION_HISTORY()
Dim code As String, endd As String
Dim PT As PivotTable

Application.ScreenUpdating = False
Application.DisplayAlerts = False
ActiveWorkbook.ShowPivotTableFieldList = False

Worksheets("ALLOC_HISTORY").Select
Cells.ClearContents
code = Worksheets("settings").Range("C3").Value
endd = Worksheets("settings").Range("C6").Value

Application.ScreenUpdating = False

With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
    .Connection = Array(Array( _
    "ODBC;DSN=FUND_DATA_TEST;Description=dd;UID=jesab;APP=Microsoft Office XP;WSID=NWKR0033;DATABASE=FUND_DATA_TEST;Trusted_Connection=Ye" _
    ), Array("s"))
    .CommandType = xlCmdSql
    .CommandText = Array( _
    "SELECT VW_AssetType.Date, VW_AssetType.FundCode, VW_AssetType.Weight, VW_AssetType.AssetType" & Chr(13) & "" & Chr(10) & "FROM FUND_DATA_TEST.""CA-AM\jesab"".VW_AssetType VW_AssetType" & Chr(13) & "" & Chr(10) & "WHERE (VW_AssetType.FundCode='" & code & "') AND (VW_AssetType.Date<='" & endd & "')" & Chr(13) & "" & Chr(10) & "ORDER " _
    , "BY VW_AssetType.Date")
    .CreatePivotTable TableDestination:= _
    "ALLOC_HISTORY!R3C1", TableName:="PivotTable8", _
    DefaultVersion:=xlPivotTableVersion10
End With

Set PT = ActiveSheet.PivotTables("PivotTable8")
PT.ManualUpdate = True

With PT.PivotFields("Date")
    .Orientation = xlRowField
    .Position = 1
End With

With PT.PivotFields("AssetType")
    .Orientation = xlColumnField
    .Position = 1
End With

PT.AddDataField ActiveSheet.PivotTables( _
"PivotTable8").PivotFields("Weight"), "Sum of Weight", xlSum

With PT
    .ColumnGrand = False
    .DisplayErrorString = True
    .RowGrand = False
End With

PT.ManualUpdate = False

Cells.Select
Selection.Copy
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("G13").Select
Application.CutCopyMode = False

Worksheets("settings").Select

End Sub
Hope this helps

Denis
 

steackbrit

New Member
Joined
Sep 21, 2006
Messages
6
Thanks!

Hey

thanks for this quick response; I'll try your code early next week, it's getting rather late in my timezone ^^

As for the Copy.paste bit of code, yep indeed, it's because I use this data range for other workbooks and find it easier to transfer and keep data in values (it's a rather huge workbook).

Thanks again!
 

Watch MrExcel Video

Forum statistics

Threads
1,133,466
Messages
5,658,952
Members
418,478
Latest member
Adam19931

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top