D
Diddy
Hi everyone,
I'm trying to select a folder then on each worksheet in each book I would
like to clear the contents of hidden rows 1-4 and then run David McRitchie's
Trimall routine on column H.
Sub PrepClear_Trimall()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
On Error Resume Next
Set wbCodeBook = ThisWorkbook
With Application.FileSearch
.NewSearch
strFolder = InputBox("Please enter folder", "Path")
.LookIn = strFolder
.FileType = msoFileTypeExcelWorkbooks
.Filename = "*.xls"
If .Execute > 0 Then 'Workbooks in folder
For lCount = 1 To .FoundFiles.Count 'Loop through all.
'Open Workbook x and Set a Workbook variable to it
Set wbResults = Workbooks.Open(.FoundFiles(lCount))
Dim Shts As Worksheet
'Carries out action for ALL worksheets in active workbook
For Each Shts In ActiveWorkbook.Worksheets
Shts.Rows("1:4").Select
Range("B1").Activate
Selection.ClearContents
Columns("H:H").Select
Range("H5").Activate
Call Trimall
Next
wbResults.Close SaveChanges:=True
Next lCount
End If
End With
On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub
I've pasted in some recorded macro bits and it works after a fashion but
only carries out the actions on the active sheet of each workbook it goes
into.
Where am I going wrong?
Thank you
I'm trying to select a folder then on each worksheet in each book I would
like to clear the contents of hidden rows 1-4 and then run David McRitchie's
Trimall routine on column H.
Sub PrepClear_Trimall()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
On Error Resume Next
Set wbCodeBook = ThisWorkbook
With Application.FileSearch
.NewSearch
strFolder = InputBox("Please enter folder", "Path")
.LookIn = strFolder
.FileType = msoFileTypeExcelWorkbooks
.Filename = "*.xls"
If .Execute > 0 Then 'Workbooks in folder
For lCount = 1 To .FoundFiles.Count 'Loop through all.
'Open Workbook x and Set a Workbook variable to it
Set wbResults = Workbooks.Open(.FoundFiles(lCount))
Dim Shts As Worksheet
'Carries out action for ALL worksheets in active workbook
For Each Shts In ActiveWorkbook.Worksheets
Shts.Rows("1:4").Select
Range("B1").Activate
Selection.ClearContents
Columns("H:H").Select
Range("H5").Activate
Call Trimall
Next
wbResults.Close SaveChanges:=True
Next lCount
End If
End With
On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub
I've pasted in some recorded macro bits and it works after a fashion but
only carries out the actions on the active sheet of each workbook it goes
into.
Where am I going wrong?
Thank you