In every case all information on the underlying worksheets should be copied and combined in the new Workbook as shown below. Display Alerts = True End Sub Not necessary anymore, thank you for your post. I suggest that you start solving your task by trying to understand those ~50 lines of code in the and perhaps spawn some new more concrete coding questions.
I found this Macro / Add-In online that gets me close to what I need using the open files add in choice. Worksheets(total) Go To exit For: Next sheet exit For: Workbooks(file Name). So far you are doing quite well Thanks, I agree and understand some effort and a template for the problem is needed.
Or if you want all the options in a nice user interface check ou this add-in : RDBMerge, Excel Merge Add-in for Excel for Windows There are a few things you must change before you can run the code Fill in the path to the folder Note: the code above use the function RDB_Last, copy this function also in your code module if you use it. It is also possible to set the start folder with Ch Drive and Ch Dir but I choose to use the Set Current Directory A function in this example because it also is working with network folders. Get Open Filename(filefilter:="Excel Files (*.xl*), *.xl*", _ Multi Select:=True) If Is Array(FName) Then For Fnum = LBound(FName) To UBound(FName) Set mybook = Nothing On Error Resume Next Set mybook = Workbooks. Number With source Range Set destrange = destrange. Column On Error Go To 0 Case 3: On Error Resume Next lrw = rng.
You find the function in the last section of this page. Note: Copy all code below in a normal module of your workbook #If VBA7 Then Declare Ptr Safe Function Set Current Directory A Lib _ "kernel32" (By Val lp Path Name As String) As Long #Else Declare Function Set Current Directory A Lib _ "kernel32" (By Val lp Path Name As String) As Long #End If Sub Ch Dir Net(sz Path As String) Set Current Directory A sz Path End Sub Sub Basic_Example_2() Dim My Path As String Dim Source Rcount As Long, Fnum As Long Dim mybook As Workbook, Base Wks As Worksheet Dim source Range As Range, destrange As Range Dim rnum As Long, Calc Mode As Long Dim Save Drive Dir As String Dim FName As Variant With Application Calc Mode = . Open(FName(Fnum)) On Error Go To 0 If Not mybook Is Nothing Then On Error Resume Next With mybook. Find(What:="*", _ after:=rng.cells(1), _ Lookat:=xl Part, _ Look In:=xl Formulas, _ Search Order:=xl By Rows, _ Search Direction:=xl Previous, _ Match Case:=False).
Note: The workbook with the code must be outside the merge folder Tip 1: Useful Workbooks. Close savechanges:=False End If Next Fnum Base Wks. Auto Fit End If Exit The Sub: You find this macro in the workbook that you can download on this page.
Open arguments )will avoid the message do you want to update the links or not "0 Doesn't update any references"Use 3 instead of 0 if you want to update the links. Value rnum = rnum Source Rcount End If End If mybook. This example will filter a range on a worksheet in every workbook in the folder and copy the filter results to a new workbook.
Note: This example use the function Last Row Note: You can use the tips above also in this example In example 1 you can see that you can copy all cells on a worksheet with this line: But what if we do not want to copy the same header row each time. If you have no idea where to paste the code then check out this page. Where do I paste the code that I find on the internet You can also find the information from this page in this article on MSDN: Consolidating Data from Multiple Worksheets into a Summary Worksheet in Excel a zip file with a workbook with the code from this page to test the examples. See the VBA help for more information about the Workbooks. Close savechanges:=False End If Next Fnum Base Wks. Auto Fit End If Exit The Sub: This example will past the data next to each other. There are five code lines that you must change before you run the code(see the code in the VBA editor) Dim lrw As Long Dim lcol As Integer Select Case choice Case 1: On Error Resume Next RDB_Last = rng.Open arguments Tip 2: Merge from all Files with a name that start with for example week, use this then Before you restore Screen Updating, Calculation and Enable Events also add this line Application. Cells(1) Download a zip file with a workbook with the code from this page to test the examples: Download Merge Dir Or download this file with FSO code that have a option to include subfolders and a option to merge data from all worksheets in each workbook. In column A you see the data from the first workbook and in Column B the data from the next and in..... Find(What:="*", _ after:=rng.cells(1), _ Lookat:=xl Part, _ Look In:=xl Formulas, _ Search Order:=xl By Rows, _ Search Direction:=xl Previous, _ Match Case:=False).