Derek, good thing you ask I generally limit my interaction on the groups as I don't really have any scripting skills. One think I have been trying to get done is enlarge the size of the text in a dropdown list when it is selected. I have about4 dropdown lists in a sheet, the text so small it is difficult to select the correct one. If you have any suggestions on how to get the font size of the text enlarged when scrolling through the drop down list it will help me out a lot.
--- In ExcelVBA@yahoogroups.com, Derek Turner <g4swy@...> wrote:
>
> Dear All
>
> As this group has been very quiet lately I thought I would post this as a solution to a problem that nearly drove me mad in Excel 2010.
>
> I had been using a macro to make a pivot table for quite some time when suddenly it stopped working with Error 13, Type Mismatch in this line :-
> Set pivotTableCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, SourceData:=rSource)
>
> where rSource was set up like this :-
> Set rSource = Range("a1").CurrentRegion
>
> It happened (of course) when the row count in the source data crept beyond a 65,535 boundary. It worked perfectly with fewer rows in an older data set.
>
> The problem seemed to be in 'SourceData:=rSource' because I could still create the pivot table manually. I tried many variations of coding the source range and then found that even if I hard-coded the fully qualified source address it still did Error 13, which by the way is not the error you get if the pivot runs out of memory.
>
>
> After many hours of Googling, I came across a mention of Named Ranges (which you can get at from menu item Formulas, Name Manager).
>
> So I created a Named Range thus :-
> Set rSource = Range("a1").CurrentRegion
> ThisWorkbook.Names.Add Name:="SOURCE", RefersTo:=rSource
>
> and then did :-
> Set pivotTableCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, SourceData:="SOURCE")
>
> It worked perfectly. So 'SourceData:=' works better with a Named Range. Why ?
>
> Here is a cut-down example of the code (removed error checking and formatting). :-
>
> Option Explicit
> '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '
> ' Generic Pivot Table Maker - Derek Turner September 2013
> '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '
> Private Sub MakePivotTable(sPivotName As String, sColumnField As String, sRowField As String, sValuesField As String)
> Dim rSource As Range, rDestination As Range
> Dim pivotTableCache As PivotCache, pivotTableReport As PivotTable
> Const SOURCE As String = "SOURCE"
> Set rSource = Range("a1").CurrentRegion
> ThisWorkbook.Names.Add Name:=SOURCE, RefersTo:=rSource ' THIS gets over the 65,000 row limit
> Application.DisplayAlerts = False
> On Error Resume Next: Sheets(sPivotName).Delete: On Error GoTo 0
> Application.DisplayAlerts = True
> Sheets.Add
> ActiveSheet.Name = sPivotName
> Set pivotTableCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, SourceData:=SOURCE)
> Set rDestination = Range("a3") '
> Set pivotTableReport = pivotTableCache.CreatePivotTable( _
> TableDestination:=rDestination, TableName:=sPivotName)
> With ActiveSheet.PivotTables(sPivotName)
> .AddDataField ActiveSheet.PivotTables(sPivotName).PivotFields(sValuesField), , xlSum
> .PivotFields(sColumnField).Orientation = xlColumnField
> .PivotFields(sRowField).Orientation = xlRowField
> End With
> If sRowField = "Date" Then ' GROUP by Year and Month
> Cells(rDestination.Row + 2, 1).Group Periods:=Array(False, False, False, False, True, False, True)
> End If
> Application.ScreenUpdating = True
> End Sub
>
> 'Called like this, column names MUST match your data :-
>
> Sub MakePivotByRep() 'these must be column names in source sheet
> MakePivotTable sPivotName:="Sales By Rep", _
> sColumnField:="Department", _
> sRowField:="Sales Rep", _
> sValuesField:="Net"
> End Sub
> Sub MakePivotByMonth() 'these must be column names in source sheet
> MakePivotTable "Sales By Month", "Department", "Date", "Net"
> End Sub
>
>
> If you have found this useful or have questions please comment.
>
>
> Regards
>
> Derek Turner
> England
> +++
>
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (3) |
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar