Cell Reference is Worksheet Name in VB Code

Just posting this again as desperate to have this work.


I need the cell B3 which displays month to refer to the worksheet name in vbcode.

Just want to say you guys are great and thanks for all you help thus far.
So the cell B3 needs to populate below where it says ("Jan")


Set Summary = Worksheets("Jan")

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

Set Summary = Worksheets(Range("B3"))

or

Set Summary = Worksheets(worksheets("Sheet_name").Range("B3"))


Mangesh

Thx for the reply
I have tried both of your possible solutions and both give an error 9 Subscript out of range and that line is highlighted:

Summary = Worksheets(Range("B3"))

Any ideas?

Set Summary = Worksheets("" & Range("b3") & "")

I presume that sheet "Jan" exists.

Mangesh

Sheets Jan Feb - Dec all exist.

I have forwarded the whole command for you to look at. THx for the help.

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("" & Range("b3") & "")


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