Just posting this again as desperate to have this work.


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

Just want to say you guys are great and thanks for all you help thu
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.Colum

--
Mikeic
-----------------------------------------------------------------------
Mikeice's Profile: http://www.excelforum.com/member.php...fo&userid=2246
View this thread: http://www.excelforum.com/showthread.php?threadid=37797

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?


--
Mikeice
------------------------------------------------------------------------
Mikeice's Profile: http://www.excelforum.com/member.php...o&userid=22467
View this thread: http://www.excelforum.com/showthread...hreadid=377971

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

--
mangesh_yada
-----------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...fo&userid=1047
View this thread: http://www.excelforum.com/showthread.php?threadid=37797

I presume that sheet "Jan" exists.

Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=377971

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


--
Mikeice
------------------------------------------------------------------------
Mikeice's Profile: http://www.excelforum.com/member.php...o&userid=22467
View this thread: http://www.excelforum.com/showthread...hreadid=377971

Just before I start going through your code, did you try my earlier
suggestion:

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

Does it work..?


Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=377971

Hi Mangesh

Yes tried both your suggestions and both give error 9


--
Mikeice
------------------------------------------------------------------------
Mikeice's Profile: http://www.excelforum.com/member.php...o&userid=22467
View this thread: http://www.excelforum.com/showthread...hreadid=377971

Hi,

I tried your code. Is it complete. The following line:

Me.Range(myFromAddr(iCtr)).ClearContents

clears the cells the first time, and the code runs without any problem.
But when I run it second time, since the cells which hold Jan, Feb are
empty, i get the run-time error... subscript out of range.

So probably the above line is the culprit. Is it intended.

Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=377971

HI Mangesh

Yes the code is required to clear the data in the
Me.Range(myFromAddr(iCtr)).Value on worksheet Quality Scorecard

That myfrom addr does need to be cleared.


--
Mikeice
------------------------------------------------------------------------
Mikeice's Profile: http://www.excelforum.com/member.php...o&userid=22467
View this thread: http://www.excelforum.com/showthread...hreadid=377971

Hi Mikeice,

I don't know what you have currently in your worksheet, and so I can't
simulate your case here. When I run your code at my end, with values in
Jan and Feb in cells B2 and B3, it works the first time (by work I mean
it does not throw any error). At this point it also clears the cell B2
and B3 which hold the values Jan and Feb.

The second time I run the macro, the cells B2 and B3 are empty, and so
the line
Set Summary = Worksheets("" & Range("b3") & "")
generates an error, as there Range("B3") is blank, and there is no
sheet with a blank name.

What is it that you expect when you run the code the second time.

Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=377971

HI

I need a blank worksheet (AS I am using it as a form)

So that I can fill it out again.

I fill out the sheet Quality Scorecard then hit command button at the
bottom.

I copy first part into array and save into the b3 worksheet Jan - Dec
then clear cells that are stated in the array on the originating sheet
Quality Scorecard.


--
Mikeice
------------------------------------------------------------------------
Mikeice's Profile: http://www.excelforum.com/member.php...o&userid=22467
View this thread: http://www.excelforum.com/showthread...hreadid=377971

Maybe I could have a look at your sheet. you could mail it to me at
. remove no spam. Before sending the
sheet, save it at the stage just before you would click the button
which generates the error. So when I open the sheet and press the
concerned button, I get the error you mention.

Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=377971

Add some diagnostic code like 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 rng = Range("B3")
msgbox rng.Address(external:=True) & " contains the value " & vbNewline & _
"--" & rng.Text & "<--"

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



--
Regards,
Tom Ogilvy

"Mikeice" wrote in
message ...

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


--
Mikeice
------------------------------------------------------------------------
Mikeice's Profile:
http://www.excelforum.com/member.php...o&userid=22467
View this thread: http://www.excelforum.com/showthread...hreadid=377971

Hi Tom,

The problem was that the cell contained a date which was formatted a
mmm. And this was being used to find the sheet which was non-existent.

Manges

--
mangesh_yada
-----------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...fo&userid=1047
View this thread: http://www.excelforum.com/showthread.php?threadid=37797

Guess he should have used the Text property instead of the Value property.

Thanks for the feedback.

--
Regards,
Tom Ogilvy

"mangesh_yadav"
wrote in message
news:mangesh_yadav.1qew2e_1118408713.9158@excelfor um-nospam.com...

Hi Tom,

The problem was that the cell contained a date which was formatted as
mmm. And this was being used to find the sheet which was non-existent.

Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile:
http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=377971