Multiple Files: Activate problem

[bhanu2003]

Hi,

I have a set of files in a folder that i open for processing, also at the same time for each workbook opened from the folder, i need to write into another workbook, save and then cotinue processing the book i had opened from the folder. I have been able to it until writing it into the new workbook, but when i try to activate the one opened initially, i get an error msg "Subscript out of range", Run-time error - 9, am familiar with this error, but am not sure why it is showing up now.

code just before error:

Activeworkbook.save
Workbooks(sheetName).activate

SheetName has been passed from another module, also declared asa public.

Thanks in advance,
Bhanu

 

[bhanu2003]

Hi,

Anyone to help !!!,

Thanks,
Bhanu

 

[bhanu2003]

Hi,

Just replying to see if any body reminds me of what is wrong here.

Thanks,
Bhanu

 

[Cbrine]

Have you tried setting up references when the workbook is opened?

Dim Wb as workbook, Ws as worksheet
Dim PB as workbook, ps as worksheet

Set Wb = Activeworkbook
Set ws = activesheet

Workbooks.open "FileName"

Set pb = Activeworkbook
Set ps = Activesheet

When you are ready to switchback to the orginal WB just WB.activate and you know the reference is the correct reference. You also get the advantage of getting a drop down listing of functions when you type wb. or ws.

 

[bhanu2003]

The references look fine to me, also, wont this code work,

Fsheet = Activeworkbook.name

[ Process data, open and write another book]
[ save the second book]

workbooks(Fsheet).activate

i have this and doesnot seem to work, any ideas.

Thanks,
Bhanu

 

[Cbrine]

They look right, but I know in the past, I had problems caused by activesheet not pointing to what you think it might be pointing to. I usually happens when switching back and forth between sheets. I don't know if this is your issue or not, but it might be something to confirm.