![]() The information from each sheet is pasted into the destination sheet at the last occupied row on the worksheet. For Each wb In Application.Workbooks If wb.Name strDestName And wb.Name "PERSONAL.XLSB" Then wb.Close False End If Next wb'remove sheet one from the destination workbook Application.DisplayAlerts = False Sheets("Sheet1").Delete Application.DisplayAlerts = True'clean up the objects to release the memory Set wbDestination = Nothing Set wbSource = Nothing Set wsSource = Nothing Set wb = Nothing'turn on the screen updating when complete Application.ScreenUpdating = FalseExit Subeh: MsgBox Err.DescriptionEnd SubĬlick on the Macro dialog box to run the procedure from your Excel screen. Sub CombineMultipleFiles()On Error GoTo eh'declare variables to hold the objects required Dim wbDestination As Workbook Dim wbSource As Workbook Dim wsSource As Worksheet Dim wb As Workbook Dim sh As Worksheet Dim strSheetName As String Dim strDestName As String'turn off the screen updating to speed things up Application.ScreenUpdating = False'first create new destination workbook Set wbDestination = Workbooks.Add'get the name of the new workbook so you exclude it from the loop below strDestName = wbDestination.Name'now loop through each of the workbooks open to get the data but exclude your new book or the Personal macro workbook For Each wb In Application.Workbooks If wb.Name strDestName And wb.Name "PERSONAL.XLSB" Then Set wbSource = wb For Each sh In wbSource.Worksheets sh.Copy After:=Workbooks(strDestName).Sheets(1) Next sh End If Next wb'now close all the open files except the new file and the Personal macro workbook. ![]() These files are the ONLY Excel Files that should be open.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |