Monday, February 18, 2019

Neat Trick - Macro to Combine Multiple Sheets to One - Excel

I'll address context in a moment.

Writing is such a difficult task and when I read clumsy or poorly worded words that are trying to convey instructions to the user, it raises an innate tendency to pontificate about what I perceive as clumsy or poorly worded words. Thus, I present step 5, which by itself means nothing:

5. Press F5 key to run the code, then a sheet named Combined will be created at the front of all sheets which has merge all contents across all worksheets.

I understood what the writer was striving for after the phrase front of all sheets, but not because it was written well. This is a suggested rewrite of step 5.

5. Press F5 key to run the code. A sheet named Combined is added to the front of all sheets with all content from the other worksheets in the Excel file.

Now, some context. This was in my ExtendOffice email this morning and it is definitely a neat trick that I want to be able to refer to in the future:


This is the VB code (so I don't have to re-type it when I go looking for this post in the future.

Sub Combine()
'UpdatebyExtendoffice
Dim J As Integer
On Error Resume Next
Sheets(1).Select
Worksheets.Add
Sheets(1).Name = "Combined"
Sheets(2).Activate
Range("A1").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1")
For J = 2 To Sheets.Count
Sheets(J).Activate
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Next
End Sub

No comments: