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?
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.

ShumsDistinguished Expert - 2017Commented:
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.
0
ShumsDistinguished Expert - 2017Commented:
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.
0
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
0

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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy 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
0
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.
1
Matt PinkstonEnterprise ArchitectAuthor Commented:
thanks for those answers
0
ShumsDistinguished Expert - 2017Commented:
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
0
ShumsDistinguished Expert - 2017Commented:
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.
0
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
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.