Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem in updating all worksheets of a workbook using a macro that calls another macro
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 wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem in updating all worksheets of a workbook using a macro that calls another macro
Thanks a lot, Norman.
Your solution has worked out perfectly. Regards, Sairam |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem in updating all worksheets of a workbook using a macro that calls another macro
Thanks a lot, Norman.
Your solution has worked out perfectly. Regards, Sairam |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro for updating a new template for a workbook | Excel Discussion (Misc queries) | |||
Updating copy of Macro workbook... | Excel Discussion (Misc queries) | |||
Macro Formula Updating Problem | Excel Programming | |||
one macro calls the other | Excel Programming | |||
Macro Calls | Excel Programming |