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

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

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

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

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

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

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

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