Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Ref to identify worksheet
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 -- Mikeic ----------------------------------------------------------------------- Mikeice's Profile: http://www.excelforum.com/member.php...fo&userid=2246 View this thread: http://www.excelforum.com/showthread.php?threadid=37767 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Ref to identify worksheet
I am guessing but perhaps
Set Summary = Worksheets(ActiveSheet.Range("D3").Value) -- Regards, Tom Ogilvy "Mikeice" wrote in message ... 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 would like to save the information to the worksheet which is equal to the 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 Sub -- Mikeice ------------------------------------------------------------------------ Mikeice's Profile: http://www.excelforum.com/member.php...o&userid=22467 View this thread: http://www.excelforum.com/showthread...hreadid=377678 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Function to Identify the Modification of a Worksheet | Excel Worksheet Functions | |||
Need to identify active worksheet in macro | Excel Worksheet Functions | |||
How to uniquely identify worksheet | Excel Programming | |||
Repost: How do you identify a worksheet as last? | Excel Programming | |||
How do you identify if a worksheet is in the last position in a Macro? | Excel Programming |