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 ...

excel

Macros and VBA

HansVogelaar

May 13, 2024

nikosrisa

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