Excel list base

Hello everyone, I need some help preparing some spreadsheets. So I have two workbooks, one has

Room #	Item Name	Property
aaa	a	aa
aaa	b	bb
aaa	c	cc
bbb	d	dd
ccc	e	ee
ccc	f	ff
ccc	g	gg
ddd	h	hh

And my other workbook is split into sheets so there is the master sheet and sheet aaa for room aaa, sheet bbb for room bbb and so on. The sheets are all prepared and there is also a cell with the room name on each sheet so the room can be referenced from there.

Room:	aaa

Item	Property
a	aa
b	bb
c	cc

How can I make this list in my second workbook that lists all the items that are in the same room? Any ideas?

You might use the FILTER function

If you prefer VBA:

Sub SplitData()
    Dim wsS As Worksheet
    Dim wbT As Workbook
    Dim wsT As Worksheet
    Dim fil As Variant
    Dim s As Long
    Dim m As Long
    Dim t As Long
    Dim room As String
    Dim item As String
    Dim prop As String
    fil = Application.GetOpenFilename(FileFilter:="Excel workbooks (*.xls*),*.xls*", _
            Title:="Please open the target workbook")
    If fil = False Then Exit Sub
    Application.ScreenUpdating = False
    Set wsS = ActiveSheet
    Set wbT = Workbooks.Open(fil)
    m = wsS.Range("A" & wsS.Rows.Count).End(xlUp).Row
    For s = 2 To m
        room = wsS.Range("A" & s).Value
        item = wsS.Range("B" & s).Value
        prop = wsS.Range("C" & s).Value
        Set wsT = wbT.Worksheets(room)
        t = wsT.Range("A" & wsT.Rows.Count).End(xlUp).Row + 1
        wsT.Range("A" & t).Value = item
        wsT.Range("B" & t).Value = prop
    Next s
    Application.ScreenUpdating = True
End Sub

4 Replies