Compiling multiple sheets into one.

[Allroy]

I am looking for a solution for the following scenario. I have a workbook with multiple worksheets. The column headers for the sheets in question ("distributor" & "AR") are the same. The vertical range for each sheet varies depending on the amount of data entered by users, but will not exceed line 152 (possible range is A3:N152). I would like to combine only the lines of data from each of the two sheets that were populated by the user into a "master" sheet. There are other worksheets in the book, and I do not want to include data from these worksheets in the "master" sheet. Any ideas would be greatly appreciated!

[mdmackillop]

Is there data in all columns of each populated row, or is it "ragged"

[Allroy]

Column C is an optional field, so it is possible that it could be null. If need be, I can get around this with a default value in that column such as "n/a" or "none".

[mdmackillop]

Something like

Sub GetData()
Dim Shts, sh, LastRow As Long
Shts = Array("Sheet2", "Sheet3")
For Each sh In Shts
With Sheets(sh)
Range(.Cells(3, 1), .Cells(Rows.Count, 1).End(xlUp)).Resize(, 14).Copy _
Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1)
End With
Next
End Sub

[tstav]

Worksheet1 is Sht1, Worksheet2 is Sht2, MasterSheet is MasterSht.
Since your data starts from Row 3 (A3:N152 you say...), try the following:
Sub CombineSheetData()

End Sub

[tstav]

Hi AllRoy
Suppose that the Sheets you are copying data from are Sht1, Sht2
Suppose that the Sheet you are copying data to is MasterSht

Since your data starts on Row 3 and ends on Column 14 (you say A3:N152),

Try the following code

Sub CombineSheetData()
Dim rngR1, rngR2 As Range
Dim Sht1, Sht2, MasterSht As Worksheet
Dim intStartRow, intEndColumn As Integer
intStartRow = 3
intEndColumn = 14
Set Sht1 = Worksheets(1)
Set Sht2 = Worksheets(2)
Set MasterSht = Worksheets(3)
'Set the DataRange of Sht1
With Sht1
Set rngR1 = .Range _
(.Cells(intStartRow, 1), _
.Cells(.Cells(.Rows.Count, 1).End(xlUp).Row, intEndColumn))
End With
'Set the DataRange of Sht2
With Sht2
Set rngR2 = .Range _
(.Cells(intStartRow, 1), _
.Cells(.Cells(.Rows.Count, 1).End(xlUp).Row, intEndColumn))
End With
'Clear the MasterSheet
MasterSht.UsedRange.EntireRow.Delete
'Copy the first Range to the first cell of the MasterSheet
rngR1.Copy MasterSht.Cells(1, 1)
'Copy the second Range to the first empty Row of the MasterSheet
With MasterSht
rngR2.Copy .Cells(.Cells(.Rows.Count, 1).End(xlUp).Row + 1, 1)
End With
End Sub