Workbook.sheets.range.select problems

[RET79]

HI.

Can anyone suggest why I am having so much difficulty going back and forth between two sheets from a different workbook when writing a macro? It keeps on saying subscript out of range, object doesnt support this proeprty etc.

All I need to do is copy something from workbook B, delete the contents on sheet2 on workbook A and paste the copied code on sheet2.

It seems like such a straightfoward thing to do but for some reason nothing seems to work and it is very frustrating.

Have I got some setting I need to change?

Thanks,

RET79

 

[brettvba]

do you know how to debug your code , sometimes watching what is going on in debug mode will answer your question , I have a lot of trial and error stuff Debuging makes it.

 

[RET79]

OK, I have another problem for u with this thing.

The 'original' workbook I do not know the name of as I am using this:

filetoOpen = Application.GetOpenFilename("Text Files (*.txt), *.txt")

thing. This gives me the full path of the file, so won't work like this:

Workbooks(filetoOpen). ....
does not work.

Is there a way of reducing the file path to just get the filename.xls out and then use that in the Workbooka("filename.xls").activate format?

Thanks

RET79

 

[RET79]

Sorry, I am answering my own question.

For that new text file I can just use

namesheet = activesheet.name & ".txt"

then can say
Workbooks(namesheet).activate

further on.