Select Case Code does not run...

  • Thread starter Thread starter Howard
  • Start date Start date
H

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
 
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
 
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.
Click to expand...

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.
 
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
Click to expand...


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:








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
Click to expand...

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

I don't understand what to change Activesheet to?

Howard
 
Hi Howard,

Am Tue, 15 Oct 2013 11:57:21 -0700 (PDT) schrieb Howard:
Talking about Claus' code here.
Click to expand...

change ActiveSheet to the wished sheet for the returns


Regards
Claus B.
 
Hi Howard,

Am Tue, 15 Oct 2013 21:00:46 +0200 schrieb Claus Busch:
change ActiveSheet to the wished sheet for the returns
Click to expand...

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.
 
Hi Howard,



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






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
Click to expand...

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
 
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
Click to expand...

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.
 
Hi Howard,



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







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
Click to expand...



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

Appreciate it.

Regards,
Howard
 
Back
Top