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.

[TABLE="width: 549"]
<tbody>[TR]
[TD]Computername[/TD]
[TD]Username[/TD]
[TD]Computer[/TD]
[TD]Department[/TD]
[TD]Serialnumber[/TD]
[TD]Installed[/TD]
[/TR]
[TR]
[TD]PC01[/TD]
[TD]Admin[/TD]
[TD]Latitude E7450[/TD]
[TD]Miami[/TD]
[TD="align: right"]1111111[/TD]
[TD="align: right"]2017-09-25[/TD]
[/TR]
[TR]
[TD]PC02[/TD]
[TD]Sell[/TD]
[TD]Surface 3[/TD]
[TD]New York[/TD]
[TD="align: right"]2222222[/TD]
[TD="align: right"]2017-05-01[/TD]
[/TR]
[TR]
[TD]PC03[/TD]
[TD]Econ[/TD]
[TD]Latitude E7450[/TD]
[TD]Seattle[/TD]
[TD="align: right"]3333333[/TD]
[TD="align: right"]2013-09-11[/TD]
[/TR]
[TR]
[TD]PC04[/TD]
[TD]Seller2[/TD]
[TD]Surface 3[/TD]
[TD]New York[/TD]
[TD="align: right"]4444444[/TD]
[TD="align: right"]2017-05-01[/TD]
[/TR]
</tbody>[/TABLE]

 

[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