Problem with the combine all Excel files in one

Vagelisr

New Member
Joined
Sep 22, 2016
Messages
28
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
Hi to all.
I want in my excel files to do 2 diff jobs.
The first one is to open a specific folder, find all Excel files, open and copy the data from a specific sheet and paste then in my main excel file (Merge Sub)
The second one is to take my main excel files and when the data from the a parameter column change save them in a new excel files (Split Sub)
The problem is: Using the merge sub everything shows that work OK but when i use also the split sub the Excel that creating from this Sub Freeze when i try to close them.
The same happens also when i make a copy paste myself in a new excel. (I make a test in the split subroutine and work without problem).
So the problem is in my Merge Sub and here i want your help.

Thank you all for your time.......

This is the code i use
Code:
Option Explicit
Public Folder As String
Public FirstExcel As Boolean

Sub Merge()
    Dim arg As String
    Dim LastMainRow As Integer
    Dim FirstLine As Integer
    Dim myExtension As String
    Dim fname As String
    Dim myfiles As String
    Dim lRow As Integer
    Dim lCol As Integer
    Dim FirstCol As Integer
    Dim WorkSheetName As String
    FirstLine = Worksheets("Parm").Cells(2, 2).Value + 1
    WorkSheetName = Worksheets("Parm").Cells(4, 2).Value
    Folder = BrowseForFolder & "\"
    If Folder <> "" Then
        myExtension = "*.xls"
        myfiles = Dir(Folder & myExtension)
        'Row = 1
        FirstCol = 1
        Worksheets("DataAll").Activate
        ActiveSheet.Cells.Clear
        FirstExcel = False
        Do While myfiles <> ""
            'Open the Specific Excell
            Workbooks.Open (Folder & myfiles)
            'ActiveWorkbook.RunAutoMacros xlAutoOpen
            fname = ActiveWorkbook.Name
            
            'Copy the Specific Excell
            Workbooks(myfiles).Activate
            Worksheets(WorkSheetName).Activate
            If FirstExcel = False Then
                ActiveSheet.Cells.Select
                Selection.Copy
            Else
                lRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row 'Last Row
                lCol = ActiveSheet.Cells(2, Columns.Count).End(xlToLeft).Column 'Last Column
                ActiveSheet.Range(ActiveSheet.Cells(FirstLine, FirstCol), ActiveSheet.Cells(lRow, lCol)).Select
                Selection.Copy
            End If
             
            'Paste the Specific Excell to Data
            Workbooks("File_Merge_Split.xls").Activate
            Worksheets("DataAll").Activate
            LastMainRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row 'Last Row
            If FirstExcel = True Then
                LastMainRow = LastMainRow + 1
            Else
                FirstExcel = True
            End If
            ActiveSheet.Cells(LastMainRow, 1).Select
            ActiveSheet.Paste
            
            'Close Copy File
            Application.DisplayAlerts = False
            Workbooks(myfiles).Close SaveChanges:=False
            Application.DisplayAlerts = True
            
            myfiles = Dir
        Loop
    End If
End Sub

Sub Split()
    Dim lRow As Integer
    Dim lCol As Integer
    Dim i As Integer
    Dim First_Range_Line As Integer
    Dim Last_Range_Line As Integer
    Dim Compare_Data As String
    Dim New_Excel_Column_Name1 As String
    Dim New_Excel_Column_Name2 As String
    Dim New_Excel_Name As String
    Dim PathForSave As String
    Dim Change_Column As String
    Dim Current_Workbook_Name As String
    Dim New_Workbook_Name As String
    Dim SumiRow As Integer
    
    Current_Workbook_Name = ActiveWorkbook.Name
    
    First_Range_Line = Worksheets("Parm").Cells(2, 2).Value + 1 '4
    New_Excel_Column_Name1 = Worksheets("Parm").Cells(5, 2).Value 'A
    New_Excel_Column_Name2 = Worksheets("Parm").Cells(5, 3).Value 'B
    Change_Column = Worksheets("Parm").Cells(3, 2).Value 'A
    New_Workbook_Name = Worksheets("Parm").Cells(4, 2).Value 'Data
    
    Worksheets("DataAll").Activate
    PathForSave = Application.ActiveWorkbook.Path & "\"
    
    Compare_Data = ActiveSheet.Cells(First_Range_Line, Change_Column).Value
    lRow = ActiveSheet.Range(Change_Column & Rows.Count).End(xlUp).Row 'Last Row
    lCol = ActiveSheet.Cells(2, Columns.Count).End(xlToLeft).Column 'Last Column
    For i = 5 To lRow + 1
        If ActiveSheet.Cells(i, Change_Column).Value <> Compare_Data Then
            New_Excel_Name = ActiveSheet.Cells(i - 1, New_Excel_Column_Name1).Value & "-" & ActiveSheet.Cells(i - 1, New_Excel_Column_Name2).Value
            'Copy_Header
            ActiveSheet.Range(ActiveSheet.Cells(1, 1), ActiveSheet.Cells(3, lCol)).Select
            Selection.Copy
            
            'Create New Excel with Name
            Workbooks.Add
            ActiveSheet.Name = New_Workbook_Name
            'Paste_Header
            ActiveSheet.Paste
            ActiveWorkbook.SaveAs PathForSave & New_Excel_Name
            
            Workbooks(Current_Workbook_Name).Activate

            Worksheets("DataAll").Activate
            Last_Range_Line = i - 1
            'Copy_Data
            ActiveSheet.Range(ActiveSheet.Cells(First_Range_Line, 1), ActiveSheet.Cells(Last_Range_Line, lCol)).Select
            Selection.Copy
            
            'Paste_Data
            Workbooks(New_Excel_Name & ".xls").Activate '------------------
            Worksheets(New_Workbook_Name).Activate
            ActiveSheet.Cells(4, 1).Select
            ActiveSheet.Paste
            
            'Save & Close New Excel
            SumiRow = ActiveSheet.Range(Change_Column & Rows.Count).End(xlUp).Row 'Last Row for Sum
            ActiveSheet.Cells(2, 62).Formula = "=SUM(BJ4:BJ" & SumiRow & ")"
            
            Application.DisplayAlerts = False
            ActiveWorkbook.Close SaveChanges:=True
            Application.DisplayAlerts = True
         
            Compare_Data = ActiveSheet.Cells(i, Change_Column).Value
            First_Range_Line = i
         
        End If
    Next i
End Sub

Function BrowseForFolder(Optional OpenAt As Variant) As Variant
    Dim ShellApp As Object
    Set ShellApp = CreateObject("Shell.Application"). _
    BrowseForFolder(0, "Ðáñáêáëþ åðéëÝîôå öÜêåëï", 0, OpenAt)
    On Error Resume Next
    BrowseForFolder = ShellApp.self.Path
    On Error GoTo 0
    Set ShellApp = Nothing
    Select Case Mid(BrowseForFolder, 2, 1)
    Case Is = ":"
        If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid
    Case Is = "\"
        If Not Left(BrowseForFolder, 1) = "\" Then GoTo Invalid
    Case Else
        GoTo Invalid
    End Select
    Exit Function
Invalid:
    BrowseForFolder = False
End Function
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Forum statistics

Threads
1,223,903
Messages
6,175,287
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top