Solved

VBA INTRODUCTION

Posted on 2008-10-29
9
407 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to have microsoft excel chart with slicer for this type of data 4 44
Excel Formula 4 28
Excel Copy Macro down foe each row 4 19
Vlookup formula error 15 11
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

910 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now