Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combine Worksheets: Works Well in Test Mode, Locks when running no
When stepping thru in test mode, the macro works fine. When activated with Shortcut key, the macro stops at line indicated by . I can't figure out why. I'm sure there has to be a much better way to combine up to 11 ws, but I don't seem to find what I need in all of Ron deBruin's samples. Am I overlooking the one magic sample? Sub Combine_M2M_Extracts() ActiveWorkbook.SaveAs Filename:="C:\DATA\Master.xls", FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False Application.ScreenUpdating = False ' Application.EnableEvents = False ==================================== ' Open/Activate # 1 File and Select Used Range Workbooks.Open Filename:="C:\DATA\1.xls" Windows("1.xls").Activate Rows("1:1").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy ' Activate Destination File, and Insert Rows Windows("Master.xls").Activate Rows("1:1").Select Selection.Insert Shift:=xlDown Windows("1.xls").Activate ' Activate #1 File and Close Windows("1.xls").Activate ActiveWindow.Close ======================================= In between ========== basically repeats up to 10 times |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combine Worksheets: Works Well in Test Mode, Locks when running no
As expected, your code ran fine for me.
-- Regards, Tom Ogilvy "BEEJAY" wrote: When stepping thru in test mode, the macro works fine. When activated with Shortcut key, the macro stops at line indicated by . I can't figure out why. I'm sure there has to be a much better way to combine up to 11 ws, but I don't seem to find what I need in all of Ron deBruin's samples. Am I overlooking the one magic sample? Sub Combine_M2M_Extracts() ActiveWorkbook.SaveAs Filename:="C:\DATA\Master.xls", FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False Application.ScreenUpdating = False ' Application.EnableEvents = False ==================================== ' Open/Activate # 1 File and Select Used Range Workbooks.Open Filename:="C:\DATA\1.xls" Windows("1.xls").Activate Rows("1:1").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy ' Activate Destination File, and Insert Rows Windows("Master.xls").Activate Rows("1:1").Select Selection.Insert Shift:=xlDown Windows("1.xls").Activate ' Activate #1 File and Close Windows("1.xls").Activate ActiveWindow.Close ======================================= In between ========== basically repeats up to 10 times |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combine Worksheets: Works Well in Test Mode, Locks when runnin
Tom:
I just finished trying to run my code on 3 other machines. Each one "locks up" at the same spot. Have you any idea where I could look for the problem? "Tom Ogilvy" wrote: As expected, your code ran fine for me. -- Regards, Tom Ogilvy "BEEJAY" wrote: When stepping thru in test mode, the macro works fine. When activated with Shortcut key, the macro stops at line indicated by . I can't figure out why. I'm sure there has to be a much better way to combine up to 11 ws, but I don't seem to find what I need in all of Ron deBruin's samples. Am I overlooking the one magic sample? Sub Combine_M2M_Extracts() ActiveWorkbook.SaveAs Filename:="C:\DATA\Master.xls", FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False Application.ScreenUpdating = False ' Application.EnableEvents = False ==================================== ' Open/Activate # 1 File and Select Used Range Workbooks.Open Filename:="C:\DATA\1.xls" Windows("1.xls").Activate Rows("1:1").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy ' Activate Destination File, and Insert Rows Windows("Master.xls").Activate Rows("1:1").Select Selection.Insert Shift:=xlDown Windows("1.xls").Activate ' Activate #1 File and Close Windows("1.xls").Activate ActiveWindow.Close ======================================= In between ========== basically repeats up to 10 times |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combine Worksheets: Works Well in Test Mode, Locks when runnin
Tom:
Further to above. I tried simplifying the process, as shown below. When I "step thru" it, it works fine. When I "run it" it also "Locks Up" at the same spot as before. I added a Range A10 to try to narrow down the problem area. When Stepping thru, again it worked fine. When "running", it would NOT go to Range A10, but the active cell stayed as A1. Therefore, I must somehow be "locking" at/on the "Activate" Line. OTHER: It just dawned on me that the original file we are working on was created as a Excel5.0/95Workbook, then Saved As MS Excel Workbook(*.xls). I just tried doing a Save As with MS Excel97-Excel2003 & 5.0/95 Workbook(*.xls) to see if that would help. No Difference. Is there something else in this line that I could/should be looking at. Sub Create_MASTER_File() ' Open Template: M2M-Master.xlt ' Save As: MASTER.XLS Workbooks.Add Template:= _ "C:\Documents and Settings\jfs\Application Data\Microsoft\Templates\M2M-Master.xlt" ChDir "C:\DATA" ActiveWorkbook.SaveAs Filename:="C:\DATA\MASTER.XLS", FileFormat:=xlNormal _ , Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False ' Insert Heading from file called "1.xls" ' Open/Activate # 1 File and Select Used Range ' Start at Row # 1, which is the Header Row Workbooks.Open Filename:="C:\DATA\1.xls" Windows("1.xls").Activate Range("A10:A10").Select 'New line for testing purposes Rows("1:1").Select Selection.Copy ' Activate Destination File, and Insert Rows Windows("Master.xls").Activate Rows("1:1").Select Selection.Insert Shift:=xlDown Windows("1.xls").Activate ' Activate #1 File and Close Windows("1.xls").Activate Application.DisplayAlerts = False ActiveWindow.Close Application.DisplayAlerts = True End Sub Tom: BeeJay Wrote: I just finished trying to run my code on 3 other machines. Each one "locks up" at the same spot. Have you any idea where I could look for the problem? "Tom Ogilvy" wrote: As expected, your code ran fine for me. -- Regards, Tom Ogilvy "BEEJAY" wrote: When stepping thru in test mode, the macro works fine. When activated with Shortcut key, the macro stops at line indicated by . I can't figure out why. I'm sure there has to be a much better way to combine up to 11 ws, but I don't seem to find what I need in all of Ron deBruin's samples. Am I overlooking the one magic sample? Sub Combine_M2M_Extracts() ActiveWorkbook.SaveAs Filename:="C:\DATA\Master.xls", FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False Application.ScreenUpdating = False ' Application.EnableEvents = False ==================================== ' Open/Activate # 1 File and Select Used Range Workbooks.Open Filename:="C:\DATA\1.xls" Windows("1.xls").Activate Rows("1:1").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy ' Activate Destination File, and Insert Rows Windows("Master.xls").Activate Rows("1:1").Select Selection.Insert Shift:=xlDown Windows("1.xls").Activate ' Activate #1 File and Close Windows("1.xls").Activate ActiveWindow.Close ======================================= In between ========== basically repeats up to 10 times |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combine Worksheets: Works Well in Test Mode, Locks when runnin
Tried Something else - as Follows.
It works great when "stepping thru" (F8) It TOO "locks up" (when "running") when 1.xls is opened. Tried several other spread-sheets from totally different sources, (renaming them to work in the macro) Everyone comes up the same. Any ideas? anyone? I'm Desperate!! "BEEJAY" wrote: Tom: Further to above. I tried simplifying the process, as shown below. When I "step thru" it, it works fine. When I "run it" it also "Locks Up" at the same spot as before. I added a Range A10 to try to narrow down the problem area. When Stepping thru, again it worked fine. When "running", it would NOT go to Range A10, but the active cell stayed as A1. Therefore, I must somehow be "locking" at/on the "Activate" Line. OTHER: It just dawned on me that the original file we are working on was created as a Excel5.0/95Workbook, then Saved As MS Excel Workbook(*.xls). I just tried doing a Save As with MS Excel97-Excel2003 & 5.0/95 Workbook(*.xls) to see if that would help. No Difference. Is there something else in this line that I could/should be looking at. Sub Create_MASTER_File() ' Open Template: M2M-Master.xlt ' Save As: MASTER.XLS Workbooks.Add Template:= _ "C:\Documents and Settings\jfs\Application Data\Microsoft\Templates\M2M-Master.xlt" ChDir "C:\DATA" ActiveWorkbook.SaveAs Filename:="C:\DATA\MASTER.XLS", FileFormat:=xlNormal _ , Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False ' Insert Heading from file called "1.xls" ' Open/Activate # 1 File and Select Used Range ' Start at Row # 1, which is the Header Row Workbooks.Open Filename:="C:\DATA\1.xls" Windows("1.xls").Activate Range("A10:A10").Select 'New line for testing purposes Rows("1:1").Select Selection.Copy ' Activate Destination File, and Insert Rows Windows("Master.xls").Activate Rows("1:1").Select Selection.Insert Shift:=xlDown Windows("1.xls").Activate ' Activate #1 File and Close Windows("1.xls").Activate Application.DisplayAlerts = False ActiveWindow.Close Application.DisplayAlerts = True End Sub Tom: BeeJay Wrote: I just finished trying to run my code on 3 other machines. Each one "locks up" at the same spot. Have you any idea where I could look for the problem? "Tom Ogilvy" wrote: As expected, your code ran fine for me. -- Regards, Tom Ogilvy "BEEJAY" wrote: When stepping thru in test mode, the macro works fine. When activated with Shortcut key, the macro stops at line indicated by . I can't figure out why. I'm sure there has to be a much better way to combine up to 11 ws, but I don't seem to find what I need in all of Ron deBruin's samples. Am I overlooking the one magic sample? Sub Combine_M2M_Extracts() ActiveWorkbook.SaveAs Filename:="C:\DATA\Master.xls", FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False Application.ScreenUpdating = False ' Application.EnableEvents = False ==================================== ' Open/Activate # 1 File and Select Used Range Workbooks.Open Filename:="C:\DATA\1.xls" Windows("1.xls").Activate Rows("1:1").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy ' Activate Destination File, and Insert Rows Windows("Master.xls").Activate Rows("1:1").Select Selection.Insert Shift:=xlDown Windows("1.xls").Activate ' Activate #1 File and Close Windows("1.xls").Activate ActiveWindow.Close ======================================= In between ========== basically repeats up to 10 times |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
installed sp1 and now only compatability mode works | Excel Discussion (Misc queries) | |||
VB Works in test mode, but Stops when doing normal run. | Excel Programming | |||
Macro Works but not in Debug Step mode | Excel Programming | |||
Excel 2000 Code works except in debug mode | Excel Programming | |||
calc locks up after running a macro that moves sheets to a new fil | Excel Discussion (Misc queries) |