In this module, you will learn how to do the most basic combining and merging tasks.

You will learn:

  • How to combine a given range or selection into a single list
  • How to combine books and sheets
  • How to combine the books in a folder
  • How to merge two tables
  • And much more…

If you don’t find your basic piece of VBA combining code here, you can submit your own VBA snippet ideas at the bottom of this page. I will develop it and update this page.

How to combine a given range into a single list

Sub CombineRangeInOneList()
'How to combine a given range into a single list
 Dim MyRange As Range
 Dim i As Integer
 Dim LastRow As Integer
 On Error Resume Next
 Set MyRange = Range("A1:D3")
 If MyRange Is Nothing Then Exit Sub
 LastRow = MyRange.Columns(1).Rows.Count + 1 'gets the last empty row of the range
 For i = 2 To MyRange.Columns.Count
 Range(MyRange.Cells(1, i), MyRange.Cells(MyRange.Columns(i).Rows.Count, i)).Cut 'cuts each column in the range starting on the 2nd
 ActiveSheet.Paste Destination:=MyRange.Cells(LastRow, 1) 'pastes the data on the last empty row
 LastRow = LastRow + MyRange.Columns(i).Rows.Count 'updates the last row
 Next
End Sub

How to combine the selection into a single list

Sub CombineSelectionInOneList()
'How to combine the selection into a single list
 Dim MyRange As Range
 Dim i As Integer
 Dim LastRow As Integer
 On Error Resume Next
 Set MyRange = Selection
 If MyRange Is Nothing Then Exit Sub
 LastRow = MyRange.Columns(1).Rows.Count + 1 'gets the last empty row of the range
 For i = 2 To MyRange.Columns.Count
 Range(MyRange.Cells(1, i), MyRange.Cells(MyRange.Columns(i).Rows.Count, i)).Cut 'cuts each column in the range starting on the 2nd
 ActiveSheet.Paste Destination:=MyRange.Cells(LastRow, 1) 'pastes the data on the last empty row
 LastRow = LastRow + MyRange.Columns(i).Rows.Count 'updates the last row
 Next
End Sub

How to combine columns in a new one


Sub CombineColumnsInNewOne()
'How to combine columns in a new one
LastRow = Range("A65536").End(xlUp).Row 'gets the last row of the first column. Change the column as needed e.g. B65536
For i = 1 To LastRow
 Range("C" & i) = Range("A" & i).Text & " " & Range("B" & i).Text 'merges column A and B on C
Next i
End Sub

How to merge all the open books sheets in a new book

Sub MergeBooksSheetsInNewBook()
'How to merge all the open books sheets in a new book
Dim SourceBook As Workbook
Dim TargetBook As Workbook

Workbooks.Add 'adds the new workbook
Set TargetBook = Workbooks(Workbooks.Count) 'stores the new book in a book object
For Each SourceBook In Workbooks
 SourceBook.Sheets.Copy after:=TargetBook.Sheets(Sheets.Count) 'copies all the sheets of the given book to the end of the single target book
Next
End Sub

How to merge all the folder books in a new book


Sub MergeFolderBooksInNewBook()
'How to merge all the folder books in a new book
Dim SourceBook As Workbook
Dim TargetBook As Workbook
Dim SourceSheet As Worksheet
Dim MyFileSystem, MyFolder, MyFile, FolderContent As Variant
Dim strFolderPath As String

Workbooks.Add 'adds the new workbook
Set TargetBook = Workbooks(Workbooks.Count) 'stores the new book in a book object

 
FolderPath = "C:\test" 'gets the folder path as a string, e.g. "C:\Users\John\Documents\Test"
'define the file variables
Set MyFileSystem = CreateObject("Scripting.FileSystemObject")
Set MyFolder = MyFileSystem.GetFolder(FolderPath)
Set FolderContent = MyFolder.Files
 
 For Each MyFile In FolderContent
 If Right(MyFile.Name, 4) = "xlsx" Then 'change "xlsx" to your desired file extension: xlsm, docx, etc
 Set SourceBook = Workbooks.Open(FileName:=MyFile.Path)
 For Each SourceSheet In SourceBook.Worksheets
 SourceSheet.Copy after:=TargetBook.Sheets(TargetBook.Sheets.Count) 'copies all the sheets of the given book to the end of the single target book
 Next
 SourceBook.Close False
 End If
 
 Next
'frees memory
 Set MyFileSystem = Nothing
 Set MyFolder = Nothing
 Set FolderContent = Nothing
End Sub

 


Submit area

If your basic VBA code snippet is not listed, click here to submit your own ideas…


Download area

Download the workbook BasicVBA_Combining.xlsm

Comments on this entry are closed.