Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Now problem with autofilter
Ok, I think I have added an array correctly, but I now have a problem with the autofilter portion of this program. It is filtering from data on "Sheet1" in the workbook containing the macro and outputting to the file that is opened by the macro (called grade(i) in the array) and saving it there. I could really use some assistance, I am stuck
Also, the array is now hard coded, it will need to eventually be filled by values on a sheet in a specific column, any advice on that would be great too, as I have gotten way out of my comfort range trying to develope this code, and could use someone knowledgeable to go over it Sub FindOrCreate( Application.DisplayAlerts = Fals Dim MyPath As Strin Dim sh As Workshee Dim grade As Varian Dim Sht1 As Workshee Dim Sht2 As Workshee Dim i As Intege Dim r As Intege Dim c As Intege grade = Array("12004", "122003", "22004" Set Sht1 = Sheets("Sheet1" Set Sht2 = Sheets("Sheet2" For i = 0 To If Dir(ThisWorkbook.Path & "\" & grade(i) & ".xls") < "" The 'Ope Workbooks.Open (ThisWorkbook.Path & "\" & grade(i) & ".xls" Els 'Creat Workbooks.Ad Sheets("Sheet2").Selec ActiveWindow.SelectedSheets.Delet Sheets("Sheet3").Selec ActiveWindow.SelectedSheets.Delet End I Windows("Working.xls").Activat With Sht .Columns("A:F").AutoFilter Field:=6, Criteria1:=grade(i .Columns("A:F").SpecialCells(xlCellTypeVisible).Co p Windows(grade(i)).Sheets("Sheet1").Range("A1").Pas teSpecia .Columns("F:F").AutoFilte End Wit With ActiveWorkboo .SaveAs Filename:=ThisWorkbook.Path & "\" & grade(i) & ".xls", FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=Fals .Clos End Wit Next Application.DisplayAlerts = Tru End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Now problem with autofilter
Ok, I have fixed the autofilter problem, I guess maybe I know more than I thought I did, or at least I got it figured out, only one last problem, the array part.
Sub FindOrCreate() Application.DisplayAlerts = False Dim MyPath As String Dim sh As Worksheet Dim grade As Variant Dim Sht1 As Worksheet Dim Sht2 As Worksheet Dim i As Integer Dim r As Integer Dim c As Integer grade = Array("12004", "122003", "22004") I need to fill this array with the values in sheet1, in column E. They start in row one, but I won't know how long the list wll be so I figure I need to use a lastrow.count type of code. I will keep looking in google for an answer to this but any help would be appreciated. Thanks, Jim |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Now problem with autofilter
Sounds like you've got a similar problem to mine!
http://www.excelforum.com/t181735-s I'll let you know if I find out! Boici -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Now problem with autofilter
Try something like this;
Dim arr() Dim iCount As Integer Do ReDim Preserve arr(iCount) arr(iCount) = Range("E1").Offset(iCount, 0).Value iCount = iCount + 1 Loop Until Range("E1").Offset(iCount, 0).Value = "" Hope this helps. Boici -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
AutoFilter Problem | Excel Discussion (Misc queries) | |||
PROBLEM WITH AUTOFILTER | New Users to Excel | |||
autofilter problem | Excel Worksheet Functions | |||
Problem with autofilter in VBA | Excel Programming | |||
AutoFilter Problem | Excel Programming |