Exract Data And Error Msg

H

Hornet

ERROR MSG:
Microsoft Excel cannot determine which row in your list or selection contains column labels which are required for this operation

I can’t figure out why:
The names of my Columns are as follows:
ITEM QTY DAYS TOTAL
I formatted them as Text. How do I make Excel know they are column labels and furthermore…

I am composing an invoice for my rental business. I have set up a sheet with all the items that I rent. The cost of each item is determined by the number of items time the number of days rented. I would like to extract, to a separate location,(sheet 2) only the items that were rented or Criteria QTY>0
I was trying to use the Auto Filter but that doesn’t seem to have the option of putting the data in another location. I can’t figure out how to do the advanced filter. I would like to extract every row on the list where the QTY is greater than zero and paste them into another location ,(sheet 2) where I will have our logo etc at top of letter size page and other info at bottom.

On the contextures website there is this explanation which is wayway over my head:

Sub CopyFilter()
'by Tom Ogilvy
Dim rng As Range
Dim rng2 As Range

With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
If rng2 Is Nothing Then
MsgBox "No data to copy"
Else
Worksheets("Sheet2").Cells.Clear
Set rng = ActiveSheet.AutoFilter.Range
rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy _
Destination:=Worksheets("Sheet2").Range("A1")
End If
ActiveSheet.ShowAllData

End Sub

Whoah!!!! Where does that happen.

I thought what I'm trying to do would be pretty simple!
But then again I'm a Mac graphic program expert???(Photoshop,Illustrator,Quark,FCP) Maybe I can help you with those programs.
If you email me I could send a file.
Thanks
 
J

Julia0001

Hi ther

It sounds like you need to use Advanced Filter, which is the feature that allows you to filter to another location. To do this you have to set up a Criteria range, which is a range of cells containing the column headings and criteria you want to use

The online help should have a full example of how to do this - try looking up "advanced criteria"

Hope this helps

Julia
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top