Index Syntax Error

M

MJSlattery

Can someone please point out what my error is. The message is synta
error!

Sub CAPTURECLOSEP()
' CAPTURECLOSEP Macro
' Macro recorded 11/7/2004 by Michael Slattery
Worksheets( "HIST" ).Activate(INDEX(I1:I300, D5)).Select
ActiveCell.Copy
Worksheets( "HIST" ).Activate(INDEX(J1:J300, D5)).Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
ActiveCell.Paste
Application.CutCopyMode = False
Beep
End Sub

Thanks, Michae
 
D

Dave Peterson

You're kind of mixing worksheet functions into your VBA code. You can do that,
but in this case, you don't need to:

Option Explicit
Sub CAPTURECLOSEP()

Dim myCellToCopy As Range
Dim myCellToPaste As Range

With Worksheets("HIST")
Set myCellToCopy = .Range("I1").Offset(.Range("d5").Value - 1, 0)
Set myCellToPaste = myCellToCopy.Offset(0, 1)

myCellToCopy.Copy
myCellToPaste.PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With
Application.CutCopyMode = False
Beep

End Sub

I read your code as looking down column I to the row number that is in D5. Then
copy it and paste it into the adjacent cell.
 
Top