Copying Worksheets in Excel via Access?

[DaddyNackers]

I am trying to copy a worksheet in an excel workbook and paste it within the same workbook using VBA.

For example, I want to copy "Sheet 1" before "Sheet 8" using VBA code. Does anyone know how to do this? I am using MS Access 97.

The following code is where I am at right now (it doesn't work though)???

wBook.Worksheets("CC5_Graph&quot.Copy (Sheets(i + 5))

This code gives me the following error:
"Method 'Sheets' of object '_Global' failed" err.num = '1004'

I appreciate any and all help!
Thanks,
Nackers

 

[RobBroekhuis]

Try:

wBook.Worksheets("CC5_Graph&quot.Copy after:=wBook.sheets(i+5)

Rob

 

[DaddyNackers]

When I do this:

wBook.Worksheets("CC5_Graph&quot.Copy before:=wBook.sheets(6)

it gives me the following error:
"Subscript out of range" err.num = "9"

The funny thing is that wBook.Sheets.Count = 10.

I tried the following code also and got the same error:

wBook.Worksheets("CC5_Graph&quot.Copy before:=wBook.Worksheets("CC1_Data&quot

Any suggestions????

Thanks,
Nackers

 

[RobBroekhuis]

It looks as if the sheet you're trying to copy is a chart sheet. In that case, it will not appear in the worksheets collection. Try

wBook.sheets("CC5_Graph&quot.Copy before:=wBook.sheets(6)

instead.

Rob