Case Statement and For next loop

[rex759]

Hello,
My workbook has a tab for each month. When I’m in a specific month, I run the macro below to add a name to the Totals tab. It places the name on the first available row for that months chart. I created name rages for column A for each months chart on the totals tab. Example, Myjan=Worksheets(“Totals”).Range(“A2:A8”)

My goal now is to add the name to the rest of months on the totals tab. For example, if I add a name in June, I want to the macro to continue to add the person to July, Aug, Sept etc… through Dec.

I am not sure what path to take. Do I create a loop to go through the rest of the months incorporating the Case Statements and if so how?

I am going to add a bit of code to check the sheet and see if the name already exists. I haven’t gotten that far yet.

Any help is appreciated.

Book1
	A	B	C	D
1	Jan
2	Name
3	Bill W
4	Rick M
5	Diane D
6
7
8
9
10	Feb
11	Name
12	Bill W
13	Rick M
14	Diane D
15
16
17
18
Totals

 Option Compare Text
Sub AddNamesl()
Dim rng As Range

'startmon =current Month
 Startmon = “February”

‘Empnam =  Employee name to add
empnam = "Phil C"

Select Case Startmon
    Case "January"
         Set Rng = Range("myjan")
    Case "February"
         Set Rng = Range("Myfeb")
    Case "March"
         Set Rng = Range("Mymar")
‘etc…….    
End Select
With Worksheets("Totals")
    Rng.Cells.SpecialCells(xlCellTypeBlanks).Cells(1) = empnam
End With

'Add to rest of the worksheet



End Sub

 

[DanteAmor]

Try this

Sub AddNames()
  Dim nms As Variant, startmon As Long, empnam As String, i As Long
[COLOR=#0000ff]  empnam = "Phil C"[/COLOR]
  nms = Array("myjan", "myfeb", "mymar", "myapr", "mymay", "myjun", _
              "myjul", "myaug", "mysep", "myoct", "mynov", "mydec")
[COLOR=#008000]  startmon = 6  'example june[/COLOR]
  For i = startmon - 1 To UBound(nms)
    Sheets("Totals").Range(nms(i)).Cells.SpecialCells(xlCellTypeBlanks).Cells(1) = empnam
  Next
End Sub

 

[rex759]

Works perfectly, thank you!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.