UtterAccess Discussion Forums > Filter, copy to new sheet, set print area and print

Full Version: Filter, copy to new sheet, set print area and print

UtterAccess Discussion Forums > Microsoft® Office > Microsoft Excel

ajalanis

Mar 17 2006, 11:11 AM

Hi to everybody, I need some help on this: I have some data in a sheet, that is a big listing. In the first column, the information is the location of products in the warehouse, in format X-XXX-X-XX, or X-XXX-XXX, being the first characters (first 5, including the "-") the number of a specific rack, which has a lot of ubications on itself.
I want to set in a cell the number of the rack I want to print, and that the code filter the entire data for the rows in which that column starts with that number, copy it to a new sheet (or to a sheet already prepared for printing), and print it.

Can someone help me with this? I´d really appreciate any help

Thanks to all you folks

Arturo

ajalanis

Mar 17 2006, 02:09 PM

Hi, I´m already trying to do this, only a question: how can I do to use the value of a cell as a criteria for the autofilter? See:

Selection.AutoFilter
Dim Est As String
Est = Range("G1").Value
If Len(Est) > 0 Then
Selection.AutoFilter Field:=1, Criteria1:="=Est *", Operator:=xlAnd

I know the problem is in the Criteria1=

Thanks for your help

Luceze

Mar 17 2006, 04:09 PM

Lets assume that you data is in columns A:D, your rack numbers are in column A, and your criteria is in cell E1.

CODE

Sub FilterCopy()
    Application.ScreenUpdating = False
    Dim sh As Worksheet
    Set sh = Sheets("Sheet1")
    sh.Range("A:A").AutoFilter 1, sh.Range("e1")
    sh.Range("A1", sh.Range("D" & sh.Range("A65536").End(xlUp).Row)) _
        .SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet2").Range("a1")
    sh.Range("A:A").AutoFilter
End Sub

This will copy the filtered data from sheet1 to sheet2.

HTH,

ajalanis

Mar 17 2006, 04:49 PM

Thank you Eric, that´s a great code, but the data in the first column start with the criteria in cell e1, e.g., criteria is 1-101, but the data in column A is 1-101-A-01, 1-101-1-02, etc., so I need to use the autofilter with the "Start with" statement.
How can I use that?

Thanks

KingMartin

Mar 17 2006, 06:00 PM

Hi Arturo,

QUOTE

so I need to use the autofilter with the "Start with" statement.

Not necessarily, concatenate the wildcard character together with the cell's contents:

CODE

rg.AutoFilter Field:=1, Criteria1:="=" & sh.Range("e1").Value & "*"

*untested*

Martin

ajalanis

Mar 20 2006, 01:38 PM

Thank you Martin and Eric, it works great

ajalanis

Mar 20 2006, 01:46 PM

Another question. How can I do for setting the print area with variable dimensions? The following code is as recorded in step by step:

ActiveSheet.PageSetup.PrintArea = ""
Range("A1:E1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$E$125"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

but I want not to always set the print area as $A$1:$E$125, but to vary as how many rows contains data in the moment.

Thanks again

KingMartin

Mar 20 2006, 01:53 PM

Hi again Arturo,

try:

CODE

[color="blue"]With[/color] ActiveSheet
.PageSetup.PrintArea = "A1:E" & .[a65536].End(3).Row
[color="blue"]End[/color] [color="blue"]With[/color]

Martin

ajalanis

Mar 20 2006, 02:12 PM

Thanks again Martin, that works perfectly

KingMartin

Mar 20 2006, 02:13 PM

You're welcome

Martin

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.