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

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

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

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

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