Help in segregating and appending data

[vijoyal]

I have a list of Clients with ID NO'S and Location. I need to segregate those in different worksheets under A, B, C according to the Location. The ID No and Name should appear in the respective worksheets. Whenver the master list is amended or added the change should effect in the respective worksheets. I tried all possible ways through my amateurish knowledge in excel, but couldn't succeed. Would anybody help me? Regards Vijoy

 

[Saagar]

I developed this code in Excel2003. For a sample data like this, you need to create sheets "LocA", "LocB" beforehand.
<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6" cellpadding="2.5px"><colgroup><col style="background-color: #E0E0F0" width="25px"><col><col><col></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th>
</th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">ID No</td><td style="font-weight: bold;;">Name</td><td style="font-weight: bold;;">Location</td></tr><tr><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">101</td><td style=";">test1</td><td style="text-align: center;;">LocA</td></tr><tr><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">102</td><td style=";">test2</td><td style="text-align: center;;">LocB</td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">103</td><td style=";">test3</td><td style="text-align: center;;">LocA</td></tr><tr><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">104</td><td style=";">test4</td><td style="text-align: center;;">LocB</td></tr><tr><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">105</td><td style=";">test5</td><td style="text-align: center;;">LocB</td></tr><tr><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">106</td><td style=";">test6</td><td style="text-align: center;;">LocB</td></tr><tr><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">107</td><td style=";">test7</td><td style="text-align: center;;">LocA</td></tr><tr><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">108</td><td style=";">test8</td><td style="text-align: center;;">LocB</td></tr><tr><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">109</td><td style=";">test9</td><td style="text-align: center;;">LocA</td></tr><tr><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;">110</td><td style=";">test10</td><td style="text-align: center;;">LocB</td></tr><tr><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;">111</td><td style=";">test11</td><td style="text-align: center;;">LocA</td></tr><tr><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;;">112</td><td style=";">test12</td><td style="text-align: center;;">LocB</td></tr></tbody></table>

Source​

You may then right click on the "source" spreadsheet, select "View Code" option and paste this code. Update the line in red with your locations later.

Private Sub Worksheet_Change(ByVal Target As Range)

'List of sheet/location names
locs = Array("LocA", "LocB")
startrow = 2
lastrowA = Range("A65536").End(xlUp).Row
lastrowb = Range("B65536").End(xlUp).Row
lastrowC = Range("C65536").End(xlUp).Row

If lastrowA = lastrowb And lastrowA = lastrowC Then
    For j = 0 To UBound(locs)
        sheetname = locs(j)
        Sheets(sheetname).Range("A:B").Clear
        Sheets(sheetname).Cells(1, 1) = "ID No"
        Sheets(sheetname).Cells(1, 2) = "Name"
    Next
    
    For i = startrow To lastrowA
        logsheet = Cells(i, 3)
        currow = Sheets(logsheet).Range("B65536").End(xlUp).Row + 1
        Sheets(logsheet).Cells(currow, 1) = Cells(i, 1)
        Sheets(logsheet).Cells(currow, 2) = Cells(i, 2)
    Next
End If

End Sub

Whenever a new row of data is added or existing data changed, columns A and B in location spreadsheets ("LocA" and "LocB" in this examlpe) will be cleared and reconstructed.

Integrate this in to a copy of your workbook, test it and let me know if it works.

 

[vijoyal]

Dear Sagar Thank you. I shall test and revert soon. God Bless. Vijoy

 

[vijoyal]

While running I got the following error:
" Run time error '9', subscript out of range"
In the debug below is highlighed as error:
"currow = Sheets(logsheet).Range("B65536").End(xlUp).Row + 1".
I am using Excel 2010. Please let me know where I went wrong. Thanks for your support. Vijoy

 

[ayazgreat]

vijoyal, have you checked sheet Name if it is existing ?

currow = Sheets(logsheet).Range("B65536").End(xlUp).Row + 1

 

[vijoyal]

Hi Ayaz
What I have done is given below: ( Only changed the Array names as suggested by Saagar and added one more row LastrowC
Private Sub Worksheet_Change(ByVal Target As Range)
'List of sheet/location names
locs = Array("ARARATH", "EDEN", "EPHRATH")
startrow = 2
lastrowA = Range("A65536").End(xlUp).Row
lastrowb = Range("B65536").End(xlUp).Row
lastrowC = Range("C65536").End(xlUp).Row
If lastrowA = lastrowb And lastrowA = lastrowC Then
For j = 0 To UBound(locs)
sheetname = locs(j)
Sheets(sheetname).Range("A:B").Clear
Sheets(sheetname).Cells(1, 1) = "ID No"
Sheets(sheetname).Cells(1, 2) = "Name"
Next

For i = startrow To lastrowA
logsheet = Cells(i, 2)
currow = Sheets(logsheet).Range("B65536").End(xlUp).Row + 1
Sheets(logsheet).Cells(currow, 1) = Cells(i, 1)
Sheets(logsheet).Cells(currow, 2) = Cells(i, 2)

Next
End If
End Sub

Please let me know where I went wrong? Tks

 

[Saagar]

In my example I assumed location is in column C, so I used logsheet = Cells(i,3).

You changed it to logsheet = cells(i,2). Do you have location listed in Column B? Are the values "Ararath", "Eden" and "Ephrath" appearing in column B or C?

 

[vijoyal]

Hi Saagar
I am so grateful to you; it worked well now. Thanks a lot once again for the full support. Vijoy

 

[vijoyal]

Hi Saagar
I am so grateful to you; it worked well now. Thanks a lot once again for the full support. Vijoy

 

[Saagar]

You are very welcome.