|
Macro resets my calculation to manual
Hi,
For the last 4 or 5 days I was running "all over the place" literally as I had a code which was executing as expected in my Colleagues workplace, but at my computer and home computer it wasnt. Please refer to "Automatic opening of files seeming to have cache" posted 3 or 4 days back. I think I might have finally found the reason to this. ( Please also refer Microsoft.public.excel.misc -- "Excel Manual calculation is stubborn" posted today. When I use to run the code (pls see below) then even if my calculation was set to automatic before the macro was run, excel would change it to manual during the course of its running ( I checked the calculation setting at when the macro had executed) and probably it wasnt reading all the files as expected. Please tell me why this macro is behaving the way it is and also how to rectify the same. Interestingly the same macro when run in my colleagues computer will not tamper with the calculation settings(!!) Regards, Hari India Sub OpenWorkbooksInLocation() Application.ScreenUpdating = True Dim i As Integer Dim p As String Workbooks.Open "C:\Documents and Settings\hprasadh\Desktop\Janice\Project comparison\IEX Format.xls" Windows("IEX Format.xls").Activate Range("A3:F7000").Select Selection.Clear Application.Goto Reference:="R1C1" With Application.FileSearch .NewSearch .LookIn = "C:\CCAPPS\ttlview\TMP\" & Format(Now, "yyyy-mm-dd") .SearchSubFolders = False .FileType = msoFileTypeExcelWorkbooks .Execute For i = 1 To .FoundFiles.Count Set wb = Workbooks.Open(filename:=.FoundFiles(i)) p = .FoundFiles(i) Call TransferIEXExceldata(p) Next i End With Application.ScreenUpdating = True Windows("IEX format").Activate Application.DisplayAlerts = False ActiveWorkbook.SaveAs filename:= _ "C:\Documents and Settings\hprasadh\Desktop\Janice\Project comparison\IEX format " & " " & CStr(Format(Now, "yyyy-mm-dd")), FileFormat:=xlNormal Application.DisplayAlerts = True End Sub Public Function TransferIEXExceldata(ByVal p As String) ' ' TransferIEXExceldata Macro ' Macro recorded 6/9/2004 by Hari Prasadh ' ' Dim q As String Windows("IEX Format.xls").Activate Application.Goto Reference:="R1C1" p = Application.WorksheetFunction.Substitute(p, "c:\ccapps\ttlview\tmp\" & Format(Now, "yyyy-mm-dd") & "\", "") Windows(p).Activate Application.Goto Reference:="R1C1" Selection.Copy Range("A3").Select ActiveSheet.Paste Application.CutCopyMode = False Selection.TextToColumns Destination:=Range("A3"), DataType:=xlDelimited, _ TextQualifier:=xlNone, ConsecutiveDelimiter:=True, Tab:=False, Semicolon _ :=False, Comma:=False, Space:=True, Other:=False, FieldInfo:=Array( _ Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)), TrailingMinusNumbers _ :=True Range("D3").Select Selection.Copy Windows("IEX format.xls").Activate Selection.End(xlDown).Select ActiveCell.Offset(1, 5).Range("A1").Select ActiveSheet.Paste Application.Goto Reference:="R1C1" Windows(p).Activate Application.Goto Reference:="R13C1" Range("A13:E13").Select Range(Selection, Selection.End(xlDown)).Select Application.CutCopyMode = False Selection.Copy Windows("IEX format.xls").Activate Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Range("A1").Select ActiveSheet.Paste Selection.End(xlDown).Select ActiveCell.Offset(0, 5).Range("A1").Select Windows(p).Activate Application.Goto Reference:="R3C4" Application.CutCopyMode = False Selection.Copy Windows("IEX format.xls").Activate ActiveSheet.Paste Range(Selection, Selection.End(xlUp)).Select ActiveSheet.Paste Application.Goto Reference:="R1C1" Windows(p).Activate Rows("3:3").Select Selection.Clear Application.Goto Reference:="R1C1" Workbooks(p).Close SaveChanges:=False Windows("IEX format.xls").Activate End Function |
Macro resets my calculation to manual
If you have a large workbook with many formulas, Excel does sometimes seem
to unilaterally reset the calculation mode to manual. -- Vasant "Hari" wrote in message ... Hi, For the last 4 or 5 days I was running "all over the place" literally as I had a code which was executing as expected in my Colleagues workplace, but at my computer and home computer it wasnt. Please refer to "Automatic opening of files seeming to have cache" posted 3 or 4 days back. I think I might have finally found the reason to this. ( Please also refer Microsoft.public.excel.misc -- "Excel Manual calculation is stubborn" posted today. When I use to run the code (pls see below) then even if my calculation was set to automatic before the macro was run, excel would change it to manual during the course of its running ( I checked the calculation setting at when the macro had executed) and probably it wasnt reading all the files as expected. Please tell me why this macro is behaving the way it is and also how to rectify the same. Interestingly the same macro when run in my colleagues computer will not tamper with the calculation settings(!!) Regards, Hari India Sub OpenWorkbooksInLocation() Application.ScreenUpdating = True Dim i As Integer Dim p As String Workbooks.Open "C:\Documents and Settings\hprasadh\Desktop\Janice\Project comparison\IEX Format.xls" Windows("IEX Format.xls").Activate Range("A3:F7000").Select Selection.Clear Application.Goto Reference:="R1C1" With Application.FileSearch .NewSearch .LookIn = "C:\CCAPPS\ttlview\TMP\" & Format(Now, "yyyy-mm-dd") .SearchSubFolders = False .FileType = msoFileTypeExcelWorkbooks .Execute For i = 1 To .FoundFiles.Count Set wb = Workbooks.Open(filename:=.FoundFiles(i)) p = .FoundFiles(i) Call TransferIEXExceldata(p) Next i End With Application.ScreenUpdating = True Windows("IEX format").Activate Application.DisplayAlerts = False ActiveWorkbook.SaveAs filename:= _ "C:\Documents and Settings\hprasadh\Desktop\Janice\Project comparison\IEX format " & " " & CStr(Format(Now, "yyyy-mm-dd")), FileFormat:=xlNormal Application.DisplayAlerts = True End Sub Public Function TransferIEXExceldata(ByVal p As String) ' ' TransferIEXExceldata Macro ' Macro recorded 6/9/2004 by Hari Prasadh ' ' Dim q As String Windows("IEX Format.xls").Activate Application.Goto Reference:="R1C1" p = Application.WorksheetFunction.Substitute(p, "c:\ccapps\ttlview\tmp\" & Format(Now, "yyyy-mm-dd") & "\", "") Windows(p).Activate Application.Goto Reference:="R1C1" Selection.Copy Range("A3").Select ActiveSheet.Paste Application.CutCopyMode = False Selection.TextToColumns Destination:=Range("A3"), DataType:=xlDelimited, _ TextQualifier:=xlNone, ConsecutiveDelimiter:=True, Tab:=False, Semicolon _ :=False, Comma:=False, Space:=True, Other:=False, FieldInfo:=Array( _ Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)), TrailingMinusNumbers _ :=True Range("D3").Select Selection.Copy Windows("IEX format.xls").Activate Selection.End(xlDown).Select ActiveCell.Offset(1, 5).Range("A1").Select ActiveSheet.Paste Application.Goto Reference:="R1C1" Windows(p).Activate Application.Goto Reference:="R13C1" Range("A13:E13").Select Range(Selection, Selection.End(xlDown)).Select Application.CutCopyMode = False Selection.Copy Windows("IEX format.xls").Activate Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Range("A1").Select ActiveSheet.Paste Selection.End(xlDown).Select ActiveCell.Offset(0, 5).Range("A1").Select Windows(p).Activate Application.Goto Reference:="R3C4" Application.CutCopyMode = False Selection.Copy Windows("IEX format.xls").Activate ActiveSheet.Paste Range(Selection, Selection.End(xlUp)).Select ActiveSheet.Paste Application.Goto Reference:="R1C1" Windows(p).Activate Rows("3:3").Select Selection.Clear Application.Goto Reference:="R1C1" Workbooks(p).Close SaveChanges:=False Windows("IEX format.xls").Activate End Function |
Macro resets my calculation to manual
Hi,
This time I ran the macro step by step and identified the point where the calculation changed from automatic to manual ( by using F8 and going back to excel , tools, options etc after each F8 pressing.) It was just after execution of the statement --Workbooks.Open "C:\Documents and Settings\hprasadh\Desktop\Janice\Project comparison\IEX Format.xls" I changed the setting back to automatic and contiued the macro execution. After that the calculation did not change back automatically in any of the statements. But my old problem still persists. So, I have 2 problems now:- 1. How/Why was my calculation option changing automatically at the execution of this macro 2. Please tell me if there is any way out for me here. Regards, Hari India "Hari" wrote in message ... Hi, For the last 4 or 5 days I was running "all over the place" literally as I had a code which was executing as expected in my Colleagues workplace, but at my computer and home computer it wasnt. Please refer to "Automatic opening of files seeming to have cache" posted 3 or 4 days back. I think I might have finally found the reason to this. ( Please also refer Microsoft.public.excel.misc -- "Excel Manual calculation is stubborn" posted today. When I use to run the code (pls see below) then even if my calculation was set to automatic before the macro was run, excel would change it to manual during the course of its running ( I checked the calculation setting at when the macro had executed) and probably it wasnt reading all the files as expected. Please tell me why this macro is behaving the way it is and also how to rectify the same. Interestingly the same macro when run in my colleagues computer will not tamper with the calculation settings(!!) Regards, Hari India Sub OpenWorkbooksInLocation() Application.ScreenUpdating = True Dim i As Integer Dim p As String Workbooks.Open "C:\Documents and Settings\hprasadh\Desktop\Janice\Project comparison\IEX Format.xls" Windows("IEX Format.xls").Activate Range("A3:F7000").Select Selection.Clear Application.Goto Reference:="R1C1" With Application.FileSearch .NewSearch .LookIn = "C:\CCAPPS\ttlview\TMP\" & Format(Now, "yyyy-mm-dd") .SearchSubFolders = False .FileType = msoFileTypeExcelWorkbooks .Execute For i = 1 To .FoundFiles.Count Set wb = Workbooks.Open(filename:=.FoundFiles(i)) p = .FoundFiles(i) Call TransferIEXExceldata(p) Next i End With Application.ScreenUpdating = True Windows("IEX format").Activate Application.DisplayAlerts = False ActiveWorkbook.SaveAs filename:= _ "C:\Documents and Settings\hprasadh\Desktop\Janice\Project comparison\IEX format " & " " & CStr(Format(Now, "yyyy-mm-dd")), FileFormat:=xlNormal Application.DisplayAlerts = True End Sub Public Function TransferIEXExceldata(ByVal p As String) ' ' TransferIEXExceldata Macro ' Macro recorded 6/9/2004 by Hari Prasadh ' ' Dim q As String Windows("IEX Format.xls").Activate Application.Goto Reference:="R1C1" p = Application.WorksheetFunction.Substitute(p, "c:\ccapps\ttlview\tmp\" & Format(Now, "yyyy-mm-dd") & "\", "") Windows(p).Activate Application.Goto Reference:="R1C1" Selection.Copy Range("A3").Select ActiveSheet.Paste Application.CutCopyMode = False Selection.TextToColumns Destination:=Range("A3"), DataType:=xlDelimited, _ TextQualifier:=xlNone, ConsecutiveDelimiter:=True, Tab:=False, Semicolon _ :=False, Comma:=False, Space:=True, Other:=False, FieldInfo:=Array( _ Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)), TrailingMinusNumbers _ :=True Range("D3").Select Selection.Copy Windows("IEX format.xls").Activate Selection.End(xlDown).Select ActiveCell.Offset(1, 5).Range("A1").Select ActiveSheet.Paste Application.Goto Reference:="R1C1" Windows(p).Activate Application.Goto Reference:="R13C1" Range("A13:E13").Select Range(Selection, Selection.End(xlDown)).Select Application.CutCopyMode = False Selection.Copy Windows("IEX format.xls").Activate Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Range("A1").Select ActiveSheet.Paste Selection.End(xlDown).Select ActiveCell.Offset(0, 5).Range("A1").Select Windows(p).Activate Application.Goto Reference:="R3C4" Application.CutCopyMode = False Selection.Copy Windows("IEX format.xls").Activate ActiveSheet.Paste Range(Selection, Selection.End(xlUp)).Select ActiveSheet.Paste Application.Goto Reference:="R1C1" Windows(p).Activate Rows("3:3").Select Selection.Clear Application.Goto Reference:="R1C1" Workbooks(p).Close SaveChanges:=False Windows("IEX format.xls").Activate End Function |
Macro resets my calculation to manual
Hari,
I believe that you may find the answer to your problem here. Description of how Excel determines the current mode of calculation http://support.microsoft.com/default...b;en-us;214395 HTH Paul -------------------------------------------------------------------------------------------------------------- Be advised to back up your WorkBook before attempting to make changes. -------------------------------------------------------------------------------------------------------------- Hi, When I use to run the code (pls see below) then even if my calculation was set to automatic before the macro was run, excel would change it to manual during the course of its running ( I checked the calculation setting at when the macro had executed) and probably it wasnt reading all the files as expected. Please tell me why this macro is behaving the way it is and also how to rectify the same. Interestingly the same macro when run in my colleagues computer will not tamper with the calculation settings(!!) Regards, Hari India |
Macro resets my calculation to manual
Hari,
This is guess work, Try opening the offending workbook, changing the calculation to automatic and save it. Check that book has any event handlers that make calculation to manual. Cecil "Hari" wrote in message ... Hi, This time I ran the macro step by step and identified the point where the calculation changed from automatic to manual ( by using F8 and going back to excel , tools, options etc after each F8 pressing.) It was just after execution of the statement --Workbooks.Open "C:\Documents and Settings\hprasadh\Desktop\Janice\Project comparison\IEX Format.xls" I changed the setting back to automatic and contiued the macro execution. After that the calculation did not change back automatically in any of the statements. But my old problem still persists. So, I have 2 problems now:- 1. How/Why was my calculation option changing automatically at the execution of this macro 2. Please tell me if there is any way out for me here. Regards, Hari India "Hari" wrote in message ... Hi, For the last 4 or 5 days I was running "all over the place" literally as I had a code which was executing as expected in my Colleagues workplace, but at my computer and home computer it wasnt. Please refer to "Automatic opening of files seeming to have cache" posted 3 or 4 days back. I think I might have finally found the reason to this. ( Please also refer Microsoft.public.excel.misc -- "Excel Manual calculation is stubborn" posted today. When I use to run the code (pls see below) then even if my calculation was set to automatic before the macro was run, excel would change it to manual during the course of its running ( I checked the calculation setting at when the macro had executed) and probably it wasnt reading all the files as expected. Please tell me why this macro is behaving the way it is and also how to rectify the same. Interestingly the same macro when run in my colleagues computer will not tamper with the calculation settings(!!) Regards, Hari India Sub OpenWorkbooksInLocation() Application.ScreenUpdating = True Dim i As Integer Dim p As String Workbooks.Open "C:\Documents and Settings\hprasadh\Desktop\Janice\Project comparison\IEX Format.xls" Windows("IEX Format.xls").Activate Range("A3:F7000").Select Selection.Clear Application.Goto Reference:="R1C1" With Application.FileSearch .NewSearch .LookIn = "C:\CCAPPS\ttlview\TMP\" & Format(Now, "yyyy-mm-dd") .SearchSubFolders = False .FileType = msoFileTypeExcelWorkbooks .Execute For i = 1 To .FoundFiles.Count Set wb = Workbooks.Open(filename:=.FoundFiles(i)) p = .FoundFiles(i) Call TransferIEXExceldata(p) Next i End With Application.ScreenUpdating = True Windows("IEX format").Activate Application.DisplayAlerts = False ActiveWorkbook.SaveAs filename:= _ "C:\Documents and Settings\hprasadh\Desktop\Janice\Project comparison\IEX format " & " " & CStr(Format(Now, "yyyy-mm-dd")), FileFormat:=xlNormal Application.DisplayAlerts = True End Sub Public Function TransferIEXExceldata(ByVal p As String) ' ' TransferIEXExceldata Macro ' Macro recorded 6/9/2004 by Hari Prasadh ' ' Dim q As String Windows("IEX Format.xls").Activate Application.Goto Reference:="R1C1" p = Application.WorksheetFunction.Substitute(p, "c:\ccapps\ttlview\tmp\" & Format(Now, "yyyy-mm-dd") & "\", "") Windows(p).Activate Application.Goto Reference:="R1C1" Selection.Copy Range("A3").Select ActiveSheet.Paste Application.CutCopyMode = False Selection.TextToColumns Destination:=Range("A3"), DataType:=xlDelimited, _ TextQualifier:=xlNone, ConsecutiveDelimiter:=True, Tab:=False, Semicolon _ :=False, Comma:=False, Space:=True, Other:=False, FieldInfo:=Array( _ Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)), TrailingMinusNumbers _ :=True Range("D3").Select Selection.Copy Windows("IEX format.xls").Activate Selection.End(xlDown).Select ActiveCell.Offset(1, 5).Range("A1").Select ActiveSheet.Paste Application.Goto Reference:="R1C1" Windows(p).Activate Application.Goto Reference:="R13C1" Range("A13:E13").Select Range(Selection, Selection.End(xlDown)).Select Application.CutCopyMode = False Selection.Copy Windows("IEX format.xls").Activate Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Range("A1").Select ActiveSheet.Paste Selection.End(xlDown).Select ActiveCell.Offset(0, 5).Range("A1").Select Windows(p).Activate Application.Goto Reference:="R3C4" Application.CutCopyMode = False Selection.Copy Windows("IEX format.xls").Activate ActiveSheet.Paste Range(Selection, Selection.End(xlUp)).Select ActiveSheet.Paste Application.Goto Reference:="R1C1" Windows(p).Activate Rows("3:3").Select Selection.Clear Application.Goto Reference:="R1C1" Workbooks(p).Close SaveChanges:=False Windows("IEX format.xls").Activate End Function |
Macro resets my calculation to manual
You sure? I've never seen that.
But I have seen this: http://support.microsoft.com/?scid=243495 XL: Calculate Message Remains in Status Bar If 65,536 Formula References From that KB: Microsoft Excel will correctly calculate all formulas. However, the "Calculate" message in the status bar indicates that Microsoft Excel can no longer track the formula dependencies and is calculating every formula in the workbook after each change. If you are unsure when the last calculation event took place and you want to be sure your formulas are up to date, simply start a calculation manually. To do this, press CTRL+ALT+F9 and wait until "Calculating Cells: n%" disappears and "Calculate" returns to the status bar. Vasant Nanavati wrote: If you have a large workbook with many formulas, Excel does sometimes seem to unilaterally reset the calculation mode to manual. -- Vasant "Hari" wrote in message ... Hi, For the last 4 or 5 days I was running "all over the place" literally as I had a code which was executing as expected in my Colleagues workplace, but at my computer and home computer it wasnt. Please refer to "Automatic opening of files seeming to have cache" posted 3 or 4 days back. I think I might have finally found the reason to this. ( Please also refer Microsoft.public.excel.misc -- "Excel Manual calculation is stubborn" posted today. When I use to run the code (pls see below) then even if my calculation was set to automatic before the macro was run, excel would change it to manual during the course of its running ( I checked the calculation setting at when the macro had executed) and probably it wasnt reading all the files as expected. Please tell me why this macro is behaving the way it is and also how to rectify the same. Interestingly the same macro when run in my colleagues computer will not tamper with the calculation settings(!!) Regards, Hari India Sub OpenWorkbooksInLocation() Application.ScreenUpdating = True Dim i As Integer Dim p As String Workbooks.Open "C:\Documents and Settings\hprasadh\Desktop\Janice\Project comparison\IEX Format.xls" Windows("IEX Format.xls").Activate Range("A3:F7000").Select Selection.Clear Application.Goto Reference:="R1C1" With Application.FileSearch .NewSearch .LookIn = "C:\CCAPPS\ttlview\TMP\" & Format(Now, "yyyy-mm-dd") .SearchSubFolders = False .FileType = msoFileTypeExcelWorkbooks .Execute For i = 1 To .FoundFiles.Count Set wb = Workbooks.Open(filename:=.FoundFiles(i)) p = .FoundFiles(i) Call TransferIEXExceldata(p) Next i End With Application.ScreenUpdating = True Windows("IEX format").Activate Application.DisplayAlerts = False ActiveWorkbook.SaveAs filename:= _ "C:\Documents and Settings\hprasadh\Desktop\Janice\Project comparison\IEX format " & " " & CStr(Format(Now, "yyyy-mm-dd")), FileFormat:=xlNormal Application.DisplayAlerts = True End Sub Public Function TransferIEXExceldata(ByVal p As String) ' ' TransferIEXExceldata Macro ' Macro recorded 6/9/2004 by Hari Prasadh ' ' Dim q As String Windows("IEX Format.xls").Activate Application.Goto Reference:="R1C1" p = Application.WorksheetFunction.Substitute(p, "c:\ccapps\ttlview\tmp\" & Format(Now, "yyyy-mm-dd") & "\", "") Windows(p).Activate Application.Goto Reference:="R1C1" Selection.Copy Range("A3").Select ActiveSheet.Paste Application.CutCopyMode = False Selection.TextToColumns Destination:=Range("A3"), DataType:=xlDelimited, _ TextQualifier:=xlNone, ConsecutiveDelimiter:=True, Tab:=False, Semicolon _ :=False, Comma:=False, Space:=True, Other:=False, FieldInfo:=Array( _ Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)), TrailingMinusNumbers _ :=True Range("D3").Select Selection.Copy Windows("IEX format.xls").Activate Selection.End(xlDown).Select ActiveCell.Offset(1, 5).Range("A1").Select ActiveSheet.Paste Application.Goto Reference:="R1C1" Windows(p).Activate Application.Goto Reference:="R13C1" Range("A13:E13").Select Range(Selection, Selection.End(xlDown)).Select Application.CutCopyMode = False Selection.Copy Windows("IEX format.xls").Activate Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Range("A1").Select ActiveSheet.Paste Selection.End(xlDown).Select ActiveCell.Offset(0, 5).Range("A1").Select Windows(p).Activate Application.Goto Reference:="R3C4" Application.CutCopyMode = False Selection.Copy Windows("IEX format.xls").Activate ActiveSheet.Paste Range(Selection, Selection.End(xlUp)).Select ActiveSheet.Paste Application.Goto Reference:="R1C1" Windows(p).Activate Rows("3:3").Select Selection.Clear Application.Goto Reference:="R1C1" Workbooks(p).Close SaveChanges:=False Windows("IEX format.xls").Activate End Function -- Dave Peterson |
Macro resets my calculation to manual
That may not have been what I said, but it was probably what I meant! <g
-- Vasant "Dave Peterson" wrote in message ... You sure? I've never seen that. But I have seen this: http://support.microsoft.com/?scid=243495 XL: Calculate Message Remains in Status Bar If 65,536 Formula References From that KB: Microsoft Excel will correctly calculate all formulas. However, the "Calculate" message in the status bar indicates that Microsoft Excel can no longer track the formula dependencies and is calculating every formula in the workbook after each change. If you are unsure when the last calculation event took place and you want to be sure your formulas are up to date, simply start a calculation manually. To do this, press CTRL+ALT+F9 and wait until "Calculating Cells: n%" disappears and "Calculate" returns to the status bar. Vasant Nanavati wrote: If you have a large workbook with many formulas, Excel does sometimes seem to unilaterally reset the calculation mode to manual. -- Vasant "Hari" wrote in message ... Hi, For the last 4 or 5 days I was running "all over the place" literally as I had a code which was executing as expected in my Colleagues workplace, but at my computer and home computer it wasnt. Please refer to "Automatic opening of files seeming to have cache" posted 3 or 4 days back. I think I might have finally found the reason to this. ( Please also refer Microsoft.public.excel.misc -- "Excel Manual calculation is stubborn" posted today. When I use to run the code (pls see below) then even if my calculation was set to automatic before the macro was run, excel would change it to manual during the course of its running ( I checked the calculation setting at when the macro had executed) and probably it wasnt reading all the files as expected. Please tell me why this macro is behaving the way it is and also how to rectify the same. Interestingly the same macro when run in my colleagues computer will not tamper with the calculation settings(!!) Regards, Hari India Sub OpenWorkbooksInLocation() Application.ScreenUpdating = True Dim i As Integer Dim p As String Workbooks.Open "C:\Documents and Settings\hprasadh\Desktop\Janice\Project comparison\IEX Format.xls" Windows("IEX Format.xls").Activate Range("A3:F7000").Select Selection.Clear Application.Goto Reference:="R1C1" With Application.FileSearch .NewSearch .LookIn = "C:\CCAPPS\ttlview\TMP\" & Format(Now, "yyyy-mm-dd") .SearchSubFolders = False .FileType = msoFileTypeExcelWorkbooks .Execute For i = 1 To .FoundFiles.Count Set wb = Workbooks.Open(filename:=.FoundFiles(i)) p = .FoundFiles(i) Call TransferIEXExceldata(p) Next i End With Application.ScreenUpdating = True Windows("IEX format").Activate Application.DisplayAlerts = False ActiveWorkbook.SaveAs filename:= _ "C:\Documents and Settings\hprasadh\Desktop\Janice\Project comparison\IEX format " & " " & CStr(Format(Now, "yyyy-mm-dd")), FileFormat:=xlNormal Application.DisplayAlerts = True End Sub Public Function TransferIEXExceldata(ByVal p As String) ' ' TransferIEXExceldata Macro ' Macro recorded 6/9/2004 by Hari Prasadh ' ' Dim q As String Windows("IEX Format.xls").Activate Application.Goto Reference:="R1C1" p = Application.WorksheetFunction.Substitute(p, "c:\ccapps\ttlview\tmp\" & Format(Now, "yyyy-mm-dd") & "\", "") Windows(p).Activate Application.Goto Reference:="R1C1" Selection.Copy Range("A3").Select ActiveSheet.Paste Application.CutCopyMode = False Selection.TextToColumns Destination:=Range("A3"), DataType:=xlDelimited, _ TextQualifier:=xlNone, ConsecutiveDelimiter:=True, Tab:=False, Semicolon _ :=False, Comma:=False, Space:=True, Other:=False, FieldInfo:=Array( _ Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)), TrailingMinusNumbers _ :=True Range("D3").Select Selection.Copy Windows("IEX format.xls").Activate Selection.End(xlDown).Select ActiveCell.Offset(1, 5).Range("A1").Select ActiveSheet.Paste Application.Goto Reference:="R1C1" Windows(p).Activate Application.Goto Reference:="R13C1" Range("A13:E13").Select Range(Selection, Selection.End(xlDown)).Select Application.CutCopyMode = False Selection.Copy Windows("IEX format.xls").Activate Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Range("A1").Select ActiveSheet.Paste Selection.End(xlDown).Select ActiveCell.Offset(0, 5).Range("A1").Select Windows(p).Activate Application.Goto Reference:="R3C4" Application.CutCopyMode = False Selection.Copy Windows("IEX format.xls").Activate ActiveSheet.Paste Range(Selection, Selection.End(xlUp)).Select ActiveSheet.Paste Application.Goto Reference:="R1C1" Windows(p).Activate Rows("3:3").Select Selection.Clear Application.Goto Reference:="R1C1" Workbooks(p).Close SaveChanges:=False Windows("IEX format.xls").Activate End Function -- Dave Peterson |
Macro resets my calculation to manual
Ahhh. I've suffered/enjoyed the same symptom.
Vasant Nanavati wrote: That may not have been what I said, but it was probably what I meant! <g -- Vasant "Dave Peterson" wrote in message ... You sure? I've never seen that. But I have seen this: http://support.microsoft.com/?scid=243495 XL: Calculate Message Remains in Status Bar If 65,536 Formula References From that KB: Microsoft Excel will correctly calculate all formulas. However, the "Calculate" message in the status bar indicates that Microsoft Excel can no longer track the formula dependencies and is calculating every formula in the workbook after each change. If you are unsure when the last calculation event took place and you want to be sure your formulas are up to date, simply start a calculation manually. To do this, press CTRL+ALT+F9 and wait until "Calculating Cells: n%" disappears and "Calculate" returns to the status bar. Vasant Nanavati wrote: If you have a large workbook with many formulas, Excel does sometimes seem to unilaterally reset the calculation mode to manual. -- Vasant "Hari" wrote in message ... Hi, For the last 4 or 5 days I was running "all over the place" literally as I had a code which was executing as expected in my Colleagues workplace, but at my computer and home computer it wasnt. Please refer to "Automatic opening of files seeming to have cache" posted 3 or 4 days back. I think I might have finally found the reason to this. ( Please also refer Microsoft.public.excel.misc -- "Excel Manual calculation is stubborn" posted today. When I use to run the code (pls see below) then even if my calculation was set to automatic before the macro was run, excel would change it to manual during the course of its running ( I checked the calculation setting at when the macro had executed) and probably it wasnt reading all the files as expected. Please tell me why this macro is behaving the way it is and also how to rectify the same. Interestingly the same macro when run in my colleagues computer will not tamper with the calculation settings(!!) Regards, Hari India Sub OpenWorkbooksInLocation() Application.ScreenUpdating = True Dim i As Integer Dim p As String Workbooks.Open "C:\Documents and Settings\hprasadh\Desktop\Janice\Project comparison\IEX Format.xls" Windows("IEX Format.xls").Activate Range("A3:F7000").Select Selection.Clear Application.Goto Reference:="R1C1" With Application.FileSearch .NewSearch .LookIn = "C:\CCAPPS\ttlview\TMP\" & Format(Now, "yyyy-mm-dd") .SearchSubFolders = False .FileType = msoFileTypeExcelWorkbooks .Execute For i = 1 To .FoundFiles.Count Set wb = Workbooks.Open(filename:=.FoundFiles(i)) p = .FoundFiles(i) Call TransferIEXExceldata(p) Next i End With Application.ScreenUpdating = True Windows("IEX format").Activate Application.DisplayAlerts = False ActiveWorkbook.SaveAs filename:= _ "C:\Documents and Settings\hprasadh\Desktop\Janice\Project comparison\IEX format " & " " & CStr(Format(Now, "yyyy-mm-dd")), FileFormat:=xlNormal Application.DisplayAlerts = True End Sub Public Function TransferIEXExceldata(ByVal p As String) ' ' TransferIEXExceldata Macro ' Macro recorded 6/9/2004 by Hari Prasadh ' ' Dim q As String Windows("IEX Format.xls").Activate Application.Goto Reference:="R1C1" p = Application.WorksheetFunction.Substitute(p, "c:\ccapps\ttlview\tmp\" & Format(Now, "yyyy-mm-dd") & "\", "") Windows(p).Activate Application.Goto Reference:="R1C1" Selection.Copy Range("A3").Select ActiveSheet.Paste Application.CutCopyMode = False Selection.TextToColumns Destination:=Range("A3"), DataType:=xlDelimited, _ TextQualifier:=xlNone, ConsecutiveDelimiter:=True, Tab:=False, Semicolon _ :=False, Comma:=False, Space:=True, Other:=False, FieldInfo:=Array( _ Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)), TrailingMinusNumbers _ :=True Range("D3").Select Selection.Copy Windows("IEX format.xls").Activate Selection.End(xlDown).Select ActiveCell.Offset(1, 5).Range("A1").Select ActiveSheet.Paste Application.Goto Reference:="R1C1" Windows(p).Activate Application.Goto Reference:="R13C1" Range("A13:E13").Select Range(Selection, Selection.End(xlDown)).Select Application.CutCopyMode = False Selection.Copy Windows("IEX format.xls").Activate Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Range("A1").Select ActiveSheet.Paste Selection.End(xlDown).Select ActiveCell.Offset(0, 5).Range("A1").Select Windows(p).Activate Application.Goto Reference:="R3C4" Application.CutCopyMode = False Selection.Copy Windows("IEX format.xls").Activate ActiveSheet.Paste Range(Selection, Selection.End(xlUp)).Select ActiveSheet.Paste Application.Goto Reference:="R1C1" Windows(p).Activate Rows("3:3").Select Selection.Clear Application.Goto Reference:="R1C1" Workbooks(p).Close SaveChanges:=False Windows("IEX format.xls").Activate End Function -- Dave Peterson -- Dave Peterson |
Macro resets my calculation to manual
Hi Cecil,
Problem is specific to my computer. The macro runs well at other workstations. I have tried even inserting statements like "Application.Calculation = xlCalculationAutomatic" but to no avail. I save the personal macro folder with calculation set to automatic still the program does not generate results as expected. Should I reinstall my Excel 2002. Regards, Hari India "Cecilkumara Fernando" <cekufdo@sltnetDOTlk wrote in message ... Hari, This is guess work, Try opening the offending workbook, changing the calculation to automatic and save it. Check that book has any event handlers that make calculation to manual. Cecil "Hari" wrote in message ... Hi, This time I ran the macro step by step and identified the point where the calculation changed from automatic to manual ( by using F8 and going back to excel , tools, options etc after each F8 pressing.) It was just after execution of the statement --Workbooks.Open "C:\Documents and Settings\hprasadh\Desktop\Janice\Project comparison\IEX Format.xls" I changed the setting back to automatic and contiued the macro execution. After that the calculation did not change back automatically in any of the statements. But my old problem still persists. So, I have 2 problems now:- 1. How/Why was my calculation option changing automatically at the execution of this macro 2. Please tell me if there is any way out for me here. Regards, Hari India "Hari" wrote in message ... Hi, For the last 4 or 5 days I was running "all over the place" literally as I had a code which was executing as expected in my Colleagues workplace, but at my computer and home computer it wasnt. Please refer to "Automatic opening of files seeming to have cache" posted 3 or 4 days back. I think I might have finally found the reason to this. ( Please also refer Microsoft.public.excel.misc -- "Excel Manual calculation is stubborn" posted today. When I use to run the code (pls see below) then even if my calculation was set to automatic before the macro was run, excel would change it to manual during the course of its running ( I checked the calculation setting at when the macro had executed) and probably it wasnt reading all the files as expected. Please tell me why this macro is behaving the way it is and also how to rectify the same. Interestingly the same macro when run in my colleagues computer will not tamper with the calculation settings(!!) Regards, Hari India Sub OpenWorkbooksInLocation() Application.ScreenUpdating = True Dim i As Integer Dim p As String Workbooks.Open "C:\Documents and Settings\hprasadh\Desktop\Janice\Project comparison\IEX Format.xls" Windows("IEX Format.xls").Activate Range("A3:F7000").Select Selection.Clear Application.Goto Reference:="R1C1" With Application.FileSearch .NewSearch .LookIn = "C:\CCAPPS\ttlview\TMP\" & Format(Now, "yyyy-mm-dd") .SearchSubFolders = False .FileType = msoFileTypeExcelWorkbooks .Execute For i = 1 To .FoundFiles.Count Set wb = Workbooks.Open(filename:=.FoundFiles(i)) p = .FoundFiles(i) Call TransferIEXExceldata(p) Next i End With Application.ScreenUpdating = True Windows("IEX format").Activate Application.DisplayAlerts = False ActiveWorkbook.SaveAs filename:= _ "C:\Documents and Settings\hprasadh\Desktop\Janice\Project comparison\IEX format " & " " & CStr(Format(Now, "yyyy-mm-dd")), FileFormat:=xlNormal Application.DisplayAlerts = True End Sub Public Function TransferIEXExceldata(ByVal p As String) ' ' TransferIEXExceldata Macro ' Macro recorded 6/9/2004 by Hari Prasadh ' ' Dim q As String Windows("IEX Format.xls").Activate Application.Goto Reference:="R1C1" p = Application.WorksheetFunction.Substitute(p, "c:\ccapps\ttlview\tmp\" & Format(Now, "yyyy-mm-dd") & "\", "") Windows(p).Activate Application.Goto Reference:="R1C1" Selection.Copy Range("A3").Select ActiveSheet.Paste Application.CutCopyMode = False Selection.TextToColumns Destination:=Range("A3"), DataType:=xlDelimited, _ TextQualifier:=xlNone, ConsecutiveDelimiter:=True, Tab:=False, Semicolon _ :=False, Comma:=False, Space:=True, Other:=False, FieldInfo:=Array( _ Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)), TrailingMinusNumbers _ :=True Range("D3").Select Selection.Copy Windows("IEX format.xls").Activate Selection.End(xlDown).Select ActiveCell.Offset(1, 5).Range("A1").Select ActiveSheet.Paste Application.Goto Reference:="R1C1" Windows(p).Activate Application.Goto Reference:="R13C1" Range("A13:E13").Select Range(Selection, Selection.End(xlDown)).Select Application.CutCopyMode = False Selection.Copy Windows("IEX format.xls").Activate Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Range("A1").Select ActiveSheet.Paste Selection.End(xlDown).Select ActiveCell.Offset(0, 5).Range("A1").Select Windows(p).Activate Application.Goto Reference:="R3C4" Application.CutCopyMode = False Selection.Copy Windows("IEX format.xls").Activate ActiveSheet.Paste Range(Selection, Selection.End(xlUp)).Select ActiveSheet.Paste Application.Goto Reference:="R1C1" Windows(p).Activate Rows("3:3").Select Selection.Clear Application.Goto Reference:="R1C1" Workbooks(p).Close SaveChanges:=False Windows("IEX format.xls").Activate End Function |
Macro resets my calculation to manual
Hi Dave, This problem was ( and still does) troubling me like heaven. I grew so desperate with this that I added "Application.Calculation = xlCalculationAutomatic" in literally every other line. But even after such .... measures ".foundfiles()" doesnt display the correct number of files in ".newsearch". There is one active workbook other than the personal macro folder. Im running all this code from the personal macro folder to the active workbook. Regards, Hari India "Dave Peterson" wrote in message ... Where did you put that: Application.Calculation = xlCalculationAutomatic line? Was there an activeworkbook at the time? Hari wrote: Hi Cecil, Problem is specific to my computer. The macro runs well at other workstations. I have tried even inserting statements like "Application.Calculation = xlCalculationAutomatic" but to no avail. I save the personal macro folder with calculation set to automatic still the program does not generate results as expected. Should I reinstall my Excel 2002. Regards, Hari India "Cecilkumara Fernando" <cekufdo@sltnetDOTlk wrote in message ... Hari, This is guess work, Try opening the offending workbook, changing the calculation to automatic and save it. Check that book has any event handlers that make calculation to manual. Cecil "Hari" wrote in message ... Hi, This time I ran the macro step by step and identified the point where the calculation changed from automatic to manual ( by using F8 and going back to excel , tools, options etc after each F8 pressing.) It was just after execution of the statement --Workbooks.Open "C:\Documents and Settings\hprasadh\Desktop\Janice\Project comparison\IEX Format.xls" I changed the setting back to automatic and contiued the macro execution. After that the calculation did not change back automatically in any of the statements. But my old problem still persists. So, I have 2 problems now:- 1. How/Why was my calculation option changing automatically at the execution of this macro 2. Please tell me if there is any way out for me here. Regards, Hari India "Hari" wrote in message ... Hi, For the last 4 or 5 days I was running "all over the place" literally as I had a code which was executing as expected in my Colleagues workplace, but at my computer and home computer it wasnt. Please refer to "Automatic opening of files seeming to have cache" posted 3 or 4 days back. I think I might have finally found the reason to this. ( Please also refer Microsoft.public.excel.misc -- "Excel Manual calculation is stubborn" posted today. When I use to run the code (pls see below) then even if my calculation was set to automatic before the macro was run, excel would change it to manual during the course of its running ( I checked the calculation setting at when the macro had executed) and probably it wasnt reading all the files as expected. Please tell me why this macro is behaving the way it is and also how to rectify the same. Interestingly the same macro when run in my colleagues computer will not tamper with the calculation settings(!!) Regards, Hari India Sub OpenWorkbooksInLocation() Application.ScreenUpdating = True Dim i As Integer Dim p As String Workbooks.Open "C:\Documents and Settings\hprasadh\Desktop\Janice\Project comparison\IEX Format.xls" Windows("IEX Format.xls").Activate Range("A3:F7000").Select Selection.Clear Application.Goto Reference:="R1C1" With Application.FileSearch .NewSearch .LookIn = "C:\CCAPPS\ttlview\TMP\" & Format(Now, "yyyy-mm-dd") .SearchSubFolders = False .FileType = msoFileTypeExcelWorkbooks .Execute For i = 1 To .FoundFiles.Count Set wb = Workbooks.Open(filename:=.FoundFiles(i)) p = .FoundFiles(i) Call TransferIEXExceldata(p) Next i End With Application.ScreenUpdating = True Windows("IEX format").Activate Application.DisplayAlerts = False ActiveWorkbook.SaveAs filename:= _ "C:\Documents and Settings\hprasadh\Desktop\Janice\Project comparison\IEX format " & " " & CStr(Format(Now, "yyyy-mm-dd")), FileFormat:=xlNormal Application.DisplayAlerts = True End Sub Public Function TransferIEXExceldata(ByVal p As String) ' ' TransferIEXExceldata Macro ' Macro recorded 6/9/2004 by Hari Prasadh ' ' Dim q As String Windows("IEX Format.xls").Activate Application.Goto Reference:="R1C1" p = Application.WorksheetFunction.Substitute(p, "c:\ccapps\ttlview\tmp\" & Format(Now, "yyyy-mm-dd") & "\", "") Windows(p).Activate Application.Goto Reference:="R1C1" Selection.Copy Range("A3").Select ActiveSheet.Paste Application.CutCopyMode = False Selection.TextToColumns Destination:=Range("A3"), DataType:=xlDelimited, _ TextQualifier:=xlNone, ConsecutiveDelimiter:=True, Tab:=False, Semicolon _ :=False, Comma:=False, Space:=True, Other:=False, FieldInfo:=Array( _ Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)), TrailingMinusNumbers _ :=True Range("D3").Select Selection.Copy Windows("IEX format.xls").Activate Selection.End(xlDown).Select ActiveCell.Offset(1, 5).Range("A1").Select ActiveSheet.Paste Application.Goto Reference:="R1C1" Windows(p).Activate Application.Goto Reference:="R13C1" Range("A13:E13").Select Range(Selection, Selection.End(xlDown)).Select Application.CutCopyMode = False Selection.Copy Windows("IEX format.xls").Activate Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Range("A1").Select ActiveSheet.Paste Selection.End(xlDown).Select ActiveCell.Offset(0, 5).Range("A1").Select Windows(p).Activate Application.Goto Reference:="R3C4" Application.CutCopyMode = False Selection.Copy Windows("IEX format.xls").Activate ActiveSheet.Paste Range(Selection, Selection.End(xlUp)).Select ActiveSheet.Paste Application.Goto Reference:="R1C1" Windows(p).Activate Rows("3:3").Select Selection.Clear Application.Goto Reference:="R1C1" Workbooks(p).Close SaveChanges:=False Windows("IEX format.xls").Activate End Function -- Dave Peterson |
Macro resets my calculation to manual
I'm not sure why the calculation won't stick. You may want to post a little of
your code and give a short description of the current configuration. You have another reply at your other thread (multiple threads are a pain!). Hari wrote: Hi Dave, This problem was ( and still does) troubling me like heaven. I grew so desperate with this that I added "Application.Calculation = xlCalculationAutomatic" in literally every other line. But even after such .... measures ".foundfiles()" doesnt display the correct number of files in ".newsearch". There is one active workbook other than the personal macro folder. Im running all this code from the personal macro folder to the active workbook. Regards, Hari India "Dave Peterson" wrote in message ... Where did you put that: Application.Calculation = xlCalculationAutomatic line? Was there an activeworkbook at the time? Hari wrote: Hi Cecil, Problem is specific to my computer. The macro runs well at other workstations. I have tried even inserting statements like "Application.Calculation = xlCalculationAutomatic" but to no avail. I save the personal macro folder with calculation set to automatic still the program does not generate results as expected. Should I reinstall my Excel 2002. Regards, Hari India "Cecilkumara Fernando" <cekufdo@sltnetDOTlk wrote in message ... Hari, This is guess work, Try opening the offending workbook, changing the calculation to automatic and save it. Check that book has any event handlers that make calculation to manual. Cecil "Hari" wrote in message ... Hi, This time I ran the macro step by step and identified the point where the calculation changed from automatic to manual ( by using F8 and going back to excel , tools, options etc after each F8 pressing.) It was just after execution of the statement --Workbooks.Open "C:\Documents and Settings\hprasadh\Desktop\Janice\Project comparison\IEX Format.xls" I changed the setting back to automatic and contiued the macro execution. After that the calculation did not change back automatically in any of the statements. But my old problem still persists. So, I have 2 problems now:- 1. How/Why was my calculation option changing automatically at the execution of this macro 2. Please tell me if there is any way out for me here. Regards, Hari India "Hari" wrote in message ... Hi, For the last 4 or 5 days I was running "all over the place" literally as I had a code which was executing as expected in my Colleagues workplace, but at my computer and home computer it wasnt. Please refer to "Automatic opening of files seeming to have cache" posted 3 or 4 days back. I think I might have finally found the reason to this. ( Please also refer Microsoft.public.excel.misc -- "Excel Manual calculation is stubborn" posted today. When I use to run the code (pls see below) then even if my calculation was set to automatic before the macro was run, excel would change it to manual during the course of its running ( I checked the calculation setting at when the macro had executed) and probably it wasnt reading all the files as expected. Please tell me why this macro is behaving the way it is and also how to rectify the same. Interestingly the same macro when run in my colleagues computer will not tamper with the calculation settings(!!) Regards, Hari India Sub OpenWorkbooksInLocation() Application.ScreenUpdating = True Dim i As Integer Dim p As String Workbooks.Open "C:\Documents and Settings\hprasadh\Desktop\Janice\Project comparison\IEX Format.xls" Windows("IEX Format.xls").Activate Range("A3:F7000").Select Selection.Clear Application.Goto Reference:="R1C1" With Application.FileSearch .NewSearch .LookIn = "C:\CCAPPS\ttlview\TMP\" & Format(Now, "yyyy-mm-dd") .SearchSubFolders = False .FileType = msoFileTypeExcelWorkbooks .Execute For i = 1 To .FoundFiles.Count Set wb = Workbooks.Open(filename:=.FoundFiles(i)) p = .FoundFiles(i) Call TransferIEXExceldata(p) Next i End With Application.ScreenUpdating = True Windows("IEX format").Activate Application.DisplayAlerts = False ActiveWorkbook.SaveAs filename:= _ "C:\Documents and Settings\hprasadh\Desktop\Janice\Project comparison\IEX format " & " " & CStr(Format(Now, "yyyy-mm-dd")), FileFormat:=xlNormal Application.DisplayAlerts = True End Sub Public Function TransferIEXExceldata(ByVal p As String) ' ' TransferIEXExceldata Macro ' Macro recorded 6/9/2004 by Hari Prasadh ' ' Dim q As String Windows("IEX Format.xls").Activate Application.Goto Reference:="R1C1" p = Application.WorksheetFunction.Substitute(p, "c:\ccapps\ttlview\tmp\" & Format(Now, "yyyy-mm-dd") & "\", "") Windows(p).Activate Application.Goto Reference:="R1C1" Selection.Copy Range("A3").Select ActiveSheet.Paste Application.CutCopyMode = False Selection.TextToColumns Destination:=Range("A3"), DataType:=xlDelimited, _ TextQualifier:=xlNone, ConsecutiveDelimiter:=True, Tab:=False, Semicolon _ :=False, Comma:=False, Space:=True, Other:=False, FieldInfo:=Array( _ Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)), TrailingMinusNumbers _ :=True Range("D3").Select Selection.Copy Windows("IEX format.xls").Activate Selection.End(xlDown).Select ActiveCell.Offset(1, 5).Range("A1").Select ActiveSheet.Paste Application.Goto Reference:="R1C1" Windows(p).Activate Application.Goto Reference:="R13C1" Range("A13:E13").Select Range(Selection, Selection.End(xlDown)).Select Application.CutCopyMode = False Selection.Copy Windows("IEX format.xls").Activate Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Range("A1").Select ActiveSheet.Paste Selection.End(xlDown).Select ActiveCell.Offset(0, 5).Range("A1").Select Windows(p).Activate Application.Goto Reference:="R3C4" Application.CutCopyMode = False Selection.Copy Windows("IEX format.xls").Activate ActiveSheet.Paste Range(Selection, Selection.End(xlUp)).Select ActiveSheet.Paste Application.Goto Reference:="R1C1" Windows(p).Activate Rows("3:3").Select Selection.Clear Application.Goto Reference:="R1C1" Workbooks(p).Close SaveChanges:=False Windows("IEX format.xls").Activate End Function -- Dave Peterson -- Dave Peterson |
| All times are GMT +1. The time now is 07:57 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com