Per -

Thank you for the tips they will certainly help.

But I am still left with the question of is there a way to write code rather
than copy & paste the INDEX function? Or maybe this, do you know where the
code is stored behind the INDEX function and can I get into it to look at it?

Carrie

Per Jessen wrote:
Hi Carrie

There is two things that wÃ*ll speed up your code.

Set screenUpdating =False at the start of your code ( make sure to set
it true at the end of the macro).

You don't need to select a range before manipulating it.

I.e range("A1:A10").delete is much faster than

Range("A1:A10").select
Selection.Delete

You can also use this aproach to paste the formula. I have changed the
code according to the above in the first part of the code and the part
pasting the formula. The rest is up to you:-)

Private Sub OptionButton1_Click()

Application.ScreenUpdating = False

Sheets("Calendar").Select
Range("Clear_Calendar").Clear
Range("B5:IT7").Delete Shift:=xlUp
Range("A5").Select
Sheets("Dates").Select
Application.Goto "January_2008"
Selection.Copy
Sheets("Calendar").Range("B4").PasteSpecial Paste:=xlPasteAll,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

Sheets("Dates").Select
Application.Goto Reference:="February_08"
Selection.Copy
Sheets("Calendar").Select
ActiveCell.Offset(2, 0).End(xlToRight).Select
ActiveCell.Offset(-2, 1).PasteSpecial Paste:=xlPasteAll,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

Sheets("Dates").Select
Application.Goto Reference:="March_08"
Selection.Copy
Sheets("Calendar").Select
ActiveCell.Offset(2, 0).Select
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Select
ActiveCell.Offset(-2, 0).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True

Sheets("Dates").Select
Application.Goto Reference:="April_08"
Selection.Copy
Sheets("Calendar").Select
ActiveCell.Offset(2, 0).Select
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Select
ActiveCell.Offset(-2, 0).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True

Sheets("Dates").Select
Application.Goto Reference:="May_08"
Selection.Copy
Sheets("Calendar").Select
ActiveCell.Offset(2, 0).Select
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Select
ActiveCell.Offset(-2, 0).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True

Sheets("Dates").Select
Application.Goto Reference:="June_08"
Selection.Copy
Sheets("Calendar").Select
ActiveCell.Offset(2, 0).Select
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Select
ActiveCell.Offset(-2, 0).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True

Sheets("Dates").Select
Application.Goto Reference:="July_08"
Selection.Copy
Sheets("Calendar").Select
ActiveCell.Offset(2, 0).Select
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Select
ActiveCell.Offset(-2, 0).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True

Sheets("Dates").Select
Application.Goto Reference:="August_08"
Selection.Copy
Sheets("Calendar").Select
ActiveCell.Offset(2, 0).Select
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Select
ActiveCell.Offset(-2, 0).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True

Range("B8:IS50").FormulaR1C1 = _
"=IF(ISNA(INDEX(Data,R3C,RC256)),"""",
(INDEX(Data,R3C,RC256)))"
Calculate

Sheets("Dates").Select
Range("A1").Select

Sheets("Calendar").Select
'Range("A3").Select
UserForm1.Hide
Range("B3:IT4").Font.ColorIndex = 2
Range("b8").Select

Call MergeCells
Application.ScreenUpdating = True

End Sub

Regards,

Per

Hi Per-

[quoted text clipped - 22 lines]
--
Message posted via OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/excel-programming/200802/1

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200802/1