shellp
Board Regular
- Joined
- Jul 7, 2010
- Messages
- 207
- Office Version
- 365
- 2021
- 2010
- 2007
- Platform
- Windows
Hello
I have an Excel macro that takes data from one worksheet that is formatted so one row is one abstract and makes a single worksheet for each abstract. The abstract is based on a template that is used as the "master" for all abstracts. All is working very well as is. But now I've added an option group of 3 buttons for age group: <45, 46 to 65 and 65+. I need the option box that is copied to the new worksheet to be based on another field, age, that is already in the abstract.
I'm not sure how to do this and just as importantly I don't know where to add the piece of code to do this in the current program. Below is the program I have now that is working minus setting the option box values:
Any and all assistance greatly appreciated.
I have an Excel macro that takes data from one worksheet that is formatted so one row is one abstract and makes a single worksheet for each abstract. The abstract is based on a template that is used as the "master" for all abstracts. All is working very well as is. But now I've added an option group of 3 buttons for age group: <45, 46 to 65 and 65+. I need the option box that is copied to the new worksheet to be based on another field, age, that is already in the abstract.
I'm not sure how to do this and just as importantly I don't know where to add the piece of code to do this in the current program. Below is the program I have now that is working minus setting the option box values:
Code:
Sub AbstractData()
Dim r As Range, wsAdd As Worksheet, t As Range, rSEQ_NO As Range, s As Range, myPassword As String, ws As Worksheet, hims
Application.EnableEvents = False
With Sheets("Raw")
Set rSEQ_NO = .Rows(1).Find("MRN")
If Not rSEQ_NO Is Nothing Then
For Each r In .Range(.[A2], .[A2].End(xlDown))
Sheets("Template").Copy After:=Sheets(Sheets.Count)
Set wsAdd = ActiveSheet
Set targcell = wsAdd.Cells(33, 6)
wsAdd.Name = .Cells(r.Row, rSEQ_NO.Column).Value
For Each t In [From]
.Range(.Cells(r.Row, t.Value), .Cells(r.Row, t.Offset(0, 1).Value)).Copy
wsAdd.Range(t.Offset(0, 2).Value).PasteSpecial _
Paste:=xlPasteAll, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
Next t
wsAdd.Range("C7:C20,C22,C24:C28:C30:C32").HorizontalAlignment = xlLeft
wsAdd.Range("C7:C20,C22,C24:C28:C30:C32").VerticalAlignment = xlTop
wsAdd.Range("C21,C23,C29").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
.EntireRow.AutoFit
End With
Next
End If
End With
Application.EnableEvents = True
End Sub
Any and all assistance greatly appreciated.