My apologies for a "repost", my original post was only partial and confusing. Here is a better snippet. This runs fairly slow (30 seconds or so) cycling through 20 workbooks. Any suggestions for improvement would be appreciated; I have left out my dimming of variables at the beginning, as well as my error handling portions, as those all work just fine.
For even MORE details, the user enters file paths into a column of up to 28 filepaths, and then the adjacent column derives the file name from the path. This macro, once initiated via a command button, begins cycling through all those filepaths, opening the document, copying and pasting it to the main workbook, until it reaches an empty cell in the column, to which then it stops.
There are also a few other small parts in there, like updating the last modified date when the button is clicked, as well as taking information from the first opened sheet to let the user know (on the main page) what kind of information is being pulled or has been pulled. Additionally, I often include frequent status bar updates for my impatient users who don't like staring at a spinning cursor without being told what's going on.
Private Sub CommandButton19_Click() Application.DisplayStatusBar = True Application.ScreenUpdating = False 'run faster Application.DisplayAlerts = False 'prevents clipboard prompt Application.Cursor = xlWait On Error GoTo HandleIt Dim RangeDifferentiator As Integer Dim i As Long Dim SheettoCopyLoop Dim FileToCopyLoop Dim FileNameLoop For i = 1 To 28 'start loop (28 because the last row being used is 34. If we need more rows this needs to change) RangeDifferentiator = i + 5 Sheets("Admin").Select SheettoCopyLoop = Range("D" & RangeDifferentiator) FileToCopyLoop = Range("E" & RangeDifferentiator) FileNameLoop = Range("F" & RangeDifferentiator) If FileToCopyLoop <> "" Then Application.StatusBar = "Pulling data for " & SheettoCopyLoop & "..." 'let's us know which one it's pulling Sheets(SheettoCopyLoop).Cells.ClearContents Workbooks.Open Filename:= _ FileToCopyLoop 'Needs optimizing Sheets("Summary").Cells.Copy 'Establishes if this is a MTD or YTD file and up to what month it's pulling If i = 1 Then Workbooks("YTD File.xlsm").Sheets("SM Summaries").Range("C2").Value = ActiveWorkbook.Sheets("Summary").Range("C6").Value Workbooks("YTD File.xlsm").Sheets("SM Summaries").Range("C4").Value = ActiveWorkbook.Sheets("Summary").Range("C5").Value End If 'Needs optimizing Windows("YTD File.xlsm").Activate Sheets(SheettoCopyLoop).Select Sheets(SheettoCopyLoop).Range("A1").Select ActiveSheet.Paste ActiveSheet.Range("A1").Select Windows(FileNameLoop).Close Sheets("Admin").Select Range("A1").Select Else Application.StatusBar = "READY" Application.DisplayAlerts = True Application.ScreenUpdating = True Application.Cursor = xlDefault 'Show last modified date on admin page for the user Workbooks("YTD File.xlsm").Sheets("Admin").Range("F1").Value = "Last modified: " & Date MsgBox "Data pull complete!", vbInformation, "Success!" Exit Sub End If Next i Exit Sub 'The following is all error handling to help show what went wrong and where HandleIt: If SheettoCopyLoop <> "" Then errorhandler = MsgBox("Oops! An error has occured on page " & SheettoCopyLoop & ". Check to make sure file exists/file path is correct.", vbExclamation, "Error!") Application.StatusBar = "Error occured on page " & SheettoCopyLoop 'Show where error occured on status bar if applicable Else errorhandler = MsgBox("Oops! An unknown error has occured.", vbCritical, "Error!") Application.StatusBar = "Unknown error" End If Worksheets("Admin").Activate Range("H7").Select Application.DisplayAlerts = True Application.ScreenUpdating = True Application.Cursor = xlDefault Exit Sub End Sub