[Howard]

Trying to do the obvious, run this code on the four sheets named in the case.
Sheet names are correct, and code works just fine in another sub run on one sheet.

Code is in sheet 1 module.
Tried ThisWorkbook and a standard module also... no go.

Thanks.
Howard

Option Explicit

Sub MyAURangeValuesAllSheets()
Dim ws As Worksheet
Dim c As Range

For Each ws In ActiveWorkbook.Worksheets
Select Case ws.Name
Case "Sheet1", "Sheet2", "Sheet3", "Sheet4"

For Each c In Range("AU1:AU10")

If c = "W" Then
c.Offset(0, 16).Resize(1, 13).Copy
Cells(Rows.Count, "M").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
End If

If c = "P" Then
c.Offset(0, 16).Resize(1, 13).Copy
Cells(Rows.Count, "AA").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
End If

Next
End Select
Next ws
End Sub

[Harald Staff[_8_]]

Hi

You are looping sheets, but all your work is repeatedly done in Sheet1, or,
with a standard module it's active sheet. AU1:AU10 in which sheet? Cells in
which sheet?

For Each c In WS.Range("AU1:AU10")

WS.Cells(Rows.Count, "M").

HTH. Best wishes Harald


"Howard" skrev i melding
...
Trying to do the obvious, run this code on the four sheets named in the
case.
Sheet names are correct, and code works just fine in another sub run on
one sheet.

Code is in sheet 1 module.
Tried ThisWorkbook and a standard module also... no go.

Thanks.
Howard

Option Explicit

Sub MyAURangeValuesAllSheets()
Dim ws As Worksheet
Dim c As Range

For Each ws In ActiveWorkbook.Worksheets
Select Case ws.Name
Case "Sheet1", "Sheet2", "Sheet3", "Sheet4"

For Each c In Range("AU1:AU10")

If c = "W" Then
c.Offset(0, 16).Resize(1, 13).Copy
Cells(Rows.Count, "M").End(xlUp).Offset(1, 0).PasteSpecial
Paste:=xlPasteValues
End If

If c = "P" Then
c.Offset(0, 16).Resize(1, 13).Copy
Cells(Rows.Count, "AA").End(xlUp).Offset(1, 0).PasteSpecial
Paste:=xlPasteValues
End If

Next
End Select
Next ws
End Sub

[Claus Busch]

Hi Howard,

Am Tue, 15 Oct 2013 09:45:09 -0700 (PDT) schrieb Howard:

Trying to do the obvious, run this code on the four sheets named in the case.
Sheet names are correct, and code works just fine in another sub run on one sheet.

Code is in sheet 1 module.
Tried ThisWorkbook and a standard module also... no go.

Code in a standard module and change ActiveSheet to the sheet name where
you want have the output:

Sub Test()
Dim arrSh As Variant
Dim i As Integer
Dim rngC As Range
Dim varOut As Variant

Application.ScreenUpdating = False

arrSh = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4")
For i = LBound(arrSh) To UBound(arrSh)
With Sheets(arrSh(i))
For Each rngC In .Range("AU1:AU10")
If rngC = "W" Then
varOut = rngC.Offset(0, 16).Resize(1, 13)
ActiveSheet.Cells(Rows.Count, "M").End(xlUp) _
.Offset(1, 0).Resize(1, 13) = varOut
ElseIf rngC = "P" Then
varOut = rngC.Offset(0, 16).Resize(1, 13)
ActiveSheet.Cells(Rows.Count, "AA").End(xlUp) _
.Offset(1, 0).Resize(1, 13) = varOut
End If
Next
End With
Next
Application.ScreenUpdating = True

End Sub


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

[Howard]

On Tuesday, October 15, 2013 10:24:51 AM UTC-7, Harald Staff wrote:
Hi



You are looping sheets, but all your work is repeatedly done in Sheet1, or,

with a standard module it's active sheet. AU1:AU10 in which sheet? Cells in

which sheet?



For Each c In WS.Range("AU1:AU10")



WS.Cells(Rows.Count, "M").



HTH. Best wishes Harald


Thanks, Harald, made those changes and in a standard module, works fine.

Regards,
Howard

[Howard]

On Tuesday, October 15, 2013 10:49:19 AM UTC-7, Claus Busch wrote:
Hi Howard,



Am Tue, 15 Oct 2013 09:45:09 -0700 (PDT) schrieb Howard:



Trying to do the obvious, run this code on the four sheets named in the case.

Sheet names are correct, and code works just fine in another sub run on one sheet.



Code is in sheet 1 module.

Tried ThisWorkbook and a standard module also... no go.



Code in a standard module and change ActiveSheet to the sheet name where

you want have the output:



Sub Test()

Dim arrSh As Variant

Dim i As Integer

Dim rngC As Range

Dim varOut As Variant



Application.ScreenUpdating = False



arrSh = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4")

For i = LBound(arrSh) To UBound(arrSh)

With Sheets(arrSh(i))

For Each rngC In .Range("AU1:AU10")

If rngC = "W" Then

varOut = rngC.Offset(0, 16).Resize(1, 13)

ActiveSheet.Cells(Rows.Count, "M").End(xlUp) _

.Offset(1, 0).Resize(1, 13) = varOut

ElseIf rngC = "P" Then

varOut = rngC.Offset(0, 16).Resize(1, 13)

ActiveSheet.Cells(Rows.Count, "AA").End(xlUp) _

.Offset(1, 0).Resize(1, 13) = varOut

End If

Next

End With

Next

Application.ScreenUpdating = True



End Sub





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

The code works, but puts ALL returns on Sheet 1.

I don't understand what to change Activesheet to?

Howard

[Howard]

The code works, but puts ALL returns on Sheet 1.

I don't understand what to change Activesheet to?

Howard


Talking about Claus' code here.

H

[Claus Busch]

Hi Howard,

Am Tue, 15 Oct 2013 11:57:21 -0700 (PDT) schrieb Howard:

Talking about Claus' code here.

change ActiveSheet to the wished sheet for the returns


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

[Claus Busch]

Hi Howard,

Am Tue, 15 Oct 2013 21:00:46 +0200 schrieb Claus Busch:

change ActiveSheet to the wished sheet for the returns

if the output always should be on same sheet as the data, then delete
ActiveSheet:

If rngC = "W" Then
varOut = rngC.Offset(0, 16).Resize(1, 13)
.Cells(Rows.Count, "M").End(xlUp) _
.Offset(1, 0).Resize(1, 13) = varOut


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

[Howard]

On Tuesday, October 15, 2013 12:57:04 PM UTC-7, Claus Busch wrote:
Hi Howard,



Am Tue, 15 Oct 2013 21:00:46 +0200 schrieb Claus Busch:



change ActiveSheet to the wished sheet for the returns



if the output always should be on same sheet as the data, then delete

ActiveSheet:



If rngC = "W" Then

varOut = rngC.Offset(0, 16).Resize(1, 13)

.Cells(Rows.Count, "M").End(xlUp) _

.Offset(1, 0).Resize(1, 13) = varOut





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

I changed Activesheet to this and it works. Is this an accepted practice?
If I add more sheets to the array then they are also taken care of at this output line of code.

Sheets(arrSh(i)).Cells(Rows.Count, "M").End(xlUp) _
.Offset(1, 0).Resize(1, 13) = varOut

Howard





Howard

[Claus Busch]

Hi Howard,

Am Tue, 15 Oct 2013 13:16:03 -0700 (PDT) schrieb Howard:

Sheets(arrSh(i)).Cells(Rows.Count, "M").End(xlUp) _
.Offset(1, 0).Resize(1, 13) = varOut

at the beginning of the code is the line with
With Sheets(ArrSh(i))
so the
..Cells(Rows.Count, "M").End(xlUp) _
.Offset(1, 0).Resize(1, 13) = varOut
is enough.

Another suggestions with Select Case:
Sub Test2()
Dim arrSh As Variant
Dim i As Integer
Dim rngC As Range
Dim varOut As Variant
Dim myTarget As Range

Application.ScreenUpdating = False

arrSh = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4")
For i = LBound(arrSh) To UBound(arrSh)
With Sheets(arrSh(i))
For Each rngC In .Range("AU1:AU10")
Select Case rngC.Value
Case "W"
varOut = rngC.Offset(0, 16).Resize(1, 13)
Set myTarget = .Cells(Rows.Count, "M") _
.End(xlUp).Offset(1, 0)
Case "P"
varOut = rngC.Offset(0, 16).Resize(1, 13)
Set myTarget = .Cells(Rows.Count, "AA") _
.End(xlUp).Offset(1, 0)
End Select
myTarget.Resize(1, 13) = varOut
Next
End With
Next
Application.ScreenUpdating = True

End Sub


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

[Howard]

On Tuesday, October 15, 2013 1:22:32 PM UTC-7, Claus Busch wrote:
Hi Howard,



Am Tue, 15 Oct 2013 13:16:03 -0700 (PDT) schrieb Howard:



Sheets(arrSh(i)).Cells(Rows.Count, "M").End(xlUp) _

.Offset(1, 0).Resize(1, 13) = varOut



at the beginning of the code is the line with

With Sheets(ArrSh(i))

so the

.Cells(Rows.Count, "M").End(xlUp) _

.Offset(1, 0).Resize(1, 13) = varOut

is enough.



Another suggestions with Select Case:

Sub Test2()

Dim arrSh As Variant

Dim i As Integer

Dim rngC As Range

Dim varOut As Variant

Dim myTarget As Range



Application.ScreenUpdating = False



arrSh = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4")

For i = LBound(arrSh) To UBound(arrSh)

With Sheets(arrSh(i))

For Each rngC In .Range("AU1:AU10")

Select Case rngC.Value

Case "W"

varOut = rngC.Offset(0, 16).Resize(1, 13)

Set myTarget = .Cells(Rows.Count, "M") _

.End(xlUp).Offset(1, 0)

Case "P"

varOut = rngC.Offset(0, 16).Resize(1, 13)

Set myTarget = .Cells(Rows.Count, "AA") _

.End(xlUp).Offset(1, 0)

End Select

myTarget.Resize(1, 13) = varOut

Next

End With

Next

Application.ScreenUpdating = True



End Sub





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2



Got a bunch of workable codes!! Thanks, Claus and Harald.

Appreciate it.

Regards,
Howard