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
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:
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
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.
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)) _
This will copy the filtered data from sheet1 to sheet2.
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?
Mar 17 2006, 06:00 PM
so I need to use the autofilter with the "Start with" statement.
Not necessarily, concatenate the wildcard character together with the cell's contents:
rg.AutoFilter Field:=1, Criteria1:="=" & sh.Range("e1").Value & "*"
Mar 20 2006, 01:38 PM
Thank you Martin and Eric, it works great
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 = ""
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.
Mar 20 2006, 01:53 PM
Hi again Arturo,
.PageSetup.PrintArea = "A1:E" & .[a65536].End(3).Row
Mar 20 2006, 02:12 PM
Thanks again Martin, that works perfectly
Mar 20 2006, 02:13 PM
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here