Selecting autofiltered cells
 

Hi,
I am trying to create a macro in Excel 97 that will select some
autofiltered cells from one sheet , copy and paste them to another
sheet. The code that I am using is:

With Sheets("Initial Test Store Data").AutoFilter.Range
On Error Resume Next
Set Sum_Order_Amt = .Offset(1, 0).Resize(.Rows.Count - 1, 1)
..SpecialCells (xlCellTypeVisible)
Set Year_Week_Number = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
Set Retail_Outlet_Number = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
Set Product_Types = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With

But when running the program VB does nothing at this point. Can you
help me?

Thank you

Selecting autofiltered cells
 

You set 4 variables to the same range (visible cells in the autofilter
except the header row, first column). You never do a copy

Sum_Order_Amt.Entirerow.copy _
Destination:=worksheets("Sheet2").Range("A1")



With Sheets("Initial Test Store Data").AutoFilter.Range
On Error Resume Next
Set Sum_Order_Amt = .Offset(1, 0).Resize(.Rows.Count - 1, 1)
..SpecialCells (xlCellTypeVisible)
Set Year_Week_Number = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
Set Retail_Outlet_Number = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
Set Product_Types = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With

--
Regards,
Tom Ogilvy


"Annita" wrote in message
om...
Hi,
I am trying to create a macro in Excel 97 that will select some
autofiltered cells from one sheet , copy and paste them to another
sheet. The code that I am using is:

With Sheets("Initial Test Store Data").AutoFilter.Range
On Error Resume Next
Set Sum_Order_Amt = .Offset(1, 0).Resize(.Rows.Count - 1, 1)
.SpecialCells (xlCellTypeVisible)
Set Year_Week_Number = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
Set Retail_Outlet_Number = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
Set Product_Types = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With

But when running the program VB does nothing at this point. Can you
help me?

Thank you

Selecting autofiltered cells
 

It would appear that you are setting four object variables to refer to
the same range. But then, I don't see any attempt to copy anything.
Maybe, you want something like:

With Sheets("Initial Test Store Data").AutoFilter.Range
.SpecialCells(xlCellTypeVisible).Copy _
Destination:=Worksheets("sheet2").Range("a1")
End With
Worksheets("sheet2").Range("a1").EntireRow.Delete

Also, unless you have a very specific reason why you need to avoid
errors with 'On Error Resume Next' don't use such a statement. All
that it does is make debugging difficult, if not impossible.

There are a few subtle problems with your code.
* Rows.Count returns a count of only the rows in the first area of a
non-contiguous range.
* What does it mean to resize a range that contains multiple areas?

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , akats80
@hotmail.com says...
Hi,
I am trying to create a macro in Excel 97 that will select some
autofiltered cells from one sheet , copy and paste them to another
sheet. The code that I am using is:

With Sheets("Initial Test Store Data").AutoFilter.Range
On Error Resume Next
Set Sum_Order_Amt = .Offset(1, 0).Resize(.Rows.Count - 1, 1)
.SpecialCells (xlCellTypeVisible)
Set Year_Week_Number = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
Set Retail_Outlet_Number = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
Set Product_Types = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With

But when running the program VB does nothing at this point. Can you
help me?

Thank you

Selecting autofiltered cells
 

There are a few subtle problems with your code.
* Rows.Count returns a count of only the rows in the first area of a
non-contiguous range.

but if .Rows.count is qualified by

With Sheets("Initial Test Store Data").AutoFilter.Range

as it appears to be in (for example)

Set Sum_Order_Amt = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells (xlCellTypeVisible)

Then it should be a contiguous single area and appropriately used here to
exclude the header from the rest of the range.

--
Regards,
Tom Ogilvy


"Tushar Mehta" wrote in message
news:MPG.1b867ed4601d21ac9898d0@news-server...
It would appear that you are setting four object variables to refer to
the same range. But then, I don't see any attempt to copy anything.
Maybe, you want something like:

With Sheets("Initial Test Store Data").AutoFilter.Range
.SpecialCells(xlCellTypeVisible).Copy _
Destination:=Worksheets("sheet2").Range("a1")
End With
Worksheets("sheet2").Range("a1").EntireRow.Delete

Also, unless you have a very specific reason why you need to avoid
errors with 'On Error Resume Next' don't use such a statement. All
that it does is make debugging difficult, if not impossible.

There are a few subtle problems with your code.
* Rows.Count returns a count of only the rows in the first area of a
non-contiguous range.
* What does it mean to resize a range that contains multiple areas?

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , akats80
@hotmail.com says...
Hi,
I am trying to create a macro in Excel 97 that will select some
autofiltered cells from one sheet , copy and paste them to another
sheet. The code that I am using is:

With Sheets("Initial Test Store Data").AutoFilter.Range
On Error Resume Next
Set Sum_Order_Amt = .Offset(1, 0).Resize(.Rows.Count - 1, 1)
.SpecialCells (xlCellTypeVisible)
Set Year_Week_Number = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
Set Retail_Outlet_Number = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
Set Product_Types = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With

But when running the program VB does nothing at this point. Can you
help me?

Thank you

Selecting autofiltered cells
 

Hi Tom,

For some reason, in interpreting the post, I mentally transformed

..Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells (xlCellTypeVisible)

into

..SpecialCells (xlCellTypeVisible).Offset(1, 0) _
.Resize(.Rows.Count - 1, 1)

and proceeded with that as the starting point.

Duh!

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
There are a few subtle problems with your code.
* Rows.Count returns a count of only the rows in the first area of a
non-contiguous range.

but if .Rows.count is qualified by

With Sheets("Initial Test Store Data").AutoFilter.Range

as it appears to be in (for example)

Set Sum_Order_Amt = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells (xlCellTypeVisible)

Then it should be a contiguous single area and appropriately used here to
exclude the header from the rest of the range.