ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Macro doesn't loop through sheets of books in specific folder (https://www.excelbanter.com/excel-programming/418346-macro-doesnt-loop-through-sheets-books-specific-folder.html)

Diddy
Macro doesn't loop through sheets of books in specific folder
 
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

--
Deirdre

Gary''s Student
Macro doesn't loop through sheets of books in specific folder
 
After:

For Each Shts In ActiveWorkbook.Worksheets
insert:
Shts.Activate


This will help insure the various ranges are associated with Shts
--
Gary''s Student - gsnu200806


"Diddy" wrote:

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

--
Deirdre

Dave Peterson
Macro doesn't loop through sheets of books in specific folder
 
Untested, but it did compile:

Option Explicit
Sub PrepClear_Trimall()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Dim Shts As Worksheet
Dim strFolder As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

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))
'Carries out action for ALL worksheets in active workbook
For Each Shts In wbResults.Worksheets
Shts.Select
Shts.Rows("1:4").ClearContents
Shts.Columns("H:H").Select
Call Trimall
Next Shts
wbResults.Close SaveChanges:=True
Next lCount
End If
End With

Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True

End Sub

Diddy wrote:

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

--
Deirdre

--

Dave Peterson

Diddy
Macro doesn't loop through sheets of books in specific folder
 
Thank you Gary's Student :-)

Is there a difference between Activate and Select in this case?

Cheers
Diddy
--
Deirdre


"Gary''s Student" wrote:

After:

For Each Shts In ActiveWorkbook.Worksheets
insert:
Shts.Activate


This will help insure the various ranges are associated with Shts
--
Gary''s Student - gsnu200806


"Diddy" wrote:

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

--
Deirdre

Diddy
Macro doesn't loop through sheets of books in specific folder
 
Thank you Dave :-)

Works beautifully

Cheers
Diddy
--
Deirdre


"Dave Peterson" wrote:

Untested, but it did compile:

Option Explicit
Sub PrepClear_Trimall()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Dim Shts As Worksheet
Dim strFolder As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

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))
'Carries out action for ALL worksheets in active workbook
For Each Shts In wbResults.Worksheets
Shts.Select
Shts.Rows("1:4").ClearContents
Shts.Columns("H:H").Select
Call Trimall
Next Shts
wbResults.Close SaveChanges:=True
Next lCount
End If
End With

Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True

End Sub

Diddy wrote:

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

--
Deirdre

--

Dave Peterson

Rick Rothstein
Macro doesn't loop through sheets of books in specific folder
 
From the Remarks section of the help files for the Select Method (for a
range)...

"To select a cell or a range of cells, use the Select method.
To make a single cell the active cell, use the Activate method."

--
Rick (MVP - Excel)


"Diddy" wrote in message
...
Thank you Gary's Student :-)

Is there a difference between Activate and Select in this case?

Cheers
Diddy
--
Deirdre


"Gary''s Student" wrote:

After:

For Each Shts In ActiveWorkbook.Worksheets
insert:
Shts.Activate


This will help insure the various ranges are associated with Shts
--
Gary''s Student - gsnu200806


"Diddy" wrote:

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

--
Deirdre



All times are GMT +1. The time now is 03:12 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com