Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Case Code does not run...
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Case Code does not run...
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Case Code does not run...
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Case Code does not run...
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Case Code does not run...
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Case Code does not run...
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Case Code does not run...
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Case Code does not run...
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Case Code does not run...
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Case Code does not run...
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Case Code does not run...
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Select Case code error | Excel Programming | |||
Clean this select case code up a bit. | Excel Programming | |||
Code not working, copy in Select Case section not copying over. | Excel Programming | |||
Case without Select Case error problem | Excel Discussion (Misc queries) | |||
Simplify Code - Select Case | Excel Programming |