I have a model that uses multiple tabs in Contributor Planning.. and creates about 8 different Excel Workbooks. I'd like to merge all of them into one workbook.
I tried using this VBA macro in excel
QuoteSub CombineWorkbooks()
Dim FilesToOpen
Dim x As Integer
On Error GoTo ErrHandler
Application.ScreenUpdating = False
FilesToOpen = Application.GetOpenFilename _
(FileFilter:="Microsoft Excel Files (*.*), *.*", _
MultiSelect:=True, Title:="Files to Merge")
If TypeName(FilesToOpen) = "Boolean" Then
MsgBox "No Files were selected"
GoTo ExitHandler
End If
x = 1
While x <= UBound(FilesToOpen)
Workbooks.Open Filename:=FilesToOpen(x)
Sheets().Move After:=ActiveWorkbook.Sheets _
(ActiveWorkbook.Sheets.Count)
x = x + 1
Wend
ExitHandler:
Application.ScreenUpdating = True
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub
But I get an error saying no worksheets are available when I chose the workbooks created by Contributor. Does anyone else know a solution to merging multiple reports together?