application run problem?

John · 08-12-2005, 11:05 AM

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

Jake Marx · 08-12-2005, 01:05 PM

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

John · 08-12-2005, 02:05 PM

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" wrote:

> 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
>
>

John · 08-12-2005, 03:05 PM

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" wrote:

> 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
>
>

Dave Peterson · 08-12-2005, 09:05 PM

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

John wrote:
>
> 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" wrote:
>
> > 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
> >
> >

--

Dave Peterson

John · 08-15-2005, 10:05 AM

Dave, THANKS that works great!

"Dave Peterson" wrote:

> 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
>
>
> John wrote:
> >
> > 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" wrote:
> >
> > > 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
> > >
> > >
>
> --
>
> Dave Peterson
>