Problem in updating all worksheets of a workbook using a macro that calls another macro

Hello,

I have been trying to run a macro (Macro1) that calls another macro
(Macro2) and runs the latter on all the worksheets of an Excel
workbook. But Macro2 seems to be running on only three worksheets of
the workbook. I am quite sure about the accuracy of Macro2. There is
some problem with the code of Macro1 and I do not quite know where the
problem lies. The code is as follows:

----------------------------------------------------------------------------------------------------------------------
Sub Macro1()
Dim sFile$
'Specifying path of the Excel file
Const path = "E:\TEST\"
Dim WS_Count As Integer
WS_Count = ActiveWorkbook.Worksheets.Count
Dim ws As Worksheet
Dim I As Integer
sFile = Dir(path & "*.xls")
Do While sFile <> ""
Workbooks.Open (path & sFile)
Set ws = ActiveSheet
For I = 1 To WS_Count
Set ws = ActiveWorkbook.Worksheets(I)
ws.Activate
Range("A1").Select
Application.Run "Macro2"
Next I
ActiveWorkbook.Close savechanges:=True
sFile = Dir
Exit Do
Loop
End Sub
-----------------------------------------------------------------------------------------------------------------

Please help me out as I need this problem to be sorted out urgently.


Thanks,

Sairam

Hi Sairam,

Your problem would appear to reside in the fact that WS_Count variable is
defined as the number of worksheets in the workbook which is active when the
code starts, not the workbook which is opened. If therefore, the currently
active workbook only contains three worksheets, only the first three
worksheets in any workbook which is opened will be processed by your code.

To resolve this, move the line:

> WS_Count = ActiveWorkbook.Worksheets.Count

after the lne:

> Workbooks.Open (path & sFile)


---
Regards,
Norman



<[email protected]> wrote in message
news:[email protected]...
> Hello,
>
> I have been trying to run a macro (Macro1) that calls another macro
> (Macro2) and runs the latter on all the worksheets of an Excel
> workbook. But Macro2 seems to be running on only three worksheets of
> the workbook. I am quite sure about the accuracy of Macro2. There is
> some problem with the code of Macro1 and I do not quite know where the
> problem lies. The code is as follows:
>
> ----------------------------------------------------------------------------------------------------------------------
> Sub Macro1()
> Dim sFile$
> 'Specifying path of the Excel file
> Const path = "E:\TEST\"
> Dim WS_Count As Integer
> WS_Count = ActiveWorkbook.Worksheets.Count
> Dim ws As Worksheet
> Dim I As Integer
> sFile = Dir(path & "*.xls")
> Do While sFile <> ""
> Workbooks.Open (path & sFile)
> Set ws = ActiveSheet
> For I = 1 To WS_Count
> Set ws = ActiveWorkbook.Worksheets(I)
> ws.Activate
> Range("A1").Select
> Application.Run "Macro2"
> Next I
> ActiveWorkbook.Close savechanges:=True
> sFile = Dir
> Exit Do
> Loop
> End Sub
> -----------------------------------------------------------------------------------------------------------------
>
> Please help me out as I need this problem to be sorted out urgently.
>
>
> Thanks,
>
> Sairam
>

Thanks a lot, Norman.

Your solution has worked out perfectly.



Regards,

Sairam

Thanks a lot, Norman.

Your solution has worked out perfectly.



Regards,

Sairam