Clean this select case code up a bit.

[Howard]

This works okay as is, but me thinks it could be a bit "cleaner".

How could I replace aCol, bCol & cCol with a single xCol that would pertain to whatever case is selected and resize to the last column of data in the row for that selection?

As you can see I am referring to B10:B12 (could be more later) and have to do a - 2 because of the offset and the column is B. (I guess I could ignore the - 2and copy empty cells to the other sheets but that seems clunky)

Thanks,
Howard

Sub A_B_C_Sheet()
Dim jName As String
Dim aCol As Long, bCol As Long, cCol As Long

jName = Range("J20")

With ActiveSheet
aCol = .Cells(10, .Columns.Count).End(xlToLeft).Column - 2

bCol = .Cells(11, .Columns.Count).End(xlToLeft).Column - 2

cCol = .Cells(12, .Columns.Count).End(xlToLeft).Column - 2

Select Case jName

Case "alpha"
Sheets("Sheet1").Range("B10").Offset(0, 1).Resize(1, aCol).Copy _
Sheets("alpha").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)

Case "bravo"
Sheets("Sheet1").Range("B11").Offset(0, 1).Resize(1, bCol).Copy _
Sheets("bravo").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)

Case "charlie"
Sheets("Sheet1").Range("B12").Offset(0, 1).Resize(1, cCol).Copy _
Sheets("charlie").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)

Case Else
MsgBox "NaDa Good jName"

End Select

End With
End Sub

 

[Claus Busch]

Hi Howard,

Am Sat, 22 Jun 2013 23:04:51 -0700 (PDT) schrieb Howard:

This works okay as is, but me thinks it could be a bit "cleaner".

How could I replace aCol, bCol & cCol with a single xCol that would pertain to whatever case is selected and resize to the last column of data in the row for that selection?

As you can see I am referring to B10:B12 (could be more later) and have to do a - 2 because of the offset and the column is B. (I guess I could ignore the - 2and copy empty cells to the other sheets but that seems clunky)

try:

Sub A_B_C_Sheet()
Dim jName As String
Dim StRow As Integer
Dim myCol As Long

With Sheets("Sheet1")
jName = .Range("J20")

Select Case jName
Case "alpha"
StRow = 10
Case "bravo"
StRow = 11
Case "charlie"
StRow = 12
Case Else
MsgBox "NaDa Good jName"
End Select
myCol = .Cells(StRow, .Columns.Count).End(xlToLeft).Column - 2
.Range("B" & StRow).Offset(0, 1).Resize(1, myCol).Copy _
Sheets(jName).Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
End With
End Sub


Regards
Claus Busch

 

[Howard]

Hi Howard,



Am Sat, 22 Jun 2013 23:04:51 -0700 (PDT) schrieb Howard:






try:



Sub A_B_C_Sheet()

Dim jName As String

Dim StRow As Integer

Dim myCol As Long



With Sheets("Sheet1")

jName = .Range("J20")



Select Case jName

Case "alpha"

StRow = 10

Case "bravo"

StRow = 11

Case "charlie"

StRow = 12

Case Else

MsgBox "NaDa Good jName"

End Select

myCol = .Cells(StRow, .Columns.Count).End(xlToLeft).Column - 2

.Range("B" & StRow).Offset(0, 1).Resize(1, myCol).Copy _

Sheets(jName).Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)

End With

End Sub





Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Now that is clean, & nice!

Thanks Claus.