In this Excel tutorial, We will be sharing a VBA automation script I use (as well as some of my clients) to combine any number of worksheets into a master table.
If you use Microsoft Excel a lot, especially at work, it's a huge pain to combine multiple worksheets into a single master worksheet. Good thing we can automate just about any Excel work with VBA (aka Excel Macro). In this Excel tutorial, We will be sharing a VBA automation script I use (as well as some of my clients) to combine any number of worksheets into a master table.
00:00 - Quick overview and demo 02:00 - Tutorial starts
Sub Combine_Worksheets() Dim LastRow As Long, LastColumn As Long, RowTracker As Long Dim wsMaster As Worksheet, ws As Worksheet Dim flag As Boolean '// Create Master Worksheet Object Set wsMaster = ThisWorkbook.Worksheets("Master") '// Clear Master Worksheet Content (Including the formatting) wsMaster.Cells.Clear RowTracker = 2 flag = False '// Iterate each worksheet in this Excel file, except the Master Workshete For Each ws In ThisWorkbook.Worksheets If UCase(ws.Name) <> "MASTER" Then LastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row LastColumn = ws.Cells(1, Columns.Count).End(xlToLeft).Column If Not flag Then ws.Range("A1").Resize(1, LastColumn).Copy wsMaster.Range("A1") flag = True End If ws.Range(ws.Cells(2, 1), ws.Cells(LastRow, LastColumn)).Copy wsMaster.Cells(RowTracker, 1) RowTracker = RowTracker + LastRow - 1 End If Next ws Call Formatting_Master_Sheet(wsMaster) MsgBox "Process is complete", vbInformation Set wsMaster = Nothing End Sub Private Sub Formatting_Master_Sheet(ByVal worksheet_object As Worksheet) Dim LastRow As Long, RowNumber As Long Dim LastColumn As Long With worksheet_object LastRow = .Cells(Rows.Count, "A").End(xlUp).Row LastColumn = .Cells(1, Columns.Count).End(xlToLeft).Column '// Insert comma seperator .Columns("D:G").NumberFormat = "#,##0" '// Convert URLs to Hyperlinks For RowNumber = 2 To LastRow .Hyperlinks.Add .Cells(RowNumber, "H"), Address:=.Cells(RowNumber, "H").Value, TextToDisplay:=.Cells(RowNumber, "B").Value Next RowNumber '// Applying Background Color To Alternate Rows For RowNumber = 2 To LastRow If RowNumber Mod 2 = 1 Then .Cells(RowNumber, "A").Resize(1, LastColumn).Interior.Color = RGB(230, 255, 227) End If Next RowNumber '// Makes sure all fonts are the same .Cells.Font.Size = 12 .Cells.Font.Name = "Noto Sans" '// Autofit Columns .Cells.EntireColumn.AutoFit '// Format Table Headers .Cells(1, 1).Resize(1, LastColumn).Interior.Color = RGB(233, 233, 233) .Cells(1, 1).Resize(1, LastColumn).Font.Bold = True Application.Goto .Range("A1"), True End With End Sub
In this MS Excel VBA video, we are going to see that how can we delete cells in excel using the VB code in the VBA editor and the Overview of formulas in Excel. We will see Basic Excel formulas & functions with examples .
In thi MS Excel VBA video, we are going to look at how can we use the VB code to clear up cells in MS Excel. The clear cells feature of MS Excel is used for the purpose. Using this feature we can easily clear cell contents, cell formatting, cell comments and hyperlinks as well
In this MS Excel VBA video, we are going to see how can we use the VB code to manipulate the Column Width of Cells in MS Excel. So we are going to use two methods and explore them in detail, along with this we aare also going to see that what are the possible values for the width of columns
In this Excel VBA video, we are going to see the usage of With Block in Excel VBA. Using with block, we can reuse and rewrite multiple code lines. Also we are going to look at the interior property in brief as well, which allows us to set background colors and background gradient as well
In this Excel VBA video, we are going to look at the usage of the Paste Special function in Excel using the VB code. Paste Special function helps us to use the paste function in different formats such as Pasting Cell Width, Pasting Text only, Pasting Cell Formats.