On Sunday, June 23, 2013 12:36:36 AM UTC-7, Claus Busch wrote:
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

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Now that is clean, & nice!

Thanks Claus.