I am using below macro to combine all worksheets into a new worksheet called 'Combined' in same workbook. All data is copied to new worksheet but I am unable to get same formatting (row height and column width) and Filter in the 'Combined'.
Sub Combine()
Dim I As Long
Dim xRg As Range
On Error Resume Next
Worksheets.Add Sheets(1)
ActiveSheet.Name = "Combined"
For I = 2 To Sheets.Count
Set xRg = Sheets(1).UsedRange
If I > 2 Then
Set xRg = Sheets(1).Cells(xRg.Rows.Count + 1, 1)
End If
Sheets(I).Activate
ActiveSheet.UsedRange.Copy xRg
Next
End Sub
All column headings have same name in all worksheets.