+ Reply to Thread
Results 1 to 3 of 3

Redimming an array of worksheets if a worksheet doesn't exist to avoid errors

Hybrid View

  1. #1
    Registered User
    Join Date
    05-25-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    11

    Redimming an array of worksheets if a worksheet doesn't exist to avoid errors

    I'm creating pivot tables from each sheet in a workbook. Sometimes I may run across a situation when one sheet doesn't exist. I'd like to be able in that instance to just ignore the missing element and move on to the next element in the array without the script continuing and causing an error. I'm very new to arrays and could use any help I could get. Thanks in advance,
    Jimalya


    OPTION EXPLICIT
    Sub CreateHrsPivot()
    Application.ScreenUpdating = False
    Dim aSheet As String
    Dim myHours As Worksheet
    Dim i As Long, lastRow As Long, nCol As Integer
    Dim mySheets()
    Dim myPath As String
    
    Dim myCharts As Worksheet
    Set myHours = Sheets.Add
    myHours.Name = "Daily Totals"
    'Application.Calculation = xlCalculationManual
    mySheets = Array("VOL1", "VOL2", "VOL3", "VOL4")
    
    
    
    nCol = 1
    
    For i = 0 To UBound(mySheets)
    
    
    
        ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
            mySheets(i) & "!R1C1:R65536C16", Version:=xlPivotTableVersion10). _
            CreatePivotTable TableDestination:=Sheets("Daily Totals").Cells(1, nCol), TableName:= _
            "PivotHours" & i, DefaultVersion:=xlPivotTableVersion10
    
        With ActiveSheet.PivotTables("PivotHours" & i).PivotFields("Due Date")
            .Orientation = xlRowField
            .Position = 1
        End With
        ActiveSheet.PivotTables("PivotHours" & i).AddDataField ActiveSheet.PivotTables( _
            "PivotHours" & i).PivotFields("Daily Total"), "Count of Daily Total", xlCount
        With ActiveSheet.PivotTables("PivotHours" & i).PivotFields("Count of Daily Total")
            .Caption = mySheets(i)
            .Function = xlSum
            End With
        With ActiveSheet.PivotTables("PivotHours" & i)
            .ColumnGrand = False
            .RowGrand = False
        End With
    
        With ActiveSheet.PivotTables("PivotHours" & i).PivotFields("Due Date")
            .PivotItems("(blank)").Visible = False
        End With
    
    
    nCol = nCol + 4
    
    Next i

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Redimming an array of worksheets if a worksheet doesn't exist to avoid errors

    This isn't really an array issue, and resizing the array wouldn't help.

    You need to check if the sheet exists, if it does then continue on and create the pivot table, otherwise skip that and move on to the next sheet.

    Here's one way.
    For I = LBound(mySheets) To UBound(mySheets)
    
       If Not IsError(Evaluate("'" & mySheets(I) & "'!A1") Then
       ' sheet exists
       
           ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
               mySheets(i) & "!R1C1:R65536C16", Version:=xlPivotTableVersion10). _
               CreatePivotTable TableDestination:=Sheets("Daily Totals").Cells(1, nCol), TableName:= _
               "PivotHours" & i, DefaultVersion:=xlPivotTableVersion10
    
           ' rest of code
    
       End If
    
    Next I
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    05-25-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Redimming an array of worksheets if a worksheet doesn't exist to avoid errors

    Thanks alot Norie! That did the trick. It makes sense about the arrays. That's probably why I couldn't find an answer. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to delete a worksheet if it doesn't exist in a list
    By iamrickdeans in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-22-2013, 09:46 AM
  2. Replies: 2
    Last Post: 08-19-2006, 09:45 PM
  3. [SOLVED] Excel Throwing Circular Errors When No Errors Exist
    By MDW in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-10-2006, 09:20 AM
  4. Replies: 4
    Last Post: 06-18-2006, 01:10 PM
  5. search for worksheet, insert new if doesn't exist
    By cereldine in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-21-2006, 12:15 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1