Solved

VBA INTRODUCTION

Posted on 2008-10-29
9
411 Views
Last Modified: 2012-06-21
I was able to find the answer for my excel question but I don't know how to use the code. I inputted it into the visual basics but do i need to change the workbook name and such b/c right now it reads compile error: Invalid ouside procedure. I am a beginner user and any help wold be great!

Here is the code i was trying to use
Dim wb As testing.xls, ws As Worksheet, sh As Worksheet
Dim pt As PivotTable, ptf As PivotField, pti As PivotItem, pfn As Variant
Dim pt_n As PivotTable, pt_rng As Variant

Application.ScreenUpdating = False
Sheets("Sheet2").Select
Set wb = ActiveWorkbook
If Not TargetSheet Is Nothing Then
    Set ws = TargetSheet
Else
    Set ws = ActiveSheet
End If

Set pt_n = ws.PivotTables(1)

On Error Resume Next

For Each sh In wb.Worksheets(Array("2a", "2b", "2c", "3a", "3b", "3c", "4a", "4b", "4c", "4d", "5", "6a", "6b", "6c", "6d", "7"))
    If sh.Name <> ws.Name Then
        For Each pt In sh.PivotTables
            For Each pti In pt.RowFields("month").PivotItems
                pti.Visible = pt_n.RowFields("month").PivotItems(pti.Name).Visible
            Next pti
        Next pt
    End If
Next sh

On Error GoTo 0

End Sub
End Sub
0
Comment
Question by:derekaly
  • 4
  • 4
9 Comments
 
LVL 20

Expert Comment

by:pari123
ID: 22835920
Hi Derek,
What is the same of the excel sheet that you was using with this ? What are the tab (Sheet) names?
There are some errors in the code u pasted... Please recheck them with the original macro.
  • Dim wb As testing.xls -
  • End Sub - Repeated Twice.
    End Sub
Thanks,
Ardhendu
0
 
LVL 81

Expert Comment

by:byundt
ID: 22836415
derekaly,
I see that you are a new member of Experts-Exchange. Welcome!

Questions like this belong in the Excel zone rather than Visual Basic. Fortunately, you posted it in both places.

As Ardhendu pointed out, you had a couple of issues with the code you posted. I tried to address them in the snippet below. I don't know if the code will do what you want--but at least it passes a compile check now.

You need to put the code in a regular module sheet. To install a sub in a regular module sheet:
1) ALT + F11 to open the VBA Editor
2) Use the Insert...Module menu item to create a blank module sheet
3) Paste the suggested code in this module sheet
4) ALT + F11 to return to the spreadsheet

To run a sub or macro:
5) ALT + F8 to open the macro window
6) Select the macro
7) Click the "Run" button

If the above procedure doesn't work, then you need to change your macro security setting. To do so, open the Tools...Macro...Security menu item. Choose Medium, then click OK.


The next issue should be to make sure that the code is doing what you need. As this is your first question on Experts-Exchange, where did the code come from? If you could provide a link to the question, that would help provide some context. It would also be helpful if you could post a sample workbook with the data and PivotTables that you want to manipulate.

As I read the code, it is looking at every PivotTable on worksheets 2a, 2b, 2c, etc. and making all the items under month become visible if they were also visible in a certain PivotTable on another worksheet. Is this what you want to accomplish?

Brad

Sub PivotTabler()
Dim wb As Workbook, ws As Worksheet, sh As Worksheet, TargetSheet As Worksheet
Dim pt As PivotTable, ptf As PivotField, pti As PivotItem, pfn As Variant
Dim pt_n As PivotTable, pt_rng As Variant
 
Application.ScreenUpdating = False
Set TargetSheet = Worksheets("Sheet1")
Sheets("Sheet2").Select
Set wb = ActiveWorkbook
If Not TargetSheet Is Nothing Then
    Set ws = TargetSheet
Else
    Set ws = ActiveSheet
End If
 
Set pt_n = ws.PivotTables(1)
 
On Error Resume Next
 
For Each sh In wb.Worksheets(Array("2a", "2b", "2c", "3a", "3b", "3c", "4a", "4b", "4c", "4d", "5", "6a", "6b", "6c", "6d", "7"))
    If sh.Name <> ws.Name Then
        For Each pt In sh.PivotTables
            For Each pti In pt.RowFields("month").PivotItems
                pti.Visible = pt_n.RowFields("month").PivotItems(pti.Name).Visible
            Next pti
        Next pt
    End If
Next sh
 
On Error GoTo 0
 
End Sub

Open in new window

0
 

Author Comment

by:derekaly
ID: 22841327
Hi Brad,
Thank you for the detailed explanation!
You are correct, I would like to look at every PivotTable on worksheets 2a, 2b, 2c, etc. and making all the items under month become visible if they were also visible in a certain PivotTable on another worksheet. Right now I only have worksheets 2a and 2b in the workbook.

When trying to run the macro following error message appears:
Run-Time error '1004' : method 'Pivot Tables' of object_'worksheet' failed.

I have attached the work book to this file for your review! THanks so much for your help!
testing.xls
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 81

Expert Comment

by:byundt
ID: 22841867
derekaly,
See if this tweak to your code works better for you. It works for me with Excel 2003.

I wasn't comfortable with the Resume Next feature if the worksheet didn't exist at all, so I looped through an array of strings instead of worksheets.

Brad
Sub PivotTabler()
Dim wb As Workbook, ws As Worksheet, sh As Worksheet, TargetSheet As Worksheet
Dim pt As PivotTable, ptf As PivotField, pti As PivotItem, pfn As Variant, vSh As Variant
Dim pt_n As PivotTable, pt_rng As Variant
 
Application.ScreenUpdating = False
Set TargetSheet = Worksheets("2a")
Sheets("2b").Select
Set wb = ActiveWorkbook
If Not TargetSheet Is Nothing Then
    Set ws = TargetSheet
Else
    Set ws = ActiveSheet
End If
 
Set pt_n = ws.PivotTables(1)
 
On Error Resume Next
 
For Each vSh In Array("2a", "2b", "2c", "3a", "3b", "3c", "4a", "4b", "4c", "4d", "5", "6a", "6b", "6c", "6d", "7")
    Set sh = Nothing
    Set sh = wb.Worksheets(vSh)
    If vSh <> ws.Name And Not sh Is Nothing Then
        For Each pt In sh.PivotTables
            For Each pti In pt.RowFields("month").PivotItems
                pti.Visible = pt_n.RowFields("month").PivotItems(pti.Name).Visible
            Next pti
        Next pt
    End If
Next
 
On Error GoTo 0
 
End Sub

Open in new window

0
 

Author Comment

by:derekaly
ID: 22843652
Hi Brad!

Thank you for your help again! That code definitely worked. I am onto the next step and trying to change the color of the bars to be all the same, but upon updating it all reconverts to the default blue. Would you have any suggestions how to do this?

I have encountered the chart_calculate code on the website, but not sure how to implement color formatting. What do you think?
testing.xls
0
 
LVL 81

Expert Comment

by:byundt
ID: 22843781
derekaly,
PivotTables are pretty obnoxious about formatting things their way. It doesn't surprise me that a PivotChart is the same way.

Why not combine the PivotTable refresh and PivotChart formatting into the same macro? First you refresh the data, then you change the colors of the bars.

Had you posted your code for the chart formatting, I'd have integrated it for you.

Brad
0
 

Author Comment

by:derekaly
ID: 22844782
Hi Brad,

After fiddling with it abit I was able to get the macro working for the first chart on the Chart 2a work book with the following coded, but not i'm having difficutly apply the same code to the second graph, would you be able to assist me with incorporating hte color changes to 1 marco?

Sub colour()
'
' colour Macro
'
' Keyboard Shortcut: Ctrl+w
'
    ActiveSheet.ChartObjects("Chart 3").Activate
    ActiveChart.SeriesCollection(1).Select
    With Selection.Border
        .Weight = xlThin
        .LineStyle = xlAutomatic
    End With
    Selection.Shadow = False
    Selection.InvertIfNegative = False
    With Selection.Interior
        .ColorIndex = 3
        .Pattern = xlSolid
    End With
    ActiveChart.SeriesCollection(3).Select
    With Selection.Border
        .Weight = xlThin
        .LineStyle = xlAutomatic
    End With
    Selection.Shadow = False
    Selection.InvertIfNegative = False
    With Selection.Interior
        .ColorIndex = 4
        .Pattern = xlSolid
    End With
    ActiveChart.SeriesCollection(2).Select
    With Selection.Border
        .Weight = xlThin
        .LineStyle = xlAutomatic
    End With
    Selection.Shadow = False
    Selection.InvertIfNegative = False
    With Selection.Interior
        .ColorIndex = 6
        .Pattern = xlSolid
    End With
End Sub
0
 
LVL 81

Accepted Solution

by:
byundt earned 50 total points
ID: 22845054
derekaly,
Here is code to format all charts on all worksheets (except certain ones) with the colors you specified. All selecting and activating has been eliminated, which is good coding practice.

Note the use of the With block to simplify the code, and the Choose function to get the colors for the different series.

Brad
Sub colour()
'
' colour Macro
'
' Keyboard Shortcut: Ctrl+w
'
Dim cht As ChartObject
Dim ws As Worksheet
Dim ser As Series
Dim i As Long
 
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets    'Loop through all the worksheets
    Select Case ws.Name
    Case "Sheet1", "Sheet2"     'Leave charts on these sheets alone
    Case Else
        For Each cht In ws.ChartObjects
            i = 0
            For Each ser In cht.Chart.SeriesCollection
                i = i + 1
                With ser
                    .Border.Weight = xlThin
                    .Border.LineStyle = xlAutomatic
                    .Shadow = false
                    .InvertIfNegative = False
                    .Interior.ColorIndex = Choose(i, 3, 6, 4)
                    .Interior.Pattern = xlSolid
                End With
            Next
        Next
    End Select
Next
Application.ScreenUpdating = True
End Sub

Open in new window

0
 

Author Comment

by:derekaly
ID: 22879875
Hi Brad,
I was trying to use the Pivot Tabler Mcro, which you helped me with last week but it doesn't work anymore. Would you be able to provide some assistance here? Thanks

Sub PivotTabler()
Dim wb As Workbook, ws As Worksheet, sh As Worksheet, TargetSheet As Worksheet
Dim pt As PivotTable, ptf As PivotField, pti As PivotItem, pfn As Variant, vSh As Variant
Dim pt_n As PivotTable, pt_rng As Variant
 
Application.ScreenUpdating = False
Set TargetSheet = Worksheets("2a")
Sheets("2b").Select
Set wb = ActiveWorkbook
If Not TargetSheet Is Nothing Then
    Set ws = TargetSheet
Else
    Set ws = ActiveSheet
End If
 
Set pt_n = ws.PivotTables(1)
 
On Error Resume Next
 
For Each vSh In Array("2a", "2b", "2c", "3a", "3b", "3c", "4a", "4b", "4c", "4d", "5", "6a", "6b", "6c", "6d", "7")
    Set sh = Nothing
    Set sh = wb.Worksheets(vSh)
    If vSh <> ws.Name And Not sh Is Nothing Then
        For Each pt In sh.PivotTables
            For Each pti In pt.RowFields("year").PivotItems
                pti.Visible = pt_n.RowFields("year").PivotItems(pti.Name).Visible
            Next pti
        Next pt
    End If
Next
 
On Error GoTo 0
 
End Sub
Also, I am basing the pivot tables on worksheet2b on the table in 2a. If I change the datasource in 2a, is there an macro to automatically update the data sourcein the tables from 2b?

Thank you for your help
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question