Help:loop on two workbooks
 

Hi,

I am trying to loop on each sheet in a workbook. If any sheet o
interest is found, I need to copy the sheet into another workbook. M
code looks like as follows, but I keep getting error message "subscrip
out of range". Anyone could help me out. Thanks in advance.

For Each ws In ActiveWorkbook.Worksheets
i = 1
For i = 1 To 20
If ws.Name = Copy_list(i) Then

ws.Copy Befo=Workbooks _
("aa").Sheets(1)
Windows("bb").Activate
End If
Next
Nex

--
ana
-----------------------------------------------------------------------
anan's Profile: http://www.excelforum.com/member.php...fo&userid=2426
View this thread: http://www.excelforum.com/showthread.php?threadid=48369

Help:loop on two workbooks
 

Well without really knowing what Copy_List was dimensioned for or what is in
it, I would say that the error you are getting is you are trying to
reference Copy_List(i) where i is outside the bounds of the array.

If you issue the command msgbox ubound(Copy_List) what does it say? If it
says 20 take a look at the lbound statement. It could be that you
dimensioned it for 20 but it starts at 0 instead of 1.

LT
"anan" wrote in message
...

Hi,

I am trying to loop on each sheet in a workbook. If any sheet of
interest is found, I need to copy the sheet into another workbook. My
code looks like as follows, but I keep getting error message "subscript
out of range". Anyone could help me out. Thanks in advance.

For Each ws In ActiveWorkbook.Worksheets
i = 1
For i = 1 To 20
If ws.Name = Copy_list(i) Then

ws.Copy Befo=Workbooks _
("aa").Sheets(1)
Windows("bb").Activate
End If
Next
Next


--
anan
------------------------------------------------------------------------
anan's Profile:
http://www.excelforum.com/member.php...o&userid=24264
View this thread: http://www.excelforum.com/showthread...hreadid=483697