Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combine non-contiguous data from multiple worksheets
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combine non-contiguous data from multiple worksheets
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 -- Regards Ron de Bruin http://www.rondebruin.nl "Brentus" wrote in message m... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I combine data from multiple worksheets into one master lis | Excel Worksheet Functions | |||
combine columns of data from multiple worksheets into one | Excel Worksheet Functions | |||
Combine Multiple Worksheets | Excel Discussion (Misc queries) | |||
Combine multiple workbooks into 1 workbook w/ multiple worksheets | Excel Discussion (Misc queries) | |||
Combine multiple workbooks into 1 workbook w/ multiple worksheets | Excel Discussion (Misc queries) |