About Us

John

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Problem with application.Hlookup	Brotherwarren	Excel Discussion (Misc queries)	6	February 23rd 06 08:09 AM
application quit problem	John[_109_]	Excel Programming	3	August 2nd 05 02:54 PM
Application.vlookup problem	David Goodall	Excel Programming	1	September 14th 04 06:00 PM
Application.OnKey problem	Phillip Reeves[_2_]	Excel Programming	1	January 23rd 04 06:46 PM
Application Problem	Stuart[_10_]	Excel Programming	7	November 16th 03 05:44 PM

Menu

About Us