Combine Multiple Workbooks into One - Free excel macro

0

Hi guys.

I've been using the "Combine Multiple Workbooks into One" macro available on the website, and it's great but I need it slightly edited: i'd like only specific columns to be copied from the specified worksheet, namely: A, G-H, J, O-AB. Is there anyway to do it please? Thank you so much

Here's the code I've been using:

Private Declare Function SetCurrentDirectoryA Lib _
     "kernel32" (ByVal lpPathName As String) As Long

Sub ChDirNet(szPath As String)
     SetCurrentDirectoryA szPath
End Sub

Sub Combine_Workbooks_Select_Files()
     Dim MyPath As String
     Dim SourceRcount As Long, Fnum As Long
     Dim mybook As Workbook, BaseWks As Worksheet
     Dim sourceRange As Range, destrange As Range
     Dim rnum As Long, CalcMode As Long
     Dim SaveDriveDir As String
     Dim FName As Variant
    With Application
         CalcMode = .Calculation
         .Calculation = xlCalculationManual
         .ScreenUpdating = False
         .EnableEvents = False
     End With
    SaveDriveDir = CurDir
     ChDirNet "G:\"
    FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xl*), *.xl*", _
                                         MultiSelect:=True)
     If IsArray(FName) Then
         Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
         rnum = 1
         For Fnum = LBound(FName) To UBound(FName)
             Set mybook = Nothing
             On Error Resume Next
             Set mybook = Workbooks.Open(FName(Fnum))
             On Error GoTo 0
             If Not mybook Is Nothing Then
                 On Error Resume Next
                 With mybook.Worksheets(1)
                     Set sourceRange = .Range("A1:A25")
                 End With
                 If Err.Number > 0 Then
                     Err.Clear
                     Set sourceRange = Nothing
                 Else
         If sourceRange.Columns.Count >= BaseWks.Columns.Count Then
                         Set sourceRange = Nothing
                     End If
                 End If
                 On Error GoTo 0
                If Not sourceRange Is Nothing Then
                    SourceRcount = sourceRange.Rows.Count
                    If rnum + SourceRcount >= BaseWks.Rows.Count Then
                         MsgBox "Not enough rows in the sheet. "
                         BaseWks.Columns.AutoFit
                         mybook.Close savechanges:=False
                         GoTo ExitTheSub
                     Else
                         Set destrange = BaseWks.Range("A" & rnum)
                         With sourceRange
                             Set destrange = destrange. _
                                             Resize(.Rows.Count, .Columns.Count)
                         End With
                         destrange.Value = sourceRange.Value
                        rnum = rnum + SourceRcount
                     End If
                 End If
                 mybook.Close savechanges:=False
             End If
         Next Fnum
         BaseWks.Columns.AutoFit
     End If
 ExitTheSub: 
     With Application
         .ScreenUpdating = True
         .EnableEvents = True
         .Calculation = CalcMode
     End With
     ChDirNet SaveDriveDir
 End Sub
Answer
Discuss

Discussion

Welcome to the forum. Most of us here on the forum are not aware of the tutorials published elsewhere on this site, and the guys who understand your question because they wrote the tutorials have no time to modify code for you. The best way forward is to publish your code along with your question. You can edit your question and attach a workbook.
Variatus (rep: 3758) Feb 27, '20 at 10:19 pm
Add to Discussion

Answers

0
Selected Answer

Please replace your existing code with the code below. Make sure you have backups of everything before you run it because it hasn't been tried.

Sub Combine_Workbooks_Select_Files()

    Dim MyPath As String
    Dim SourceRcount As Long, FNum As Long
    Dim MyBook As Workbook, BaseWks As Worksheet
    Dim SourceRange As Range, Destrange As Range
    Dim Rnum As Long, CalcMode As Long
    Dim SaveDriveDir As String
    Dim FName As Variant

    With Application
         CalcMode = .Calculation
         .Calculation = xlCalculationManual
         .ScreenUpdating = False
         .EnableEvents = False
    End With

    SaveDriveDir = CurDir
    ChDirNet "G:\"
    FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xl*), *.xl*", _
                                        MultiSelect:=True)
    If IsArray(FName) Then
        Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
        Rnum = 1
        For FNum = LBound(FName) To UBound(FName)
            Set MyBook = Nothing
            On Error Resume Next
            Set MyBook = Workbooks.Open(FName(FNum))
            On Error GoTo 0
            If Not MyBook Is Nothing Then
                 On Error Resume Next
                 With MyBook.Worksheets(1)
                    Set SourceRange = .Range(.Cells(1, "A"), .Cells(.Rows.Count, "A").End(xlUp))
                    ' I suggest to delete either the code line above or below this text:
                    ' The one above sets the range from A1 to the
                    ' last non-empty cell in column A.
                    ' The one below has a fixed length.
                    Set SourceRange = .Range("A1:A25")         ' =====================
                 End With
                 If Err.Number > 0 Then
                     Err.Clear
                     Set SourceRange = Nothing
                 Else
                    ' BaseWks.Columns.Count = 16384
                    ' since SourceRange = .Range("A1:A25") it has only one column
                    ' Therefore it will never have 16384 columns. The condition can't be met.
                    If SourceRange.Columns.Count >= BaseWks.Columns.Count Then
                        Set SourceRange = Nothing
                    End If
                    On Error GoTo 0

                    If Not SourceRange Is Nothing Then
                        SourceRcount = SourceRange.Rows.Count
                        ' BaseWks.Rows.Count = 1048576
                        If Rnum + SourceRcount >= BaseWks.Rows.Count Then
                            MsgBox "Not enough rows in the sheet."
                            BaseWks.Columns.AutoFit
                            MyBook.Close SaveChanges:=False
                            GoTo ExitTheSub
                        Else
                            Set Destrange = BaseWks.Range("A" & Rnum)
                            With SourceRange
                                Set Destrange = Destrange _
                                               .Resize(.Rows.Count, .Columns.Count)
                            End With
                            Destrange.Value = SourceRange.Value
                            Rnum = Rnum + SourceRcount
                       End If
                    End If
                End If
                MyBook.Close SaveChanges:=False
             End If
        Next FNum

        ' the variables FName and Rnum are recycled below because
        ' they are available. You may prefer to declare descriptively
        ' named alternative variables to replace them.

        ' list of columns you want deleted.
        ' Important! They must be listed in ascending order !!!!
        FName = Array("B", "C", "D", "E", "I", "K", "L", "M", "N")
        With BaseWks
            For Rnum = UBound(FName) To 0 Step -1
                .Columns(FName(Rnum)).EntireColumn.Delete
            Next Rnum
            .Columns.AutoFit
        End With
    End If

ExitTheSub:
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = CalcMode
    End With

    ChDirNet SaveDriveDir
 End Sub

The biggest part of this job was to fix your messed up indentations. Indentations are vital for reading code. In the process I found one misplaced End If which I have moved. In my opinion your code shouldn't have run as it was, but perhaps I'm wrong.

I found the easiest way to achieve what you want is to copy everything same as before but then delete the columns you don't want. You can specify the columns to delete in this line of code.

FName = Array("B", "C", "D", "E", "I", "K", "L", "M", "N")

Note that the columns are those in the target sheet. For now they are the same as in the source. Also observe that the columns for deletion must be listed in ascending order.

Please read all the comments I have added to your code.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login