Copy row when a certain value is on Sheet1 to Sheet2

[Key M]

Hi
I am new to VBA. and need a lots of help to make it work.

The first Sheet (Everything) got everything, and when i put a value under department like Miami then i want to copy the entire row to my Sheet called Miami.
And so on. I have googled it and watched some youtube, but i cant figure out what to do.

Computername	Username	Computer	Department	Serialnumber	Installed
PC01	Admin	Latitude E7450	Miami	1111111	2017-09-25
PC02	Sell	Surface 3	New York	2222222	2017-05-01
PC03	Econ	Latitude E7450	Seattle	3333333	2013-09-11
PC04	Seller2	Surface 3	New York	4444444	2017-05-01

<tbody>
</tbody>

 

[UncleCatwoman]

It looks like you're tracking inventory. Is there a reason you can't just leave it all one one tab and filter for a single department/location when you need to?

 

[Key M]

It looks like you're tracking inventory. Is there a reason you can't just leave it all one one tab and filter for a single department/location when you need to?

Thanks for anwer.
Its to keep it cleaner and easier for end-users, and they also wanted to have this function for other projects.
Im just an IT-Admin so i promised to much and told them that i will probably solve it

 

[UncleCatwoman]

You still don't want to have to worry about maintaining data in multiple worksheets.

What I would do (and have done) is write a macro that filters your main sheet for each department/location and then saves them as individual files. That way, after making updates, you just run the macro. And everyone knows where to go to get the most current copy of each.

 

[Key M]

I found something pretty close to what im searching for.

How can i Make this Macro to "Auto-Run" as soon as a new row is typed?

Option Explicit


Sub Test()


Dim Cell As Range


With Sheets(1)
' loop column D untill last cell with value (not entire column)
For Each Cell In .Range("D2:D" & .Cells(.Rows.Count, "D").End(xlUp).Row)
If Cell.Value = "Miami" Then
' Copy>>Paste in 1-line (no need to use Select)
.Rows(Cell.Row).Copy Destination:=Sheets(2).Rows(Cell.Row)
End If
Next Cell
End With


End Sub