The source data is collected using drop-down menus. In moving the data from one workbook to another, the drop-down lists are carried over into the destination cells. I have used both: paste special "values" and paste special "valuesandnumber formats". The code in the else statement produces the same result as the 2 step process above it. Thanks in advance
Sub extractData(sourcedata, Dataset)
Dim finalRow As Integer
Dim rowCount As Integer
Dim emptyRow As Integer
Dim pasteRange As Range
emptyRow = (Dataset.Range("A" & Rows.Count).End(xlUp).Row) + 1
rowCount = sourcedata.Range("A" & Rows.Count).End(xlUp).Row
If sourcedata.Cells(rowCount, 2) > 2 Then
'Loop through Source - paste to Master
With sourcedata
If rowCount > 2 Then
'Remove formatting
.Cells.EntireRow.Interior.ColorIndex = 0
.Cells.EntireRow.Borders.LineStyle = xlNone
'Copy and Paste Data from source to dataset
.Range(("A3:AX" & .Range("A" & Rows.Count).End(xlUp).Row)).Copy
Set pasteRange = Dataset.Cells(emptyRow, 1)
pasteRange.PasteSpecial ()
Else
.Cells.EntireRow.Interior.ColorIndex = 0
.Cells.EntireRow.Borders.LineStyle = xlNone
.Range(("A3:AX" & .Range("A" & Rows.Count).End(xlUp).Row)).Copy _
Dataset.Cells(emptyRow, 1)
End If
'Application.CutCopyMode = False
End With
End If
End Sub
Bookmarks