Excel Copy Rows from one tab to another

I have an excel workbook with many rows in a tab called data...  I would like to have another tab called terminated that would only show rows from the data tab where column K = terminated

how is this done with a formula or lookup etc...
Matt PinkstonEnterprise ArchitectAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ShumsExcel & VBA ExpertCommented:
Hi Matt,

Copy or Move your Data Tab and rename it as "Terminated", then filter Col K with Terminated.

Else upload a sample workbook, we can provide you VBA solution.
ShumsExcel & VBA ExpertCommented:
Hi Matt,

Try below VBA:
Sub CopyTerminatedRows()
Dim SrcWs As Worksheet, NewSh As Worksheet, xWs As Worksheet
Dim SrcLR As Long
Dim Crit As String
Dim RngFilter As Range

'Disable Events
With Application
    .ScreenUpdating = False
    .DisplayStatusBar = True
    .StatusBar = "!!! Please Be Patient...Updating Records !!!"
    .EnableEvents = False
    .Calculation = xlManual
End With

'Set Variables
Set SrcWs = Worksheets("Data")
SrcLR = SrcWs.Range("A" & Rows.Count).End(xlUp).Row
Crit = "Terminated"

'Delete Terminated Sheet if exists
Application.DisplayAlerts = False
For Each xWs In Application.ActiveWorkbook.Worksheets
    If xWs.Name = "Terminated" Then
        xWs.Delete
    End If
Next
Application.DisplayAlerts = True

'Filter Range & Copy
Set RngFilter = SrcWs.Range("A1:K" & SrcLR)
With RngFilter
    .AutoFilter Field:=11, Criteria1:=Crit, Operator:=xlFilterValues
    .SpecialCells(xlCellTypeVisible).Copy
    ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)).Paste
End With

ActiveSheet.Name = "Terminated"
Set NewSh = Worksheets("Terminated")
Application.CutCopyMode = False
NewSh.Activate
NewSh.Columns.AutoFit
NewSh.Range("A2").Select
ActiveWindow.FreezePanes = True
With Application
    .ScreenUpdating = True
    .DisplayStatusBar = True
    .StatusBar = False
    .EnableEvents = True
    .Calculation = xlAutomatic
End With
End Sub

Open in new window

Please change the Range for RngFilter accordingly.
Martin LissOlder than dirtCommented:
Try this workbook. The terminated sheet will be updated every time you open that sheet. In the code in Module1 you'll find the following two lines which represent the number of heading rows in the data and terminated sheets. Change them as needed.

Const DATA_HR = 1
Const TERM_HR = 1
29076770.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Matt PinkstonEnterprise ArchitectAuthor Commented:
okay then perhaps easier...

If I create a tab called totals and have a row labeled total active users how do I get the count of all records in tab data where column K=Active and column H=yes
Martin LissOlder than dirtCommented:
Has one or more of us answered your first question? If so then please close this question by selecting one or more of the suggestions and ask a new question.
Matt PinkstonEnterprise ArchitectAuthor Commented:
thanks for those answers
ShumsExcel & VBA ExpertCommented:
Hi Matt,

Seems like you haven't closed this question and requested 2 new questions.
If you are not aware of closing the question, please follow below links:
How do I accept a comment as my solution?
How do I close my question?
Thanks
ShumsExcel & VBA ExpertCommented:
No comment has been added to this question in more than 14 days and OP agreed one of our solution helped him, so it is now classified as abandoned.

If you feel this question should be closed differently, post an objection and a moderator will read all objections and then close it as they feel fit. If no one objects, this question will be closed automatically the way described above.
Martin LissOlder than dirtCommented:
I’m glad I was able to help.

If you expand the “Full Biography” section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2017
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.