| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Propogate information based on cell contents
Hello
I have a worksheet named "List" that contains data (text and numbers) in column A. I'd like a macro to create a new tab with the name equivalent to the value of cell B6 on the worksheet "List" and based on a worksheet called "Template". The data in column A of "List" needs to be added to the new worksheet, transposing the value of cell A1 on List to cell B3 on the new worksheet and then continued across row 3. So A1=B3, A2=C3, A3=D3 etc. Thanks for the help! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Propogate information based on cell contents
Go to the LIST tab and right click to VIEW CODE.
Paste this code and let me know if it's what you need. Private Sub Worksheet_Change(ByVal Target As Range) Dim aWS As Worksheet Dim myWS As Worksheet Set aWS = Target.Parent If Not Intersect(Target, aWS.Range("B6")) Is Nothing Then Set myWS = Nothing On Error Resume Next Set myWS = Worksheets(Range("B6").Value) On Error GoTo 0 If myWS Is Nothing Then Set myWS = Worksheets.Add myWS.Name = Range("B6").Value End If lrow = aWS.Cells(Rows.Count, 1).End(xlUp).Row Debug.Print lrow For i = 1 To lrow myWS.Cells(2, 2 + i).Value = aWS.Cells(i, 1).Value Next i End If End Sub "Mr. Matt" wrote: Hello I have a worksheet named "List" that contains data (text and numbers) in column A. I'd like a macro to create a new tab with the name equivalent to the value of cell B6 on the worksheet "List" and based on a worksheet called "Template". The data in column A of "List" needs to be added to the new worksheet, transposing the value of cell A1 on List to cell B3 on the new worksheet and then continued across row 3. So A1=B3, A2=C3, A3=D3 etc. Thanks for the help! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Propogate information based on cell contents
Can this be incorporated into a button?
"Barb Reinhardt" wrote: Go to the LIST tab and right click to VIEW CODE. Paste this code and let me know if it's what you need. Private Sub Worksheet_Change(ByVal Target As Range) Dim aWS As Worksheet Dim myWS As Worksheet Set aWS = Target.Parent If Not Intersect(Target, aWS.Range("B6")) Is Nothing Then Set myWS = Nothing On Error Resume Next Set myWS = Worksheets(Range("B6").Value) On Error GoTo 0 If myWS Is Nothing Then Set myWS = Worksheets.Add myWS.Name = Range("B6").Value End If lrow = aWS.Cells(Rows.Count, 1).End(xlUp).Row Debug.Print lrow For i = 1 To lrow myWS.Cells(2, 2 + i).Value = aWS.Cells(i, 1).Value Next i End If End Sub "Mr. Matt" wrote: Hello I have a worksheet named "List" that contains data (text and numbers) in column A. I'd like a macro to create a new tab with the name equivalent to the value of cell B6 on the worksheet "List" and based on a worksheet called "Template". The data in column A of "List" needs to be added to the new worksheet, transposing the value of cell A1 on List to cell B3 on the new worksheet and then continued across row 3. So A1=B3, A2=C3, A3=D3 etc. Thanks for the help! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Propogate information based on cell contents
I'm sorry, I should have told you that it executes when B6 is changed.
"Mr. Matt" wrote: Can this be incorporated into a button? "Barb Reinhardt" wrote: Go to the LIST tab and right click to VIEW CODE. Paste this code and let me know if it's what you need. Private Sub Worksheet_Change(ByVal Target As Range) Dim aWS As Worksheet Dim myWS As Worksheet Set aWS = Target.Parent If Not Intersect(Target, aWS.Range("B6")) Is Nothing Then Set myWS = Nothing On Error Resume Next Set myWS = Worksheets(Range("B6").Value) On Error GoTo 0 If myWS Is Nothing Then Set myWS = Worksheets.Add myWS.Name = Range("B6").Value End If lrow = aWS.Cells(Rows.Count, 1).End(xlUp).Row Debug.Print lrow For i = 1 To lrow myWS.Cells(2, 2 + i).Value = aWS.Cells(i, 1).Value Next i End If End Sub "Mr. Matt" wrote: Hello I have a worksheet named "List" that contains data (text and numbers) in column A. I'd like a macro to create a new tab with the name equivalent to the value of cell B6 on the worksheet "List" and based on a worksheet called "Template". The data in column A of "List" needs to be added to the new worksheet, transposing the value of cell A1 on List to cell B3 on the new worksheet and then continued across row 3. So A1=B3, A2=C3, A3=D3 etc. Thanks for the help! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Propogate information based on cell contents
It's likely that B6 won't change. If the new tab could be created when a
button is clicked (crated from the control toolbar) that would be perfect!! Thanks "Barb Reinhardt" wrote: I'm sorry, I should have told you that it executes when B6 is changed. "Mr. Matt" wrote: Can this be incorporated into a button? "Barb Reinhardt" wrote: Go to the LIST tab and right click to VIEW CODE. Paste this code and let me know if it's what you need. Private Sub Worksheet_Change(ByVal Target As Range) Dim aWS As Worksheet Dim myWS As Worksheet Set aWS = Target.Parent If Not Intersect(Target, aWS.Range("B6")) Is Nothing Then Set myWS = Nothing On Error Resume Next Set myWS = Worksheets(Range("B6").Value) On Error GoTo 0 If myWS Is Nothing Then Set myWS = Worksheets.Add myWS.Name = Range("B6").Value End If lrow = aWS.Cells(Rows.Count, 1).End(xlUp).Row Debug.Print lrow For i = 1 To lrow myWS.Cells(2, 2 + i).Value = aWS.Cells(i, 1).Value Next i End If End Sub "Mr. Matt" wrote: Hello I have a worksheet named "List" that contains data (text and numbers) in column A. I'd like a macro to create a new tab with the name equivalent to the value of cell B6 on the worksheet "List" and based on a worksheet called "Template". The data in column A of "List" needs to be added to the new worksheet, transposing the value of cell A1 on List to cell B3 on the new worksheet and then continued across row 3. So A1=B3, A2=C3, A3=D3 etc. Thanks for the help! |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Propogate information based on cell contents
Use this for the control bar code.
Sub Test() Dim aWS As Worksheet Dim myWS As Worksheet Set aWS = ActiveSheet If Not IsEmpty(aWS.Range("B6")) Then Set myWS = Nothing On Error Resume Next Set myWS = Worksheets(Range("B6").Value) On Error GoTo 0 If myWS Is Nothing Then Set myWS = Worksheets.Add myWS.Name = aWS.Range("B6").Value End If lrow = aWS.Cells(Rows.Count, 1).End(xlUp).Row Debug.Print lrow For i = 1 To lrow myWS.Cells(2, 2 + i).Value = aWS.Cells(i, 1).Value Next i End If "Mr. Matt" wrote: It's likely that B6 won't change. If the new tab could be created when a button is clicked (crated from the control toolbar) that would be perfect!! Thanks "Barb Reinhardt" wrote: I'm sorry, I should have told you that it executes when B6 is changed. "Mr. Matt" wrote: Can this be incorporated into a button? "Barb Reinhardt" wrote: Go to the LIST tab and right click to VIEW CODE. Paste this code and let me know if it's what you need. Private Sub Worksheet_Change(ByVal Target As Range) Dim aWS As Worksheet Dim myWS As Worksheet Set aWS = Target.Parent If Not Intersect(Target, aWS.Range("B6")) Is Nothing Then Set myWS = Nothing On Error Resume Next Set myWS = Worksheets(Range("B6").Value) On Error GoTo 0 If myWS Is Nothing Then Set myWS = Worksheets.Add myWS.Name = Range("B6").Value End If lrow = aWS.Cells(Rows.Count, 1).End(xlUp).Row Debug.Print lrow For i = 1 To lrow myWS.Cells(2, 2 + i).Value = aWS.Cells(i, 1).Value Next i End If End Sub "Mr. Matt" wrote: Hello I have a worksheet named "List" that contains data (text and numbers) in column A. I'd like a macro to create a new tab with the name equivalent to the value of cell B6 on the worksheet "List" and based on a worksheet called "Template". The data in column A of "List" needs to be added to the new worksheet, transposing the value of cell A1 on List to cell B3 on the new worksheet and then continued across row 3. So A1=B3, A2=C3, A3=D3 etc. Thanks for the help! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I Propogate Cell Selection Pattern? | Excel Worksheet Functions | |||
| looking up cell value based off of row and column information | Excel Discussion (Misc queries) | |||
| Transfer single cell information to specific cell based on user criteria | Excel Programming | |||
| Auto-fill cell based on adjacent cell information.. | Excel Worksheet Functions | |||
| HOW DO I HAVE A CELL DISPLAY INFORMATION BASED ON 2 OTHER CELLS | Excel Worksheet Functions | |||
Linear Mode
