Merge Cell Data from multiple tabs to one master tab

[jiangmojia]

Hello,

I try to merge the data from multiple tabs to one master tab. Since the concepts in those tabs constantly updated every day (always adding the new information), I want to make sure my master tab is always up to date, and include all updated concepts. Below is the coding I came up with in VBA. However, I could not run it in Excel since the coding underlined was always showed a debug issue. Could somebody look at my coding and let me know how to fix it?

Much appreciated. Lucy

Sub MergeDataFromWorksheets()
Dim sh As Worksheet
Dim DestShe As Worksheet
Dim erow As Long, lrowsh As Long, Startrow As Long
Dim CopyRng As Range
Startrow = 2
With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Delete the sheet "MyMergeSheet"
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Worksheets("MyMergeSheet").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'Add a worksheet with the name "MyMergeSheet"
Set DestSh = ActiveWorkbook.Worksheets.Add
DestSh.Name = "MyMergeSheet"
'loop through all worksheets and copy the data to the DestSh
For Each sh In ActiveWorkbook.Worksheets
If shtName <> DestSh.Name Then
'Find the next blank or empty row on the DestSh
erow = DestSh.Range("A" & Rows.Count).End(x1UP).Offset(1, 0).Row
'Find the last row with data in the Sheet
lrowsh = sh.Range("A" & Rows.Count).End(xlUp).Row

Set CopyRng = sh.Range(sh.Rows(Startrow), sh.Rows(lrowsh))

'copies values/formats
CopyRng.Copy
With DestSh.Cells(erow, 1)
.PasteSpecial x1PasteValues
.PasteSepcial x1PasteFormates
Application.CutCopyMode = faluse

End With

End If
Next

DestSh.Cells(1, 1) = "Current Period Update"
DestSh.Cells(1, 2) = "Deficiency Reference #"
DestSh.Cells(1, 3) = "Audit Report Number"
DestSh.Cells(1, 4) = "IA Reference #"
DestSh.Cells(1, 5) = "Identifier"
DestSh.Cells(1, 6) = "Control Matrix Ref. #"
DestSh.Cells(1, 7) = "Category"
DestSh.Cells(1, 8) = "Region"
DestSh.Cells(1, 9) = "Location"
DestSh.Cells(1, 10) = "Control Activity"
'AutoFit the column width in the DestSh sheet
DestSh.Columns.AutoFit

With Application
.ScreeUpdating = True
.EnableEvents = True
End With
End Sub

 

[Fazza]

please check if there is End(x-one-up) instead of (x-ell-up)

 

[jiangmojia]

please check if there is End(x-one-up) instead of (x-ell-up)

I updated the code, and it works. Thanks! Lucy

 

[Fazza]

Super