Vba to open a series of workbooks and update one tab

[p4nny]

Hi,

I hope someone can help..

I would like some vba code to open a series of workbooks (based off a list of workbook names) and up date one tab (called raw data), refresh pivots, refresh formulae and close. I would like this to run until the list of workbook names is blank.

Hope this makes sense. if you need more info or I'm not being clear please let me know

Thanks in advance

Anthony

 

[JLGWhiz]

See if this does what you want.

Sub update()
Dim wb As Workbook, sh As Worksheet, rng As Range, fPath As String
Set rng = Sheets(2).Range("A2", Sheets(2).Cells(Rows.Count, 1).End(xlUp))
fPath = ThisWorkbook.Path
If Right(fPath, 1) <> "/" Then fPath = fPath & "/"
For Each c In rng
On Error GoTo SKIP:
    Set wb = Workbooks.Open(fPath & c.Value) 'If your list does not include the file extion, this could error.
    Set sh = wb.Sheets("raw data")
    For Each Pivot In sh.PivotTables
        Pivot.RefreshTable
        Pivot.update
    Next
SKIP:
    If Err.Number > 0 Then
        MsgBox Err.Number & ":  " & Err.Description & vbLf & wb.Name & " was not found, or the worksheet evoked an error."
    End If
Next
End Sub

This code is untested.

 

[jjlwork]

I can't write it for you but I will share something similar. Note I am writing this to be reusable for my other reports and thus have a worksheet called config. It uses the config worksheet to piece together my file names to open. As a result may be a little more complex than you need and TBH I still haven't finished it myself so it is definitely not copy and paste but may guide you in the right direction.

Sub Main()
'
' Macro to populate SCB_3
'
Application.DisplayAlerts = False

Dim configSheet As Worksheet
Dim linkedFiles As String
Dim dataFiles() As String
Dim expectedDateSuffix As String
Dim dataDirectory As String
Dim dataSheet As Worksheet
Dim formulas As String

'Get configuration
Set configSheet = Config
    configSheet.Activate
    expectedDateSuffix = Cells(5, "B").Value
    dataDirectory = Cells(6, "B").Value + Left(expectedDateSuffix, 7) + "\"
'Set dataSheet = Cells(7, "B").Value
    formulas = Cells(8, "B").Value
    firstDataRow = 2
    dataFiles = Split(Cells(4, "B").Value, ";") 'Create array of data files
    
    'create full path for each dataFile
    For a = 0 To UBound(dataFiles)
        dataFiles(a) = dataFiles(a) + expectedDateSuffix + ".xlsx"
    Next a
  
    
   
    For a = 0 To UBound(dataFiles)
        Workbooks.Open (dataDirectory + dataFiles(a))
        Windows(dataFiles(a)).Activate
        Worksheets("Agent Summary").Activate
        
        'Filter date to current Month
        Worksheets("Agent Summary").PivotTables("PivotTable5").PivotFields("Date Range").PivotFilters.Add _
        Type:=xlAfter, Value1:="2015-01-31"
        
        'Add in "AgentID Field"
        With Worksheets("Agent Summary").PivotTables("PivotTable5").PivotFields("AgentID")
            .Orientation = xlRowField
            .Position = 5
        End With
        
        'If it is the Moncton file remove Avail Time
        If dataFiles(a) = dataFiles(UBound(dataFiles)) Then
            Worksheets("Agent Summary").PivotTables("PivotTable5").PivotFields("AvailTime").Orientation = _
            xlHidden
        End If
         ActiveSheet.PivotTables("PivotTable5").PivotCache.Refresh
        
        'Find the last data row and column and copy the data
        lastRow = Worksheets("Agent Summary").Range("A" & Rows.Count).End(xlUp).Row
        MsgBox lastRow
        
        Range(Cells(3, 1), Cells(lastRow, 26)).Copy
        
        'Paste to lastrow+1 in auto_scb_3
        Windows("auto_scb_3.xlsb").Activate
        Data.Activate
        lastRow = Data.Range("A" & Rows.Count).End(xlUp).Row
        Range("A" + CStr(lastRow)).PasteSpecial (xlPasteValues)
    Next a
    
    'Paste Formulas:
    Range("AA2", "AU2").Copy
    Range("AA5", "AU5").PasteSpecial (xlPasteFormulas)
    End Sub