Results 1 to 22 of 22

List Extracted Rows Of Specified Data From Another Workbook

Threaded View

swordswinger710 List Extracted Rows Of... 10-04-2013, 12:58 PM
jaslake Re: List Extracted Rows Of... 10-04-2013, 02:14 PM
mehmetcik Re: List Extracted Rows Of... 10-04-2013, 02:43 PM
swordswinger710 Re: List Extracted Rows Of... 10-10-2013, 12:36 PM
jaslake Re: List Extracted Rows Of... 10-10-2013, 04:19 PM
swordswinger710 Re: List Extracted Rows Of... 10-25-2013, 03:32 PM
jaslake Re: List Extracted Rows Of... 10-25-2013, 04:46 PM
swordswinger710 Re: List Extracted Rows Of... 10-30-2013, 01:04 PM
jaslake Re: List Extracted Rows Of... 10-30-2013, 02:58 PM
swordswinger710 Re: List Extracted Rows Of... 11-08-2013, 09:26 AM
jaslake Re: List Extracted Rows Of... 11-08-2013, 10:53 AM
swordswinger710 Re: List Extracted Rows Of... 11-08-2013, 02:02 PM
jaslake Re: List Extracted Rows Of... 11-09-2013, 11:09 PM
jaslake Re: List Extracted Rows Of... 11-10-2013, 11:12 AM
swordswinger710 Re: List Extracted Rows Of... 12-10-2013, 12:09 PM
jaslake Re: List Extracted Rows Of... 12-10-2013, 12:39 PM
swordswinger710 Re: List Extracted Rows Of... 12-10-2013, 12:51 PM
jaslake Re: List Extracted Rows Of... 12-10-2013, 12:54 PM
jaslake Re: List Extracted Rows Of... 12-10-2013, 12:57 PM
jaslake Re: List Extracted Rows Of... 12-10-2013, 01:06 PM
swordswinger710 Re: List Extracted Rows Of... 04-04-2014, 02:58 PM
jaslake Re: List Extracted Rows Of... 04-04-2014, 04:17 PM
  1. #14
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: List Extracted Rows Of Specified Data From Another Workbook

    Hi swordswinger710

    This Code in the attached appears to do as you require for the Custom Sort Routine...let me know of issues...
    Option Explicit
    
    Sub Copy_Me()
        Dim rng As Range
        Dim ws As Worksheet, ws1 As Worksheet
    
        Set ws = Sheets("Current Thread Gauge List")
        Application.ScreenUpdating = False
        With ws
            .Cells.EntireColumn.Hidden = False
            .UsedRange.Offset(2, 0).Clear
        End With
        Set ws1 = Sheets("Current List")
        Application.ScreenUpdating = False
        With ws1
            Set rng = .Range("A3:T" & .Range("A3").End(xlDown).Row)
    
            rng.AutoFilter Field:=3, Criteria1:="=Thread Gauge", _
                    Operator:=xlAnd
            rng.Copy
        End With
    
        With ws
            .Range("A3").PasteSpecial
            .Columns.AutoFit
            .Columns("C:J").EntireColumn.Hidden = True
            .Columns("L:L").EntireColumn.Hidden = True
            .Columns("N:T").EntireColumn.Hidden = True
    '        Set rng = .Range("A3:T" & .Range("A3").End(xlDown).Row)
    
            Call Sort_Me
    
            .Range("A1").Select
        End With
        Application.CutCopyMode = False
        Application.ScreenUpdating = True
        ws1.AutoFilterMode = False
        Application.ScreenUpdating = True
    End Sub
    
    
    Sub Sort_Me()
        Dim ws As Worksheet
        Dim rng As Range, cel As Range
        Dim iCustListNum As Long
    
        Set ws = Sheets("Current Thread Gauge List")
        With ws
            .Columns("AC:AF").Delete
            .Columns("AC:AE").NumberFormat = "@"
            .Columns("AF:AF").NumberFormat = "General"
            Set rng = .Range("B4:B" & .Range("A3").End(xlDown))
            rng.Copy Destination:=.Range("AC4")
            Set rng = .Range("AC4:AC" & .Range("A3").End(xlDown))
            For Each cel In rng
                On Error Resume Next
                cel.Offset(0, 1).Value = Split(cel, "-")(0)
                cel.Offset(0, 2).Value = Split(cel, "-")(1)
                cel.Offset(0, 3).FormulaR1C1 = "=CONCATENATE(RC[-2],""-"",RC[-1])"
                On Error GoTo 0
            Next cel
            .Sort.SortFields.Clear
            .Sort.SortFields.Add Key _
                    :=Range("AD4:AD" & .Range("A3").End(xlDown)), SortOn:=xlSortOnValues, Order:=xlAscending, _
                    DataOption:=xlSortTextAsNumbers
            With ws.Sort
                .SetRange Range("AD4:AE" & Range("A3").End(xlDown))
                .Header = xlNo
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
            Set rng = .Range("AF4:AF" & .Range("A3").End(xlDown))
    
            iCustListNum = Application.CustomListCount + 1
            Application.AddCustomList rng.Value
    
            .Sort.SortFields.Clear
            .Sort.SortFields.Add Key _
                    :=Range("B3:B" & .Range("A3").End(xlDown)), CustomOrder:= _
                    Application.CustomListCount, DataOption:=xlSortTextAsNumbers
            With ActiveWorkbook.Worksheets("Current Thread Gauge List").Sort
                .SetRange Range("A3:M" & Range("A3").End(xlDown))
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
            Application.DeleteCustomList Application.CustomListCount
            .Columns("AC:AF").Delete
        End With
    
    End Sub
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Specific rows in several worbooks need to be extracted to a new workbook
    By patchpollito in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-07-2013, 04:21 PM
  2. [SOLVED] Using VBA how do you close a user defind workbook after you have extracted data
    By klonbeck in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-14-2012, 06:00 PM
  3. [SOLVED] Extracted names from list if value greater than 2
    By MajorT in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-31-2012, 10:05 PM
  4. Cannot view personal workbook on doc extracted from database
    By fabrecass in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-04-2011, 09:43 AM
  5. New sheet name extracted from various cells, sorting sheets, saving new workbook
    By Luccin in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-10-2010, 05:51 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