Issues with Macro for merging data from several sheets onto one

[lovleigh]

Let me preface this by saying, I do not write macros nor do I completely understand how they work. I am guilty of "googling" until I find something that works, then copy/paste them into my workbook and use them that way.

My workbook consists of several sheets of data - same number of columns, same headers, same formatting. In addition I have one additional sheet with my drop down list data on it (noted below as one of my issues when I run the macro).

I recently found the following code that works beautifully for the most part to copy the data from each sheet onto another sheet named DataMergeSheet, however, I have two issues I'd like to solve for if possible.

1. When the merged data appears on the new sheet named "DataMergeSheet", the first row of data from the very first active sheet always populates twice. None of the other sheets have that issue.

2. I have one sheet named "dropdowns" that contains all of my drop down data for the lists I have in some of the cells. I hide this sheet and would like for the data on that sheet NOT to be included in the merge for obvious reasons.

Macro:


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 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 "DataMergeSheet" if it exist
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Worksheets("DataMergeSheet").Delete
On Error GoTo 0
Application.DisplayAlerts = True
'Add a worksheet with the name "DataMergeSheet"
Set DestSh = ActiveWorkbook.Worksheets.Add
DestSh.Name = "DataMergeSheet"
'Fill in the start row
StartRow = 2
'loop through all worksheets and copy the data to the DestSh
For Each sh In ActiveWorkbook.Worksheets
'If sh.Name <> DestSh.Name Then
If IsError(Application.Match(sh.Name, _
Array(DestSh.Name, "SES data", "Equipment ID - Rates", "LOG_HISTORY"), 0)) Then

'Copy header row, change the range if you use more columns
If WorksheetFunction.CountA(DestSh.UsedRange) = 0 Then
sh.Range("A1:ac2").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

Thank you in advance for any help you can provide to help solve my concerns....especially the first one.


Leigh

 

[bsquad]

If I understand this right, you are trying to take what is in the worksheets 'SES data', 'Equipment ID - Rates', 'LOG_HISTORY' and dump/crap/aggregate them in the 'DataMergeSheet'?