Prev: Find a Item & Copy the Data
Next: Apply ADDRESS formula in VB
From: the_tiger_grrr on 13 Apr 2010 09:06
G'day all you clever people, hope someone can help me

I am using the code:

ActiveSheet.Copy after:=Workbook("Example.xls").Sheets(i)

to copy a dynamic selection of sheets from one workbook to another

The code works just fine until the 18th sheet is copied across, then I get a
subscript out of range error message

This is really frustrating as I know the code works, but I am limited in how
often I can use it, and I want to copy across 50+ sheets.

Has anyone come across this issue before and does anyone know a good way to
solve it?

PS, I ran the same code in Excel2007 and managed 45 repititions before I get
the same subscript out of range error

Kind regards
Tiger


From: aflatoon on 13 Apr 2010 09:25

Assuming your value for i is correct (i.e. not greater than the number
of sheets), try saving the target workbook periodically (after every 10
copies for example).



t
h
e
_
t
i
g
e
r
_
g
r
r
r
;
6
9
7
9
1
1

W
r
o
t
e
:


>
G'day all you clever people, hope someone can help me
>
> I am using the code:
>
> ActiveSheet.Copy after:=Workbook("Example.xls").Sheets(i)
>
> to copy a dynamic selection of sheets from one workbook to another
>
> The code works just fine until the 18th sheet is copied across, then I
get a
> subscript out of range error message
>
> This is really frustrating as I know the code works, but I am limited
in how
> often I can use it, and I want to copy across 50+ sheets.
>
> Has anyone come across this issue before and does anyone know a good
way to
> solve it?
>
> PS, I ran the same code in Excel2007 and managed 45 repititions before
I get
> the same subscript out of range error
>
> Kind regards
> Tiger


--
aflatoon

Regards,
A.
------------------------------------------------------------------------
aflatoon's Profile: http://www.thecodecage.com/forumz/member.php?u=1501
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=195202

http://www.thecodecage.com/forumz

From: Ron de Bruin on 13 Apr 2010 13:41
See
http://support.microsoft.com/kb/210684/en-us


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm



"the_tiger_grrr" <thetigergrrr(a)discussions.microsoft.com> wrote in message
news:F3177BC2-81AB-403E-BA93-81D8E2E54F39(a)microsoft.com...
> G'day all you clever people, hope someone can help me
>
> I am using the code:
>
> ActiveSheet.Copy after:=Workbook("Example.xls").Sheets(i)
>
> to copy a dynamic selection of sheets from one workbook to another
>
> The code works just fine until the 18th sheet is copied across, then I get a
> subscript out of range error message
>
> This is really frustrating as I know the code works, but I am limited in how
> often I can use it, and I want to copy across 50+ sheets.
>
> Has anyone come across this issue before and does anyone know a good way to
> solve it?
>
> PS, I ran the same code in Excel2007 and managed 45 repititions before I get
> the same subscript out of range error
>
> Kind regards
> Tiger
>
>
 | 
Pages: 1
Prev: Find a Item & Copy the Data
Next: Apply ADDRESS formula in VB