Create a table on a seperate sheet in the same workbook

gebo84

Board Regular
Joined
Nov 6, 2014
Messages
96
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
How many priority levels do you have and what are the numbers representing each priority?
 
Upvote 0
sorry for the late reply,

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

thanks
George
 
Upvote 0
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:
Upvote 0
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" ??
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top