PDA

View Full Version : Turning off Autofilters & formatting



DAMAN
02-08-2010, 07:55 PM
Hi

I have 214 excel workbooks and I would like to turn off the autofilters in every single one of them, is there some code that can do this automatically to turn it off in everyone of them?

Also I would like to insert text with the same message in all 214 workbooks, say in row 5 of each workbook, there is a only one worksheet?

Can anyone please help with some code to solve this?

Thanks

mbarron
02-08-2010, 08:47 PM
If all books are in the same directory, you can use the following.

Sub removeAutoFilter()
Application.ScreenUpdating = False
Dim wbkTo As Workbook 'current book
Dim wsTo As Worksheet 'dest sheet in current book
Dim wbFr As Workbook 'wb from directory
Dim wsFr As Worksheet
Dim strPath As String 'location directory of files
Dim strFile As String 'file name
Dim i As Long, j As Long, fCount As Integer, f As Integer
Dim strDirect As String
'x-x-x-x gets the directory for import x-x-x-x
Dim wrkCur As Workbook
Set wrkCur = ActiveWorkbook
Application.Dialogs(xlDialogOpen).Show
If wrkCur.Name = ActiveWorkbook.Name Then
MsgBox "You canceled the operation", vbExclamation
Exit Sub
End If
strPath = ActiveWorkbook.Path & "\"
ActiveWorkbook.Close savechanges:=False
strFile = Dir(strPath & "*.xls")
Set wsTo = ActiveSheet


strFile = Dir(strPath & "*.xls")

Do While Not strFile = ""

'x-x-x-x-x-x Do stuff with the workbooks x-x-x-x-x-x-x-x
Set wbFr = Workbooks.Open(strPath & strFile)
Set wsFr = wbFr.Sheets(1)
'x-x-x-x-x-x do stuff x-x-x-x-x-x-x-x
Cells(5, 1) = "Special Message"
If wsFr.AutoFilterMode = True Then
Cells.AutoFilter
End If
wbFr.Close savechanges:=True
strFile = Dir
Loop
Application.ScreenUpdating = True
End Sub

DAMAN
02-08-2010, 09:10 PM
Thanks MBarron! thats great