Copying columns from one worksheet to another only if the name in Column B is "Internal"

Hi, can someone help add additional coding so that I can copy multiple columns over to a different worksheet if Column B says "Internal"?  There is a separate worksheet with the name Internal that I want the data to be copied over to. The headers are different in both worksheets, but the column header has the same title for the data I want in the destination worksheet.  Another coding I also need is to copy all information in certain columns from the source worksheet over to a management worksheet.  

I grayed out the columns that do not need to be copied over.  

**The coding I provided was written by user Roy Cox.  

Sub ExtractToSheets()
    Dim ws     As Worksheet
    Dim wsNew  As Worksheet
    Dim rData  As Range
    Dim rCl    As Range
    Dim sNm    As String
    Set ws = Sheet1

    'extract a list of unique names
    'first clear existing list
    With ws
        Set rData = .Range("A1").CurrentRegion
        rData.Columns(9).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Cells(1, .Columns.Count), Unique:=True

        For Each rCl In .Cells(1, .Columns.Count).CurrentRegion
            sNm = rCl.Text
            'add new sheet (only if required-NB uses UDF)
            If WksExists(sNm) Then
                'so clear contents
                'new sheet required
                Set wsNew = Sheets.Add
                wsNew.Move After:=Worksheets(Worksheets.Count)    'move to end
                wsNew.Name = sNm
            End If
            'AutoFilter & copy to relevant sheet
            rData.AutoFilter Field:=9, Criteria1:=sNm
            rData.Copy Destination:=Worksheets(sNm).Cells(1, 1)
        Next rCl
    End With
    ws.Columns(Columns.Count).ClearContents        'remove temporary list
    rData.AutoFilter        'switch off AutoFilter
End Sub

Function WksExists(wksName As String) As Boolean
    On Error Resume Next
    WksExists = CBool(Len(Worksheets(wksName).Name) > 0)
End Function
Bobby FAsked:
Who is Participating?

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

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.

Saurabh Singh TeotiaCommented:
You can use this code which will do what you are looking for...

Sub copydata()
    Dim rng As Range, cell As Range
    Dim lrow As Long, lr As Long
    Dim ws As Worksheet, ws1 As Worksheet

    Set ws = Sheets("MASTER FILE")
    Set ws1 = Sheets("INTERNAL_Tracker")

    lrow = ws.Cells(Cells.Rows.Count, "a").End(xlUp).Row

    Set rng = ws.Range("B2:B" & lrow)

    For Each cell In rng

        If Trim(UCase(cell.Value)) = "INTERNAL" Then
            lr = ws1.Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1

            ws.Range("A" & cell.Row & ":D" & cell.Row & ",G" & cell.Row & ":J" & cell.Row & ",N" & cell.Row & ":R" & cell.Row).Copy ws1.Range("A" & lr)

        End If
    Next cell
End Sub

Open in new window


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
Bobby FAuthor Commented:
Thank you so much!!!!  Worked like it was supposed to.
Bobby FAuthor Commented:
Saurabh, I'm having a problem with the coding.  It's copying the same information over every time I run the macro.  Is there anyway to only have it copy over new data as the workbook gets updated so there are no duplicate rows in the destination sheet?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Saurabh Singh TeotiaCommented:
Quick question basis of which columns or combination of which column..You want to call it as duplicate??
Bobby FAuthor Commented:

I'm not sure I follow your question.  I still want all the information in the columns I grayed out in the sample file to be copied over to their destination worksheets.  However, it's copying over the same information when I run the macro.  Is there any way to only copy new data from the source worksheet and leave out anything that was copied over previously?  I don't want it to overwrite the existing data that was copied over before, just copy and paste into a new blank row in the destination worksheet.
Saurabh Singh TeotiaCommented:
That's what i wanted to know how do you identify its a new data vs old data as in which columns you look into to identify the same?
Bobby FAuthor Commented:

There's column to indicate which data is new and which is old... can we update the coding to have it delete out duplicates if each cell in a different row has the exact same data as another row? or what would you recommend?
Saurabh Singh TeotiaCommented:
Sure if you can add that column i can use as reference point to check..Also by using this column do you want to keep the new data or old data?

In additional can you post your sample workbook with this additional column and how it looks so that i can accordingly do the necessary programming..
Bobby FAuthor Commented:
sorry saurabh, i meant there's no column to indicate which data is new or old.  Staff is already used to the standard template heading and to add an additional column as a unique identifier would be extra work.  Can we have the coding find duplicates within a worksheet and remove them as it updates? no row should have the same information if that helps.
Saurabh Singh TeotiaCommented:

Again if you can help me answer this question i can tweak this code for you.. If you just can let me know when you say duplicates..which column you look at to call them duplicates or is it a combination of in like Column-A & B has same value or you look at more columns to determine that answer??
Bobby FAuthor Commented:
Saurabh, it's a combination of columns.  If you filter by any column you notice there are the same information repeated.  Does that help? Sorry for the back and forth, I didn't understand your question at first.
Saurabh Singh TeotiaCommented:

Can you give me your updated data where you have duplicate involved so that i can look into it.. Also when you say combination of columns..It will be extremely helpful if you can point out which columns do i need to check for duplicate as accordingly i can code the same..

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 Excel

From novice to tech pro — start learning today.