Results 1 to 10 of 10

Thread: Create a table on a seperate sheet in the same workbook
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Nov 2014
    Location
    Newcastle-Upon-Tyne
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Create a table on a seperate sheet in the same workbook

    Hi all,

    i would like to create a table of selected information from a tracking sheet i have.

    basically the tracker is a vehicle to store all requests that i get in for documents of information. i would like to create a priorty table on a seperate sheet so i can see which ones require working on first etc. To do this i created a coloumn (A) to put in a number to represent the priotriy level, 1 being the highest priortiy. In column AB i have a completion date, so dont want to include anything that is completed. Only open requests.

    is there a way to pull information for everything that has a number in column A (and nothing in column AB) for these columns
    A, B, F, K, O, X? then is there a way to automate the new table to have number 1's first followed by 2's etc?

    Probably not explained this very well sorry!
    any help is appreciated.

    Thanks
    George

  2. #2
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    7,873
    Post Thanks / Like
    Mentioned
    83 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Create a table on a seperate sheet in the same workbook

    How many priority levels do you have and what are the numbers representing each priority?
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Board Regular
    Join Date
    Nov 2014
    Location
    Newcastle-Upon-Tyne
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Create a table on a seperate sheet in the same workbook

    sorry for the late reply,

    i will have 6 priority levels ranging from 1-6 (number 1 being the highest priotity)

    thanks
    George

  4. #4
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    7,873
    Post Thanks / Like
    Mentioned
    83 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Create a table on a seperate sheet in the same workbook

    Re-name one of your blank sheets to "Priority Table" (no quotes) and then try this macro. The macro assumes you have headers in row 1 and your data starts in row 2.
    Code:
    Sub CopyData()
        Application.ScreenUpdating = False
        Dim LastRow As Long, desWS As Worksheet
        Set desWS = Sheets("Priority Table")
        desWS.UsedRange.ClearContents
        LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        With Cells(1, 1).CurrentRegion
            .AutoFilter Field:=1, Criteria1:="<=6"
            .AutoFilter Field:=28, Criteria1:="<>"
            Intersect(.Rows("1:" & LastRow).SpecialCells(xlCellTypeVisible), .Range("A:A,B:B,F:F,K:K,O:O,X:X")).Copy desWS.Cells(1, 1)
        End With
        Range("A1").AutoFilter
        LastRow = desWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        desWS.Sort.SortFields.Clear
        desWS.Sort.SortFields.Add Key:=Range("A2:A" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With desWS.Sort
            .SetRange Range("A1:F" & LastRow)
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        Application.ScreenUpdating = True
    End Sub
    Last edited by mumps; May 15th, 2019 at 08:27 AM.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  5. #5
    Board Regular
    Join Date
    Nov 2014
    Location
    Newcastle-Upon-Tyne
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Create a table on a seperate sheet in the same workbook

    Thanks for that, it didnt work though.....

    Run time error "91" Object variable or block not set
    when i click on debug it highlights the line which begins with "lastRow" ??

  6. #6
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    7,873
    Post Thanks / Like
    Mentioned
    83 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Create a table on a seperate sheet in the same workbook

    When I tested the macro on some dummy data, it worked properly. I think that it would be easier to help and test possible solutions if I could work with your actual file which includes any macros you are currently using. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  7. #7
    Board Regular
    Join Date
    Nov 2014
    Location
    Newcastle-Upon-Tyne
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Create a table on a seperate sheet in the same workbook

    Ok thanks for that, i have uploaded to dropbox

    https://www.dropbox.com/s/k1dhirqqtl...02019.xls?dl=0

  8. #8
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    7,873
    Post Thanks / Like
    Mentioned
    83 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Create a table on a seperate sheet in the same workbook

    I'm having trouble downloading your file. How big is your file? I'm not sure if there is a problem with DropBox or the file is extremely large.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  9. #9
    Board Regular
    Join Date
    Nov 2014
    Location
    Newcastle-Upon-Tyne
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Create a table on a seperate sheet in the same workbook

    it says 1.26mb so not that big....

  10. #10
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    7,873
    Post Thanks / Like
    Mentioned
    83 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Create a table on a seperate sheet in the same workbook

    I've tried to download the file several times. Even at 1.26 mb it shouldn't take too long to download. However, DropBox displays the file but then seems to just freeze up. Could I suggest you try to upload it to Box.com instead?
    Practice makes perfect. I'm very far from perfect so I'm still practising.

Some videos you may like

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
  •