application run problem?

J

John

I have the following code which runs several macros... the macros are to cut
and paste certian values into different sheets... and each macro runs fine on
its own. However, when I run all of them together many of the values that
should be cut and pasted are not... any ideas?

Application.Run "'SNW Sales Credit Summary.xls'!SA"
Application.Run "'SNW Sales Credit Summary.xls'!NI"
Application.Run "'SNW Sales Credit Summary.xls'!DN"
Application.Run "'SNW Sales Credit Summary.xls'!SC"
Application.Run "'SNW Sales Credit Summary.xls'!CP"
Application.Run "'SNW Sales Credit Summary.xls'!ST"
Application.Run "'SNW Sales Credit Summary.xls'!ARS"
Application.Run "'SNW Sales Credit Summary.xls'!DAN_FUT"
Application.Run "'SNW Sales Credit Summary.xls'!BUZ_DN"
Application.Run "'SNW Sales Credit Summary.xls'!TA"
Application.Run "'SNW Sales Credit Summary.xls'!TAT"
Application.Run "'SNW Sales Credit Summary.xls'!DA"
Application.Run "'SNW Sales Credit Summary.xls'!TC"
Application.Run "'SNW Sales Credit Summary.xls'!TM"
Application.Run "'SNW Sales Credit Summary.xls'!TMT"
Application.Run "'SNW Sales Credit Summary.xls'!TCT"
Application.Run "'SNW Sales Credit Summary.xls'!UST"
Range("A1").Select
 
J

Jake Marx

Hi John,

A few things to check:

1) Are any of your subroutines selecting or activating ranges or worksheets?
Do any of them use ActiveCell or ActiveSheet? If so, you may get unexpected
results by running them all in a row like this. Instead of selecting
objects and using relative references, you should try to use fully-qualified
ranges when copying/pasting. For example, instead of this:

Worksheets("Sheet1").Select
Range("A1").Select
ActiveCell.Copy
Worksheets("Sheet2").Select
Range("A1").Select
ActiveCell.Paste

You should do this:

Worksheets("Sheet1").Range("A1").Copy Destination:= _
Worksheets("Sheet2").Range("A1")


2) Maybe you're running into timing issues (not likely, but possible I
suppose). If #1 doesn't hold true, then you could try putting DoEvents
between each Application.Run to see if your problems clear up.


3) If neither #1 or #2 work, try putting a breakpoint on the first line of
code and step through it line by line to see where you might be going wrong.


--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]
 
J

John

Jake, thanks for the response, I will play around with some of those
suggestions... I do have something like your first idea (acitve worksheets
and such).

Jake Marx said:
Hi John,

A few things to check:

1) Are any of your subroutines selecting or activating ranges or worksheets?
Do any of them use ActiveCell or ActiveSheet? If so, you may get unexpected
results by running them all in a row like this. Instead of selecting
objects and using relative references, you should try to use fully-qualified
ranges when copying/pasting. For example, instead of this:

Worksheets("Sheet1").Select
Range("A1").Select
ActiveCell.Copy
Worksheets("Sheet2").Select
Range("A1").Select
ActiveCell.Paste

You should do this:

Worksheets("Sheet1").Range("A1").Copy Destination:= _
Worksheets("Sheet2").Range("A1")


2) Maybe you're running into timing issues (not likely, but possible I
suppose). If #1 doesn't hold true, then you could try putting DoEvents
between each Application.Run to see if your problems clear up.


3) If neither #1 or #2 work, try putting a breakpoint on the first line of
code and step through it line by line to see where you might be going wrong.


--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

I have the following code which runs several macros... the macros are
to cut and paste certian values into different sheets... and each
macro runs fine on its own. However, when I run all of them together
many of the values that should be cut and pasted are not... any ideas?

Application.Run "'SNW Sales Credit Summary.xls'!SA"
Application.Run "'SNW Sales Credit Summary.xls'!NI"
Application.Run "'SNW Sales Credit Summary.xls'!DN"
Application.Run "'SNW Sales Credit Summary.xls'!SC"
Application.Run "'SNW Sales Credit Summary.xls'!CP"
Application.Run "'SNW Sales Credit Summary.xls'!ST"
Application.Run "'SNW Sales Credit Summary.xls'!ARS"
Application.Run "'SNW Sales Credit Summary.xls'!DAN_FUT"
Application.Run "'SNW Sales Credit Summary.xls'!BUZ_DN"
Application.Run "'SNW Sales Credit Summary.xls'!TA"
Application.Run "'SNW Sales Credit Summary.xls'!TAT"
Application.Run "'SNW Sales Credit Summary.xls'!DA"
Application.Run "'SNW Sales Credit Summary.xls'!TC"
Application.Run "'SNW Sales Credit Summary.xls'!TM"
Application.Run "'SNW Sales Credit Summary.xls'!TMT"
Application.Run "'SNW Sales Credit Summary.xls'!TCT"
Application.Run "'SNW Sales Credit Summary.xls'!UST"
Range("A1").Select
Click to expand...
Click to expand...
 
J

John

Jake, or someone else?

here is my underlying code... any idea how to make this so that I can run
the code I posted above or make this code so I can change just what I search
for and repeat the process... meaning in this example I search for "SA" and
paste into SA sheet... could I do that then look for "NI" and paste into NI
sheet?

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Sheets("t0983101").Select
Application.CutCopyMode = False
Range("A4").Select
Dim rngToSearch As Range
Dim wks As Worksheet
Dim rngFound As Range

Set wks = Sheets("t0983101")
Set rngToSearch = wks.Columns(5)

Set rngFound = rngToSearch.Find(what:="SA", LookIn:=xlValues,
lookat:=xlWhole)
If rngFound Is Nothing Then
Sheets("Macro Sheet").Select
End
Else
Do
rngFound.EntireRow.Cut
Sheets("SA").Select
Range("A9").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Sheets("t0983101").Select

Set rngFound = rngToSearch.FindNext
Loop Until rngFound Is Nothing
End If
Sheets("Macro Sheet").Select
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

Jake Marx said:
Hi John,

A few things to check:

1) Are any of your subroutines selecting or activating ranges or worksheets?
Do any of them use ActiveCell or ActiveSheet? If so, you may get unexpected
results by running them all in a row like this. Instead of selecting
objects and using relative references, you should try to use fully-qualified
ranges when copying/pasting. For example, instead of this:

Worksheets("Sheet1").Select
Range("A1").Select
ActiveCell.Copy
Worksheets("Sheet2").Select
Range("A1").Select
ActiveCell.Paste

You should do this:

Worksheets("Sheet1").Range("A1").Copy Destination:= _
Worksheets("Sheet2").Range("A1")


2) Maybe you're running into timing issues (not likely, but possible I
suppose). If #1 doesn't hold true, then you could try putting DoEvents
between each Application.Run to see if your problems clear up.


3) If neither #1 or #2 work, try putting a breakpoint on the first line of
code and step through it line by line to see where you might be going wrong.


--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

I have the following code which runs several macros... the macros are
to cut and paste certian values into different sheets... and each
macro runs fine on its own. However, when I run all of them together
many of the values that should be cut and pasted are not... any ideas?

Application.Run "'SNW Sales Credit Summary.xls'!SA"
Application.Run "'SNW Sales Credit Summary.xls'!NI"
Application.Run "'SNW Sales Credit Summary.xls'!DN"
Application.Run "'SNW Sales Credit Summary.xls'!SC"
Application.Run "'SNW Sales Credit Summary.xls'!CP"
Application.Run "'SNW Sales Credit Summary.xls'!ST"
Application.Run "'SNW Sales Credit Summary.xls'!ARS"
Application.Run "'SNW Sales Credit Summary.xls'!DAN_FUT"
Application.Run "'SNW Sales Credit Summary.xls'!BUZ_DN"
Application.Run "'SNW Sales Credit Summary.xls'!TA"
Application.Run "'SNW Sales Credit Summary.xls'!TAT"
Application.Run "'SNW Sales Credit Summary.xls'!DA"
Application.Run "'SNW Sales Credit Summary.xls'!TC"
Application.Run "'SNW Sales Credit Summary.xls'!TM"
Application.Run "'SNW Sales Credit Summary.xls'!TMT"
Application.Run "'SNW Sales Credit Summary.xls'!TCT"
Application.Run "'SNW Sales Credit Summary.xls'!UST"
Range("A1").Select
Click to expand...
Click to expand...
 
D

Dave Peterson

Instead of duplicating the code, you could loop through all your values:

Option Explicit
Sub testme()
Dim rngToSearch As Range
Dim wks As Worksheet
Dim rngFound As Range
Dim WhatToFind As Variant
Dim iCtr As Long
Dim DestCell As Range

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Set wks = Sheets("t0983101")
Set rngToSearch = wks.Columns(5)

WhatToFind = Array("SA", "NI")

For iCtr = LBound(WhatToFind) To UBound(WhatToFind)
With rngToSearch
Set rngFound = .Cells.Find(what:=WhatToFind(iCtr), _
LookIn:=xlValues, lookat:=xlWhole, _
after:=.Cells(.Cells.Count), _
MatchCase:=False)

If rngFound Is Nothing Then
'do nothing
Else
Do
With Worksheets(WhatToFind(iCtr))
Set DestCell = .Range("a9").End(xlDown).Offset(1, 0)
End With

rngFound.EntireRow.Cut _
Destination:=DestCell

Set rngFound = .FindNext(rngFound)

Loop Until rngFound Is Nothing
End If
End With
Next iCtr
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

You may want to look at how Debra Dalgleish approaches a similar situation:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

and how Ron de Bruin did it with his easyfilter addin:
http://www.rondebruin.nl/easyfilter.htm

Jake, or someone else?

here is my underlying code... any idea how to make this so that I can run
the code I posted above or make this code so I can change just what I search
for and repeat the process... meaning in this example I search for "SA" and
paste into SA sheet... could I do that then look for "NI" and paste into NI
sheet?

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Sheets("t0983101").Select
Application.CutCopyMode = False
Range("A4").Select
Dim rngToSearch As Range
Dim wks As Worksheet
Dim rngFound As Range

Set wks = Sheets("t0983101")
Set rngToSearch = wks.Columns(5)

Set rngFound = rngToSearch.Find(what:="SA", LookIn:=xlValues,
lookat:=xlWhole)
If rngFound Is Nothing Then
Sheets("Macro Sheet").Select
End
Else
Do
rngFound.EntireRow.Cut
Sheets("SA").Select
Range("A9").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Sheets("t0983101").Select

Set rngFound = rngToSearch.FindNext
Loop Until rngFound Is Nothing
End If
Sheets("Macro Sheet").Select
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

Jake Marx said:
Hi John,

A few things to check:

1) Are any of your subroutines selecting or activating ranges or worksheets?
Do any of them use ActiveCell or ActiveSheet? If so, you may get unexpected
results by running them all in a row like this. Instead of selecting
objects and using relative references, you should try to use fully-qualified
ranges when copying/pasting. For example, instead of this:

Worksheets("Sheet1").Select
Range("A1").Select
ActiveCell.Copy
Worksheets("Sheet2").Select
Range("A1").Select
ActiveCell.Paste

You should do this:

Worksheets("Sheet1").Range("A1").Copy Destination:= _
Worksheets("Sheet2").Range("A1")


2) Maybe you're running into timing issues (not likely, but possible I
suppose). If #1 doesn't hold true, then you could try putting DoEvents
between each Application.Run to see if your problems clear up.


3) If neither #1 or #2 work, try putting a breakpoint on the first line of
code and step through it line by line to see where you might be going wrong.


--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

I have the following code which runs several macros... the macros are
to cut and paste certian values into different sheets... and each
macro runs fine on its own. However, when I run all of them together
many of the values that should be cut and pasted are not... any ideas?

Application.Run "'SNW Sales Credit Summary.xls'!SA"
Application.Run "'SNW Sales Credit Summary.xls'!NI"
Application.Run "'SNW Sales Credit Summary.xls'!DN"
Application.Run "'SNW Sales Credit Summary.xls'!SC"
Application.Run "'SNW Sales Credit Summary.xls'!CP"
Application.Run "'SNW Sales Credit Summary.xls'!ST"
Application.Run "'SNW Sales Credit Summary.xls'!ARS"
Application.Run "'SNW Sales Credit Summary.xls'!DAN_FUT"
Application.Run "'SNW Sales Credit Summary.xls'!BUZ_DN"
Application.Run "'SNW Sales Credit Summary.xls'!TA"
Application.Run "'SNW Sales Credit Summary.xls'!TAT"
Application.Run "'SNW Sales Credit Summary.xls'!DA"
Application.Run "'SNW Sales Credit Summary.xls'!TC"
Application.Run "'SNW Sales Credit Summary.xls'!TM"
Application.Run "'SNW Sales Credit Summary.xls'!TMT"
Application.Run "'SNW Sales Credit Summary.xls'!TCT"
Application.Run "'SNW Sales Credit Summary.xls'!UST"
Range("A1").Select
Click to expand...
Click to expand...
Click to expand...
 
J

John

Dave, THANKS that works great!

Dave Peterson said:
Instead of duplicating the code, you could loop through all your values:

Option Explicit
Sub testme()
Dim rngToSearch As Range
Dim wks As Worksheet
Dim rngFound As Range
Dim WhatToFind As Variant
Dim iCtr As Long
Dim DestCell As Range

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Set wks = Sheets("t0983101")
Set rngToSearch = wks.Columns(5)

WhatToFind = Array("SA", "NI")

For iCtr = LBound(WhatToFind) To UBound(WhatToFind)
With rngToSearch
Set rngFound = .Cells.Find(what:=WhatToFind(iCtr), _
LookIn:=xlValues, lookat:=xlWhole, _
after:=.Cells(.Cells.Count), _
MatchCase:=False)

If rngFound Is Nothing Then
'do nothing
Else
Do
With Worksheets(WhatToFind(iCtr))
Set DestCell = .Range("a9").End(xlDown).Offset(1, 0)
End With

rngFound.EntireRow.Cut _
Destination:=DestCell

Set rngFound = .FindNext(rngFound)

Loop Until rngFound Is Nothing
End If
End With
Next iCtr
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

You may want to look at how Debra Dalgleish approaches a similar situation:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

and how Ron de Bruin did it with his easyfilter addin:
http://www.rondebruin.nl/easyfilter.htm

Jake, or someone else?

here is my underlying code... any idea how to make this so that I can run
the code I posted above or make this code so I can change just what I search
for and repeat the process... meaning in this example I search for "SA" and
paste into SA sheet... could I do that then look for "NI" and paste into NI
sheet?

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Sheets("t0983101").Select
Application.CutCopyMode = False
Range("A4").Select
Dim rngToSearch As Range
Dim wks As Worksheet
Dim rngFound As Range

Set wks = Sheets("t0983101")
Set rngToSearch = wks.Columns(5)

Set rngFound = rngToSearch.Find(what:="SA", LookIn:=xlValues,
lookat:=xlWhole)
If rngFound Is Nothing Then
Sheets("Macro Sheet").Select
End
Else
Do
rngFound.EntireRow.Cut
Sheets("SA").Select
Range("A9").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Sheets("t0983101").Select

Set rngFound = rngToSearch.FindNext
Loop Until rngFound Is Nothing
End If
Sheets("Macro Sheet").Select
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

Jake Marx said:
Hi John,

A few things to check:

1) Are any of your subroutines selecting or activating ranges or worksheets?
Do any of them use ActiveCell or ActiveSheet? If so, you may get unexpected
results by running them all in a row like this. Instead of selecting
objects and using relative references, you should try to use fully-qualified
ranges when copying/pasting. For example, instead of this:

Worksheets("Sheet1").Select
Range("A1").Select
ActiveCell.Copy
Worksheets("Sheet2").Select
Range("A1").Select
ActiveCell.Paste

You should do this:

Worksheets("Sheet1").Range("A1").Copy Destination:= _
Worksheets("Sheet2").Range("A1")


2) Maybe you're running into timing issues (not likely, but possible I
suppose). If #1 doesn't hold true, then you could try putting DoEvents
between each Application.Run to see if your problems clear up.


3) If neither #1 or #2 work, try putting a breakpoint on the first line of
code and step through it line by line to see where you might be going wrong.


--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


John wrote:
I have the following code which runs several macros... the macros are
to cut and paste certian values into different sheets... and each
macro runs fine on its own. However, when I run all of them together
many of the values that should be cut and pasted are not... any ideas?

Application.Run "'SNW Sales Credit Summary.xls'!SA"
Application.Run "'SNW Sales Credit Summary.xls'!NI"
Application.Run "'SNW Sales Credit Summary.xls'!DN"
Application.Run "'SNW Sales Credit Summary.xls'!SC"
Application.Run "'SNW Sales Credit Summary.xls'!CP"
Application.Run "'SNW Sales Credit Summary.xls'!ST"
Application.Run "'SNW Sales Credit Summary.xls'!ARS"
Application.Run "'SNW Sales Credit Summary.xls'!DAN_FUT"
Application.Run "'SNW Sales Credit Summary.xls'!BUZ_DN"
Application.Run "'SNW Sales Credit Summary.xls'!TA"
Application.Run "'SNW Sales Credit Summary.xls'!TAT"
Application.Run "'SNW Sales Credit Summary.xls'!DA"
Application.Run "'SNW Sales Credit Summary.xls'!TC"
Application.Run "'SNW Sales Credit Summary.xls'!TM"
Application.Run "'SNW Sales Credit Summary.xls'!TMT"
Application.Run "'SNW Sales Credit Summary.xls'!TCT"
Application.Run "'SNW Sales Credit Summary.xls'!UST"
Range("A1").Select
Click to expand...
Click to expand...
Click to expand...
 
Top