The Commit button commits data only in active sheet, so I'm asked to make a button to commit the entire book.
Firstly, maybe there is an option to make the Commit button commit the entire book (like the "Refresh" one)?
Secondly, I've made a VBA script to do this. The idea is to loop through all "tm1\\_X_R" named ranges to point the rows of each report book and to commit it
Code: Select all
Option Explicit
Option Compare Text
Sub CommitAll()
Dim nm As Name
Dim sFilters As String
Dim sRange As String
sFilters = "_R"
For Each nm In ActiveWorkbook.Names
'Locate "tm1\\_X_R" named range
If InStr(nm.Name, sFilters) > 1 Then
sRange = nm.Name
'Print where we are
Debug.Print (sRange)
Reporting.GetCurrentReport(Range(sRange)).Commit True
Reporting.Wait
End If
Next nm
End Sub
Appreciate any help on this.
Or, if somebody has working VBA for this - please share.