Need to be able to copy rows from a master sheet into another sheet

First I want to say, I know how to use Excel but never had much experience with VBA/macros and the experience I do have, is years old.
I have a list of inventory - the two parts that really matter are the name and the location of the item. The items are alphebetized by name and new items are constantly being added. All of the items are on a master sheet, however we also have separate sheets for individual locations. What I want to do is run a macro where you can press a button and be able to automatically add the items to the location sheet corresponding to their location on the master sheet. Here is a simplified version of my inventory. (The screenshot is from a Mac, but I am working on Office 2016 for Windows.)

Screen Shot 2018-07-17 at 9.40.37 PM.png

Code is attached as a text file, but looks like this.

Sub PopLoc_Click()

Dim i As Integer
i = 2 'Defines starting in Row 2


Dim loc As String 'Location input will be a string
loc = Sheets("Master Sheet").Cells(i, 2) ' Location initially starts in B2
Sheets("Master Sheet").Range("B2").Activate

Do Until Cells(i, 2).Value = ""
If loc = "A" Then
Sheets("Master Sheet").Range("A2:B2").Copy Sheets("A").Range("A2:B2")
ElseIf loc = "B" Then
Sheets("Master Sheet").Range("A2:B2").Copy Sheets("A").Range("A2:B2")
ElseIf loc = "C" Then
Sheets("Master Sheet").Range("A2:B2").Copy Sheets("A").Range("A2:B2")
Else
MsgBox ("Please select a valid location from the drop down menu for the Item in RowCount.")
End If

i = i + 1
Loop

MsgBox ("Finished!!!")
MsgBox (i)

End Sub

As of now, it correctly places only the first row from my master inventory into the correct column as expected, but will not continue onto do the same for the next columns, and beyond that I'm not sure how to write it so that after one item is put on a location sheet, the next item that is added will be put onto the next free line. I suspect I am on the write track with what I already have (I know it's probably not elegant) because when the Message Box displays the number of rows it got down to, it displays 7, meaning it went through all of the rows but just didn't copy/paste any but the first.

I apologize in advance if this is extremely stupid, and if the solution is simple, but I really just re-taught myself everything I know about VBA in 4 hours today and this is as far as I could get. Any help (with some explanation if possible) would be greatly appreciated.

Honestly we should probably just move all our inventory to Access, but that is a change I think would be too big for others that use it to accept...

Easier just using a filter or an advanced filter per location …

Looping is not the fastest way but if the file is not too big, it will at least do the job.
I certainly would look into what the other gentlemen suggest.

Please Login or Register  to view this content.