+ Reply to Thread
Results 1 to 12 of 12

How to copy data to new tab depending in word in a second tab

Hybrid View

  1. #1
    Registered User
    Join Date
    06-19-2013
    Location
    Peterborough, UK
    MS-Off Ver
    Excel 2010
    Posts
    37

    How to copy data to new tab depending in word in a second tab

    Please HELP!!!

    The program below is looking at the Field "Organisation" in the "Data" tab and copies any data that matches the exact contents on Column A in the "Buss Type" Tab into a new Tab called "Target-Data"

    Could you show me how to change it so that it copies into the "Target-Data" Tab any data that contains the word listed in Column A in the "Buss Type" Tab any where in the content of the Field "Organisation" in the "Data" Tab?

    Option Explicit
    
    Sub Buss_Select()
        Dim ws As Worksheet, ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet, ws4 As Worksheet, ws5 As Worksheet, ws6 As Worksheet
        Dim Rng As Range, cel As Range
        Dim ToTRow As Long, LR As Long, LR1 As Long, x As Long
        Dim Headers As Variant
        Dim awf As WorksheetFunction: Set awf = WorksheetFunction
        Dim lColumn As Long
        Dim vColumn As Long
        Dim uColumn
        Dim tColumn As String
    
        lColumn = awf.Match("Organisation", Rows(1), 0)
    
        vColumn = Cells(1, Columns.Count).End(xlToLeft).Column
        uColumn = Split(Cells(1, vColumn).Address, "$")
    
        tColumn = uColumn(LBound(uColumn) + 1)
    
        Set ws = Sheets("Buss Type")
        Set ws1 = Sheets("Data")
        With ws1
            Headers = WorksheetFunction.Transpose(.Range("A1", .Range("A1").End(xlToRight)).Value)
        End With
        
        Application.ScreenUpdating = False
        If Not Evaluate("ISREF('Target-Data'!A1)") Then
            Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Target-Data"
            Sheets("Target-Data").Range("A1").Resize(1, UBound(Headers)).Value = WorksheetFunction.Transpose(Headers)
        Else
            Sheets("Target-Data").UsedRange.Offset(1, 0).ClearContents
        End If
        Set ws2 = Sheets("Target-Data")
    
        With ws
            ToTRow = .Columns("A").Find(what:="End", LookIn:=xlValues, lookat:=xlWhole).Row - 1
    
            Set Rng = .Range("A2:A" & ToTRow)
            For Each cel In Rng
                With ws1
                    LR = .Cells.Find("*", .Cells(Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious).Row
                    .Range("A1:" & tColumn & LR).AutoFilter Field:=lColumn, Criteria1:=cel.Value
                    Set Rng = .AutoFilter.Range
                    x = Rng.Columns(lColumn).SpecialCells(xlCellTypeVisible).Count - 1
                    If x >= 1 Then
                    Rng.Offset(1, 0).Copy
                        With ws2
                            LR1 = .Cells.Find("*", .Cells(Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
                                    SearchDirection:=xlPrevious).Row + 1
                            .Range("A" & LR1).PasteSpecial
                        End With
                    End If
                End With
            Next cel
            ws1.AutoFilterMode = False
        End With
        ws2.Columns.AutoFit
        Application.ScreenUpdating = True
    End Sub
    Last edited by Edinson; 09-17-2014 at 09:19 AM.

  2. #2
    Registered User
    Join Date
    06-19-2013
    Location
    Peterborough, UK
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: How to copy data to new tab depending in word in a second tab

    Can Any guru help please??

  3. #3
    Registered User
    Join Date
    06-19-2013
    Location
    Peterborough, UK
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: How to copy data to new tab depending in word in a second tab

    Can Someone help me with this please?

  4. #4
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: How to copy data to new tab depending in word in a second tab

    Please attach a sample with a desired result.

  5. #5
    Registered User
    Join Date
    06-19-2013
    Location
    Peterborough, UK
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: How to copy data to new tab depending in word in a second tab

    Thanks AB33.

    Please find attached a data file as it should end up.
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: How to copy data to new tab depending in word in a second tab

    Edinson,
    This is no exact match, hence the code you have is not returning anything.
    There is no exact word "PLUMBING" in sheet Data column B. You have a sentence.
    I can do a partial match, but you might get some unwanted lines.

  7. #7
    Registered User
    Join Date
    06-19-2013
    Location
    Peterborough, UK
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: How to copy data to new tab depending in word in a second tab

    That will be fine. That is what I want. As long as the Word "Plumbing" or any other word in the Buss Type Data Sheet appears in Column B of the Data Sheet.

  8. #8
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: How to copy data to new tab depending in word in a second tab

    Try the attached.

  9. #9
    Registered User
    Join Date
    06-19-2013
    Location
    Peterborough, UK
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: How to copy data to new tab depending in word in a second tab

    Hi AB33,

    This has worked but only it only copies one line. It copies the line which has "CLEANING". Also it does not copy the Header line.

  10. #10
    Registered User
    Join Date
    06-19-2013
    Location
    Peterborough, UK
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: How to copy data to new tab depending in word in a second tab

    Hi AB33,

    This has worked but only it only copies one line. It copies the line which has "CLEANING". Also it does not copy the Header line.

  11. #11
    Registered User
    Join Date
    06-19-2013
    Location
    Peterborough, UK
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: How to copy data to new tab depending in word in a second tab

    Hi AB33,

    I tried this again and it is now working correctly. I don't know what I did before, but it works!!! Thank you very much for your help on this

  12. #12
    Registered User
    Join Date
    06-19-2013
    Location
    Peterborough, UK
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: How to copy data to new tab depending in word in a second tab

    Last edited by Edinson; 09-17-2014 at 07:31 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Macro to copy, paste then delete depending on a certain word in the row
    By melliard5779259 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-02-2014, 05:36 AM
  2. Copy two cells depending on data being input in another
    By papasmurfuo9 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-31-2013, 09:48 AM
  3. Macro to copy data from one sheet to another depending on name in first cell
    By subtilty in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-14-2013, 07:44 AM
  4. [SOLVED] Copy row data from one sheet to another depending on a category
    By Burli in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 01-24-2013, 10:13 AM
  5. copy rows of data depending on value in 1 column
    By wardga in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-28-2010, 10:30 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1