Excel VBA merge problem

[ac_milan]

Hello all,

I'm a novice VBA user and I'm having some issues merging data. I'm trying to merge data from several sheets into one master sheets and I've run into some issues. I've borrowed some code from Ron de Bruin, however the code does not work for shares documents as his code deleted and re-creates the master sheet (unfortunately addition or deletion of sheets is not possible for shared files.)

I've been trying to modify the code for days to get it to work without deleting the tab, yet I've been unsuccessful. I figured the best way would be to delete the contents of the cells and then use the remainder of the code but that hasn't worked.

Any help would be greatly appreciated.

Please find below the code, with the section in question bolded.

Regards,

Stu


Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function


Function LastCol(sh As Worksheet)
On Error Resume Next
LastCol = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function


Sub MainMacro()

CopyDataWithoutHeaders
ApplyAutoFilters
Home

End Sub


'***Merge all sheets without headers***

Sub CopyDataWithoutHeaders()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim shLast As Long
Dim CopyRng As Range
Dim StartRow As Long

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

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

'Add a worksheet with the name "Master"
Set DestSh = ActiveWorkbook.Worksheets.Add
DestSh.Name = "Master"
StartRow = 2 'Fill in the start row



'loop through all worksheets and copy the data to the DestSh
For Each sh In ActiveWorkbook.Worksheets
If sh.Name <> DestSh.Name And sh.Visible = True Then

'Copy header row, change the range if you use more columns
If WorksheetFunction.CountA(DestSh.UsedRange) = 0 Then
sh.Range("A1:Z1").Copy DestSh.Range("A1")
End If

'Find the last row with data on the DestSh and sh
Last = LastRow(DestSh)
shLast = LastRow(sh)

'If sh is not empty and if the last row >= StartRow copy the CopyRng
If shLast > 0 And shLast >= StartRow Then

'Set the range that you want to copy
Set CopyRng = sh.Range(sh.Rows(StartRow), sh.Rows(shLast))

'Test if there enough rows in the DestSh to copy all the data
If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then
MsgBox "There are not enough rows in the Destsh"
GoTo ExitTheSub
End If

'This example copies values/formats, if you only want to copy the
'values or want to copy everything look below example 1 on this page
CopyRng.Copy
With DestSh.Cells(Last + 1, "A")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With

End If

End If
Next

ExitTheSub:

Application.Goto DestSh.Cells(1)

'AutoFit the column width in the DestSh sheet
DestSh.Columns.AutoFit

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


Sub ApplyAutoFilters()
With ActiveWorkbook.Worksheets("Master")
.AutoFilterMode = False
.Range("A1:J1").AutoFilter
End With
End Sub