Filtering rows from 1 sheet to multiple other sheets based on a reference column (vbs?)

[thepolish]

I have a spreadsheet that I'm trying to work out something I've never done before. I've researched and found that formulas won't cut it, I'll need to use VB (which I've never done) to make this work. In examples I've found the reference columns to move rows seems to be the first or last, and mine MUST be in the middle.

We have a source of data that comes over and is copy/pasted into excel and the order never changes, data always falls into the same cells as follows (rows are longer but this gives example of basic data):


[TABLE="width: 500"]
<tbody>[TR]
[TD]Load ID[/TD]
[TD]Site ID[/TD]
[TD]Site Name[/TD]
[TD]Material SubGrp[/TD]
[/TR]
[TR]
[TD][TABLE="width: 115"]
<tbody>[TR]
[TD]1508130191101[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]19[/TD]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD]ELKTON[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]ULSD[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 115"]
<tbody>[TR]
[TD]1508130451101[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]45[/TD]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD]MADISON[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]ULSD[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 115"]
<tbody>[TR]
[TD][TABLE="width: 115"]
<tbody>[TR]
[TD]1508170521101[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]52[/TD]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD]OAK GROVE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]UNL[/TD]
[/TR]
[TR]
[TD]1508170521102[/TD]
[TD]19[/TD]
[TD]ELKTON[/TD]
[TD]B5[/TD]
[/TR]
</tbody>[/TABLE]



I need to figure out how when Site ID (B2) matches a range of numbers (for example 19, 52) it will move to a sheet named for the location of the site.

For the example - Any rows with 19 or 52 in "Site ID" need to move to the sheet named "PCT" and any with 45 in B2 need that row moved to sheet "KW".


There are going to be numbers 1-300 that will be grouped together in sets based on their locations (19 and 52 are in the same area and go to the same sheet, there will be some where it's 48,333,22,18 all go to another). The data will fall in a different order every time, but as long as B2 is referenced, the numbers will always correspond with those sheets.

All in all, I need to learn how to set this up as the source data changes daily, and when I paste it in to sheet 1 "Open Loads" I will have to add the filtered to sheets (PCT, KW and will have to add KT, KG, KM later).


I have linked the sheet that shows what I've started (scrubbed of extra stuff off the rows after) with and what needs filtered and how it needs moved over.



https://drive.google.com/file/d/0BzECPoCT-clXbURPYmR5NjZGalU/view?usp=sharing (file must be downloaded, in chrome it opens up like a pdf apparently?)

 

[DebugGalpin]

Couldn't open the file but...

Start with adding a Mapping Table with the Site ID and Sheet you want it to go to.... You can then reference this table to allow you to add new sites etc without the need to change the code. I'd also just loop this as well. Also name your worksheets in VB editor Source and Mapping... Something like the below should then work (will need edited as I can't see the spreadsheet)

s=1
Do until Source.Cells(s,1) = vbnullstring
Dim MapCheck as variant
Err.Clear
On error resume next
MapCheck = Application.Worksheetfunction.Match(Source.Cells(s,2), Mapping.Columns(1),0)
If Err.Number = 0 then
ws = Mapping.Cells(MapCheck,2)
irow = Sheets(ws).Cells(Rows.Count,1).End(xlup).Row+1
t=1
Do until t =5
Sheets(ws).Cells(irow,t) = Source.Cells(s,t)
t=t+1
Loop
Else
MsgBox "Please map Site ID "& Source.Cells(s,2), vbokonly
Exit sub
End if



s=s+1
Loop

 

[thepolish]

I resaved it and uploaded it to drop box. I'll give this a shot! Thank you! First foray into VB and excel!

https://www.dropbox.com/s/czkmll5bnk51ss9/Open Loads.xlsx?dl=0

Thank you!

 

[thepolish]

Couldn't open the file but...

Start with adding a Mapping Table with the Site ID and Sheet you want it to go to.... You can then reference this table to allow you to add new sites etc without the need to change the code. I'd also just loop this as well. Also name your worksheets in VB editor Source and Mapping... Something like the below should then work (will need edited as I can't see the spreadsheet)

s=1
Do until Source.Cells(s,1) = vbnullstring
Dim MapCheck as variant
Err.Clear
On error resume next
MapCheck = Application.Worksheetfunction.Match(Source.Cells(s,2), Mapping.Columns(1),0)
If Err.Number = 0 then
ws = Mapping.Cells(MapCheck,2)
irow = Sheets(ws).Cells(Rows.Count,1).End(xlup).Row+1
t=1
Do until t =5
Sheets(ws).Cells(irow,t) = Source.Cells(s,t)
t=t+1
Loop
Else
MsgBox "Please map Site ID "& Source.Cells(s,2), vbokonly
Exit sub
End if



s=s+1
Loop

Okay, I'm attempting to map this out. I have some confusion as to what the s=1 refers to as well as the (s, 1) and t. Are these references to cells?

I felt like s=1 would be in relation to the spreadsheet which is my master list of sites but I think I'm misguided.

 

[DebugGalpin]

No worries back online now. Yes the s and t reference rows and columns respectively in the loops.

s refers to the starting row in your source worksheet. You are looping down every row here until Cell(s,1) is empty.

t refers to the column and starts at column 1 and stops after copying column 4. Having declared your destination worksheet as ws and irow as the next row in the relevant destination worksheet it is saying your 4 cells A through to D = the row you're in on the source sheet columns A through D. ("Sheets(ws).Cells(irow,t) = Source.Cells(s,t)")

 

[thepolish]

No worries back online now. Yes the s and t reference rows and columns respectively in the loops.

s refers to the starting row in your source worksheet.....

I am still a bit lost, I worked out a lot more of the cells - but I'm still having issues with the macros. Essentially I need =INDEX(Mapping!B:B,MATCH(B:B,Mapping!A:A)) to transfer the whole row to each sheet rather than where the cell is on the open loads page.


On the "open loads" page I kind of have a better example of what needs done.

Thank you again!


Drop Box Link