Copy Data to new Worksheet Based on Column Value
Copy Data to new Worksheet Based on Column Value
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Copy Data to new Worksheet Based on Column Value

  1. #1
    New Member
    Join Date
    Jan 2018
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Copy Data to new Worksheet Based on Column Value

     
    I have a worksheet called Master Table with columns A-CR. The Column I am searching is in column B. In the same workbook I have a spreadsheet called SearchMasterTable I am using cell C1 in this workbook for the input of the requested lookup.

    If the data in SearchMasterTable cell C1 equals one or multiple records in workbook Master Table column B, I am trying to copy these records to the workbook SearchMasterTable starting with row 6.

    The code runs but is not copying the data to the new worksheet.

    Code:
    Sub finddata()
    
    
    Dim ApplicationNumber As String
    Dim finalrow As Integer
    Dim i As Integer
    Dim LastRow As Long
    Sheets("SearchMasterTable").Range("A6:CR506").ClearContents
    
    
    ApplicationNumber = Sheets("SearchMasterTable").Range("C1").Value
    
    
    finalrow = Sheets("Master Table").Range("A10000").End(xlUp).Row
    
    
    For i = 2 To finalrow
    If Cells(i, 2) = ApplicationNumber Then
        'Sheets("Master Table").Range(Cells(i, 1), Cells(i, 96)).Copy
        'Sheets("SearchMasterTable").Range("A6").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats
        Application.ScreenUpdating = False
        LastRow = Sheets("Master Table").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Sheets("Master Table").Range("B8:B" & LastRow).AutoFilter Field:=2, Criteria1:="=ApplicationNumber"
        On Error Resume Next
        Sheets("Master Table").Range("B2:B" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("SearchMasterTable").Cells(Sheets("SearchMasterTable").Rows.Count, "A").End(xlUp).Offset(2, 0)
        On Error GoTo 0
        If Sheets("Master Table").AutoFilterMode = True Then Sheets("Master Table").AutoFilterMode = False
        Application.ScreenUpdating = True
    
    
        End If
    
    
    Next i
    Range("C1").Select
    
    
    
    
    End Sub

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    6,215
    Post Thanks / Like
    Mentioned
    127 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Copy Data to new Worksheet Based on Column Value

    Firstly comment out the lines in red
    Code:
    Sub finddata()
    
    
    Dim ApplicationNumber As String
    Dim finalrow As Integer
    Dim i As Integer
    Dim LastRow As Long
    Sheets("SearchMasterTable").Range("A6:CR506").ClearContents
    
    
    ApplicationNumber = Sheets("SearchMasterTable").Range("C1").Value
    
    
    finalrow = Sheets("Master Table").Range("A10000").End(xlUp).Row
    
    
    For i = 2 To finalrow
    If Cells(i, 2) = ApplicationNumber Then
        'Sheets("Master Table").Range(Cells(i, 1), Cells(i, 96)).Copy
        'Sheets("SearchMasterTable").Range("A6").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats
        Application.ScreenUpdating = False
        LastRow = Sheets("Master Table").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Sheets("Master Table").Range("B8:B" & LastRow).AutoFilter Field:=2, Criteria1:="=ApplicationNumber"
        On Error Resume Next
        Sheets("Master Table").Range("B2:B" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("SearchMasterTable").Cells(Sheets("SearchMasterTable").Rows.Count, "A").End(xlUp).Offset(2, 0)
        On Error GoTo 0
        If Sheets("Master Table").AutoFilterMode = True Then Sheets("Master Table").AutoFilterMode = False
        Application.ScreenUpdating = True
    
    
        End If
    
    
    Next i
    Range("C1").Select
    
    
    
    
    End Sub
    You don't need them.
    Then step through the code using F8 & when it applies the filter, are there any visible rows?
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

    Running Office 2003 & 2013 on Win 7

  3. #3
    New Member
    Join Date
    Jan 2018
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy Data to new Worksheet Based on Column Value

    Hey Fluff always great to hear from you. As always thanks for helping. As I step through I get to Sheets("Master Table").Range("B8:B" & LastRow).AutoFilter Field:=2, Criteria1:="=ApplicationNumber" and the next F8 gives me an AutoFilter method of Range class failed.

    Still nothing moving to the new sheet yet.

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    6,215
    Post Thanks / Like
    Mentioned
    127 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Copy Data to new Worksheet Based on Column Value

    Ok, try changing this
    Code:
    Sheets("Master Table").Range("B8:B" & LastRow).AutoFilter Field:=1, Criteria1:=ApplicationNumber
    Your only selecting one column, so it doesn't like field=2
    Last edited by Fluff; Feb 13th, 2018 at 11:58 AM.
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

    Running Office 2003 & 2013 on Win 7

  5. #5
    New Member
    Join Date
    Jan 2018
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy Data to new Worksheet Based on Column Value

    Okay this at least moves data to the correct worksheet. But not based on the value in SearchMasterTable cell C1. It is only moving the data from Master Table row B8. I have re-entered the code to catch up on the changes
    Code:
    Sub finddata()
    Dim ApplicationNumber As String
    Dim finalrow As Integer
    Dim i As Integer
    Dim LastRow As Long
    Sheets("SearchMasterTable").Range("A6:CR506").ClearContents
    ApplicationNumber = Sheets("SearchMasterTable").Range("C1").Value
    finalrow = Sheets("Master Table").Range("A10000").End(xlUp).Row
    
            Application.ScreenUpdating = False
        LastRow = Sheets("Master Table").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Sheets("Master Table").Range("B8:B" & LastRow).AutoFilter Field:=1, Criteria1:="=ApplicationNumber"
        On Error Resume Next
        Sheets("Master Table").Range("B8:B" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("SearchMasterTable").Cells(Sheets("SearchMasterTable").Rows.Count, "A").End(xlUp).Offset(2, 0)
        On Error GoTo 0
        If Sheets("Master Table").AutoFilterMode = True Then Sheets("Master Table").AutoFilterMode = False
        Application.ScreenUpdating = True
    
    Range("C1").Select
    End Sub
    Last edited by Fluff; Feb 13th, 2018 at 01:10 PM. Reason: Code tags

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    6,215
    Post Thanks / Like
    Mentioned
    127 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Copy Data to new Worksheet Based on Column Value

    You haven't changed the criteria
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

    Running Office 2003 & 2013 on Win 7

  7. #7
    New Member
    Join Date
    Jan 2018
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy Data to new Worksheet Based on Column Value

    Dang quotes kill me every time. Works perfect now. Thanks again.

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    6,215
    Post Thanks / Like
    Mentioned
    127 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Copy Data to new Worksheet Based on Column Value

    Glad to help & thanks for the feedback
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

    Running Office 2003 & 2013 on Win 7

  9. #9
    New Member
    Join Date
    Jan 2018
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy Data to new Worksheet Based on Column Value

    Dang quotes get me every time, you would think I would learn! . I was getting a blank row at the top so I changed the offset from 2 to 1 and that seemed to fix that issue. For some reason it is always copying the top row (8) from the Master Table in Row 6 on my target sheet (searchmastertable). Rows 7 on have the correct data in them.
    Last edited by slpswhite; Feb 13th, 2018 at 02:19 PM.

  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    6,215
    Post Thanks / Like
    Mentioned
    127 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Copy Data to new Worksheet Based on Column Value

      
    If you don't want row 8 copied over, make this change
    Code:
    Sheets("Master Table").Range("B9:B" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("SearchMasterTable").Cells(Sheets("SearchMasterTable").Rows.Count, "A").End(xlUp).Offset(2, 0)
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

    Running Office 2003 & 2013 on Win 7

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com