Re: Filtered list

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hi,
Thanks for what you offer, I will try it. Just want you know it is going to
be pasted in a new empty worksheet.

Khalil

"Dave Peterson" <petersod@xxxxxxxxxxxxxxxx> wrote in message
news:44109EE3.75C5EDAE@xxxxxxxxxxxxxxxxxxx
Since your code depends on what's selected, it's difficult to guess where
things
should be pasted.

But this may give you an idea. I didn't test it, but it did compile:

Option Explicit
Sub testme()

Dim RngToFilter As Range
Dim RngToCopy As Range
Dim DestWks As Worksheet
Dim DestCell As Range
Dim LastRow As Long

With ActiveSheet
.Unprotect Password:="hithere"
'turn off any existing filter
.AutoFilterMode = False
Set RngToFilter = .Range("ei16", .Cells(.Rows.Count,
"EI").End(xlUp))
RngToFilter.AutoFilter Field:=1, Criteria1:="<>"
If RngToFilter.Cells.SpecialCells(xlCellTypeVisible).Count = 1 Then
'no visible rows in filter.
Set RngToCopy = Nothing
Else
With RngToFilter
Set RngToCopy = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible)
End With
End If
.AutoFilterMode = False
.Protect Password:="hithere"
End With

If RngToCopy Is Nothing Then
MsgBox "Nothing filtered--quitting"
Exit Sub
End If

Set DestWks = Nothing
On Error Resume Next
Set DestWks = Workbooks("w_v.xls").Worksheets("sheet2")
On Error GoTo 0
If DestWks Is Nothing Then
Set DestWks = Workbooks.Open("H:\W_V.xls").Worksheets("sheet2")
End If

With DestWks
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row + 1
Set DestCell = .Cells(LastRow, "A")
End With

RngToCopy.EntireRow.Copy _
Destination:=DestCell

Application.CutCopyMode = False

End Sub






Khalil Handal wrote:

Hi, I hope that some one can help!

I want to take a certain range of cells in the file "HCP_2005_upgrade"
and
filter so as to select all the cells that are not empty.

Then select all the rows for these cells and copy them to a new workbook
"W_V" in "sheet2". I have the following code.

Three problems:

1- When copying to the new workbook I did not have the
same
column width. What should I do in order to have the same column width?

2- What code do I need to add, and where, so as to let
the
macro automatically find the last row that is not empty (particularly in
column B). Select only the filtered range.

3- My sheet is protected. The auto filter only works for
unprotected sheets. How can I overcome this problem or go around it.
(i.e.
to do filtration for protected sheets).

The code is:

Sub Macro1()

Range("EI16:EI159").Select

Selection.AutoFilter

Selection.AutoFilter Field:=1, Criteria1:="<>"

Rows("1:44").select --à (what code do I need so that the macro do this
automaticaly since it changes often).

Workbooks.Open Filename:="H:\W_V.xls"

Sheet("Sheet2").Select

Selection.PasteSpecial Paste:x1PasteColumnWidths, Operation:=x1None,_

SkipBlanks:=False, Transpose:=False

ActiveSheet.Paste

Range("E7").Select

ActiveWindow.FreezePanes=True

Windows("HCP_2006_Upgrade.xls").Activate

Range("A16").Select

Application.CutCopyMode=False

Selection.AutoFilter

Range("A1").Select

End Sub

Khalil

--

Dave Peterson


.

Relevant Pages

  • EXAMPLE: fill menu from path
    ... I wanted the ability to store any given filter to a user file, and be able to display it in a user organized menu system. ... Dim testPathsAs String ... Sub initForm() ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Enter key behavior
    ... message I get when I try and switch to form view. ... Code in dialog form to generate filter: (In a button to open the filtered ... Private Sub cmdOK_Click ... Dim stLinkCriteria As String ...
    (microsoft.public.access.formscoding)
  • Re: Error 3048 "Cannot open any more databases"
    ... I'm not sure where the code above runs - is it the main form or the subform? ... check the filter string to see if it looks OK ... Private Sub Form_Current ... Dim rst As DAO.Recordset ...
    (microsoft.public.access.formscoding)
  • Re: Looping Through Variables
    ... Sub RunCheck ... I am assuming that you use "empty" instead of "" to check if the cell is empty. ... Dim CL ... The next code "RunCheck" is what checks the variable list. ...
    (microsoft.public.excel.misc)
  • Re: Access module does not continue past the Filter Method
    ... Sub SetStartupProperties() ... Dim prpNew As DAO.Property ... I also added the "Dirty" code, ... Debug.Print "Past the Filter" ...
    (microsoft.public.access.modulesdaovba)