First, I'm not sure if I'd do this. This is one heck of a lot of optionbuttons
and groupboxes. When I was testing the code, I broke out of it after a couple
of minutes and a single sheet wasn't done!
But if you want to try (save your work before you start the macro!):
Option Explicit
Sub testme01()
Dim GrpBox As GroupBox
Dim OptBtn As OptionButton
Dim wks As Worksheet
Dim myCell As Range
Dim myRng As Range
Dim mCtr As Long
Dim cCtr As Long
Dim myMonthStr As String
For mCtr = 1 To 1 '12 when you're done testing!
myMonthStr = Format(DateSerial(2008, mCtr, 1), "mmm") & "det"
Set wks = Nothing
On Error Resume Next
Set wks = Worksheets(myMonthStr)
On Error GoTo 0
If wks Is Nothing Then
Set wks = Worksheets.Add
wks.Name = myMonthStr
End If
With wks
'nice for testing
.OptionButtons.Delete
.GroupBoxes.Delete
Set myRng = .Range("c6:c20")
For Each myCell In myRng.Cells
For cCtr = 1 To 100 '100 columns total
If cCtr Mod 4 = 0 Then
'in the fourth column of the group, so skip it
Else
With myCell.Offset(0, cCtr - 1)
Set GrpBox = .Parent.GroupBoxes.Add(Top:=.Top, _
Left:=.Left, _
Width:=.Width, _
Height:=.Height)
GrpBox.Caption = ""
GrpBox.Visible = False
Set OptBtn = .Parent.OptionButtons.Add(Top:=.Top, _
Left:=.Left, _
Width:=.Width / 2, _
Height:=.Height)
OptBtn.Caption = ""
OptBtn.LinkedCell = .Address(external:=True)
Set OptBtn = .Parent.OptionButtons.Add(Top:=.Top, _
Left:=.Left + (.Width / 2), _
Width:=.Width / 2, _
Height:=.Height)
OptBtn.Caption = ""
.NumberFormat = ";;;"
End With
End If
Next cCtr
Next myCell
End With
Next mCtr
End Sub
Jerry wrote:
>
> I have a spreadsheet where I need to copy multiple cells every other column
> and applicable to other worksheets. Under Set wks = Worksheets("mardet")
> I need to put 12 worksheet names (febdet, mardet, aprdet) for the 12 months
> of the year. Under Set myRng = .Range("c6:e20,g6:i20") I need to run this
> loop 25 times for as to read 3 columns(c thru e), skip one column (f) and do
> 3 columns (g - i) and skip a column (j) and so on. The code that I use for a
> worksheet is below. Your asistance is greatly appreciated
>
> Option Explicit
> Sub testme01()
>
> Dim GrpBox As GroupBox
> Dim OptBtn As OptionButton
> Dim wks As Worksheet
> Dim myCell As Range
> Dim myRng As Range
>
> Set wks = Worksheets("mardet")
>
> With wks
> 'nice for testing
> .OptionButtons.Delete
> .GroupBoxes.Delete
>
> Set myRng = .Range("c6:e20,g6:i20")
> For Each myCell In myRng.Cells
> With myCell
> Set GrpBox = .Parent.GroupBoxes.Add(Top:=.Top, _
> Left:=.Left, _
> Width:=.Width, _
> Height:=.Height)
> GrpBox.Caption = ""
> GrpBox.Visible = False
>
> Set OptBtn = .Parent.OptionButtons.Add(Top:=.Top, _
> Left:=.Left, _
> Width:=.Width / 2, _
> Height:=.Height)
> OptBtn.Caption = ""
> OptBtn.LinkedCell = .Address(external:=True)
>
> Set OptBtn = .Parent.OptionButtons.Add(Top:=.Top, _
> Left:=.Left + (.Width / 2), _
> Width:=.Width / 2, _
> Height:=.Height)
> OptBtn.Caption = ""
>
> .NumberFormat = ";;;"
>
> End With
> Next myCell
> End With
> End Sub
> Sub compliance()
>
> End Sub
--
Dave Peterson
|
Similar Threads
