Now problem with autofilter

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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).Cop
Windows(grade(i)).Sheets("Sheet1").Range("A1").PasteSpecia
.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
 
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
 
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
 
Back
Top