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

 

[ck1999]

Try changing this

RawData.Range("A:A").Copy
or
RawData.Range("A:A").Copy Destination:=Sheets(i).Range("A1")

to

sheets("rawdata").range("A:A").copy
and
sheets("RawData").Range("A:A").Copy Destination:=Sheets(i).Range("A1")

ck1999

 

[SkipVought]

Hi,

Try this...

Sub EmptyWorksheets(IndWrkShtArr As Variant)
    For i = [b]LBound(IndWrkShtArr)[/b] To UBound(IndWrkShtArr)
      With Sheets([b]IndWrkShtArr(i)[/b])
        .Cells.ClearContents
        RawData.Range("A:A").Copy Destination:=.Range("A1")
      End With
    Next i
End Sub

use the Sheet Name rather that the INDEX, which may not be correct.

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]

Thanks for the help. The following worked and it didn't activate all the sheets unnecessarily as I was doing. Did make one change as I intended to copy a row not a column:

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