All, I've built a macro (not super experienced with vba) that will copy and paste up to 12 worksheets from various excel models into a consolidator model. Right now, the macro runs by clicking a button, after which the macro will ask me if I want to proceed, then copy and paste all files into the consolidator workbook. The problem is, I don't always need to consolidate all files. I may only need to update 1 or 2 files. Wondering if there is a way to adjust my code so that a dialog box pops up at the beginning of the macro, allowing me to select which workbooks I want to consolidate. Below is my code. Any ideas are much appreciated.
Sub Rollup() 'Application.ScreenUpdating = False Dim Loan As Integer Dim Count As Integer Dim Rangecount As Integer Dim File As String Dim Buttons As Integer, Result As Integer Count = Range("Count") Rangecount = Range("Rangecount") Control = Range("Control") Range_control = Range("Range_Control") Buttons = vbYesNo + vbQuestion + vbDefaultButton1 Result = MsgBox("Data from " & Count & " files will be extracted. Proceed?", Buttons) If Result = vbYes Then Clear_Macro For Loan = 1 To Count Step 1 Application.Goto Reference:="control" ActiveCell.Value = Loan File = Range("Rollfile") Workbooks.Open Filename:=File, UpdateLinks:=0 'Calculate ActiveWindow.ActivateNext For Pastenumber = 1 To Rangecount Step 1 Application.Goto Reference:="Range_Control" ActiveCell.Value = Pastenumber Dim MyRef As String MyRef = Range("ActiveName").Value Application.Goto Reference:=MyRef 'ActiveCell.Offset(Loan, Pastenumber).Range("a1").Select Pastetarget = Range("Pasterange") ActiveWindow.ActivateNext Worksheets(Pastetarget).Activate Cells.Copy Cells.FormatConditions.Delete 'Cells.Select 'Selection.Copy 'Range(Pastetarget).Copy ActiveWindow.ActivateNext Application.DisplayAlerts = False Selection.PasteSpecial Paste:=xlValues Selection.PasteSpecial Paste:=xlFormats Next Pastenumber ActiveWindow.ActivateNext Application.CutCopyMode = False ActiveWorkbook.Close SaveChanges:=False Next Loan MsgBox ("Data extracted.") Else MsgBox "You chose to abort the operation" End If End Sub