Excel VBA Copy Paste Question

[baycolor]

Hi, I'm really new to Excel VBA programming and have the question below. I'm using Excel 2003 on a laptop.

I have the following procedure that is passed an array of worksheet names:

Sub EmptyWorksheets(IndWrkShtArr As Variant)
For i = 1 To UBound(IndWrkShtArr)
Sheets(i).Select
Cells.Select
Selection.ClearContents
Range("A1").Select
RawData.Range("A:A").Copy Destination:=Sheets(i).Range("A1")
Next i
End Sub

Seventh line is all on one line in the Excel editor window with the end looking like...
:=RawData.Range("A2:A3")

RawData is a tab in my workbook.

Each time the code works on a sheet it selects all cells, clears them puts the focus on cell A1 and then attempts to copy the firs row of header data from my raw data tab to the active sheet. The mesage I get is:

Run-time error '424':
Object required

When I click debug the editor highlights line seven of the procedure.

I copied this copy/paste example from a site that said it is more efficient then copy paste via the clipboard (something like):
RawData.Range("A:A").Copy
Sheets(i).Range("A1").paste

I've attempted to change Sheets(i) to IndWrkShtArr(i)

Also attempted to copy and paste into the same worksheet with
RawData.Range("A:A").Copy Destination:=RawData.Range("A2")

I'm not all that familiar with the Excel/Office VBA object model and I don't have time to learn it all right now. Any help is appreciated.

Thanks

 

[SkipVought]

Please post VBA, code, macro questions in Forum707.

Skip,
[sub]
I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent![/sub]

 

[baycolor]

Sorry thanks!

 

[baycolor]

OK not sure what gives I posted them in the VBA forum and they don't show up. I'm wondering if something on the site is preventing a duplicate post

 

[SkipVought]

Try it again. Use a different title. Go slow and be sure you crossed your i's and ditted your t's.


Skip,
[sub]
I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent![/sub]