Solved

VBA INTRODUCTION

Posted on 2008-10-29
9
404 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 80

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 80

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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 80

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 80

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

747 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

10 Experts available now in Live!

Get 1:1 Help Now