Combine non-contiguous data from multiple worksheets

B

Brentus

I am using Excel 2002 and have a workbook with several sheets that I
need to combine into one list. The data I need to combine is located
on the same place on each sheet; however, one potential wrinkle is
that the data is non-contiguous (i.e. Name is cell B7, Salary is in
cell D10, etc.). This may be able to be done without using VBA;
however, the consolidate feature does not seem to produce what I want.

Any ideas?

Thanks,
Brent
 
R

Ron de Bruin

Hi Brentus

Try this

Sub Test1()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim smallrng As Range

On Error Resume Next
If Len(ThisWorkbook.Worksheets.Item("Master").Name) = 0 Then
On Error GoTo 0
Application.ScreenUpdating = False
Set DestSh = Worksheets.Add
DestSh.Name = "Master"
For Each sh In ThisWorkbook.Worksheets
If sh.Name <> DestSh.Name Then
For Each smallrng In sh. _
Range("B7,D10").Areas
Last = LastRow(DestSh)
smallrng.Copy DestSh.Cells(Last + 1, "A")
Next
End If
Next
Cells(1).Select
Application.ScreenUpdating = True
Else
MsgBox "The sheet Master already exist"
End If
End Sub

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
 
Top