Cell Ref to identify worksheet

[Mikeice]

Hi There

I need to have a cell (currently) D3 which give a month.

I have twelve worksheets all with the name of each month - Jan - Dec

I have a template and when I hit the command button to save I woul
like to save the information to the worksheet which is equal to th
value in D3. D3 show Jan - dec.

You guys have already helped heaps and I thank you:

Code below:

Private Sub CommandButton1_Click()

Dim Summary As Worksheet
Dim myFromAddr As Variant
Dim myToRow As Variant
Dim iCtr As Long
Dim LastCol As Range
Dim NextColNum As Long

myToRow = Array(2, 3, 4, 5, 6, 7, 8, _
12, 13, 15, 16, 18, 19, _
22, 23, 24, 27, 28, _
31, 32, 33, 34, 35, _
40, 44, 45, 46, 47, 48, 49, 50, _
55, 56, 57, 58, 59, 60, 61, 62)


myFromAddr = Array("B2", "B3", "B4", "B5", "B6", "d2", "e3", _
"d10", "e10", "d17", "e17", "d23", "e23", _
"D36", "D37", "e36", "D42", "E42", _
"D47", "D48", "D49", "D50", "E47", _
"E59", "d63", "D64", "d65", "d66", "d67", "d68", "e63", _
"D73", "D74", "D75", "D76", "d77", "D78", "D79", "E73")

If UBound(myToRow) <> UBound(myFromAddr) Then
MsgBox "Design error--not same number of cells!"
Exit Sub
End If

If IsEmpty(Me.Range(myFromAddr(LBound(myFromAddr)))) Then
MsgBox "Please fill in cell: "
myFromAddr(LBound(myFromAddr))
Exit Sub
End If

Set Summary = Worksheets("Jan")

With Summary
Set LastCol _
= .Cells(myToRow(LBound(myToRow)), .Columns.Count).End(xlToLeft)
If IsEmpty(LastCol) Then
NextColNum = LastCol.Column
Else
NextColNum = LastCol.Column + 1
End If

For iCtr = LBound(myToRow) To UBound(myToRow)
.Cells(myToRow(iCtr), NextColNum).Value _
= Me.Range(myFromAddr(iCtr)).Value
Me.Range(myFromAddr(iCtr)).ClearContents
Next iCtr
End With


End Su

 

[Tom Ogilvy]

I am guessing but perhaps

Set Summary = Worksheets(ActiveSheet.Range("D3").Value)