autofilter with a twist

Kevin, I assume you mean the headings in your data table when you state "one row above the range of cells". You could copy all the data, including the headings, and then delete that first row after pasting the data (that's what I have done). Something like:

ActiveCell.EntireRow.Delete

Regards,
BarrieBarrie Davidson

Barrie,

Yes that is exactly what I am talking about - but deleting the row after I paste is not really an option. I am pasting into a completely different accounting/billing program that our company uses, and if I paste the range in and it includes that top blank line, I get an error message and the program will not accept any of it. So I will need to figure out a way to remove that top line from the range before I copy the range to the clipboard.

Thanks,
Kevin

Kevin, how about pasting the data to a blank worksheet, deleting the heading row, and then copying that data (from the blank worksheet) where you need it?

BarrieBarrie Davidson

Kevin,

Assuming the records being filtered are the ones with an accounts balance, see if this code does what you want. It assumes you are filtering on Sheet1, copying to Sheet2 and that you have already nailed the filter part of the code. It also allows for a dynamic range destination in case you are adding filtered records onto Sheet2 as you go:

Sheet1.UsedRange.Offset(1, 0).SpecialCells(xlTypeVisible).Copy _
Destination:=Sheet2.Range("A65536").End(xlUp).Offset(1, 0)

HTH

Tom Urtis

That would work, but is there any way that this task can be easily automated, because otherwise we might as well just manually change the range selected before copying to the clipboard. My main concern is making this work with as few steps as possible, because the people using it are not very computer-literate.

Thanks,
Kevin

Kevin, how about code something like this?

Sub YourSub()
Dim dummyFile As String
Dim dataFile As String

dataFile = ActiveWorkbook.Name
'Your code to filter, select, and copy the data
Workbooks.Add
dummyFile = ActiveWorkbook.Name
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.EntireRow.Delete
ActiveSheet.UsedRange.Select
Selection.Copy
'Your code to select the destination to copy the data to
Workbooks(dummyFile).Close (False)
Workbooks(dataFile).Activate
'Your code to clear the filters

End Sub

BarrieBarrie Davidson

Barrie,

I will give this a try. Is there any way in VB to use an IF statement of some sort in order to select cells in a range based on certain conditions, for example after filtering, select only cells that have a value greater than zero?

Just wondering,
Kevin

Can you add this to your filter criteria?

BarrieBarrie Davidson

Barrie,

This is what my code looks like right now:

Range("A5:D328").Select
Selection.AutoFilter
Selection.AutoFilter Field:=4, Criteria1:=">0", Operator:=xlAnd

I am not really sure how I would have to modify this code to make it reselect the range of cells that contain only values greater than zero. Do you?

Thanks,
Kevin

I think VBA is required. Here's one way. Put this in the workbook module if you want the sheet name in all sheets :-

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Range("A1") = ActiveSheet.Name
End Sub

If you only want it for one particlar sheet, put this in the sheet's module :-

Private Sub Worksheet_Activate()
Range("A1") = ActiveSheet.Name
End Sub

Don't know what's wrong with this board, but

Field:=4, Criteria1:=">0", Operator:=xlAnd

Mark, if you put this after your criteria code it will select the visible cells only.

Selection.SpecialCells(xlCellTypeVisible).Select

You can then copy via

Selection.Copy

Hope this is what you need.

Regards,
BarrieBarrie Davidson