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

[robfo0]

do you have your the code you are using? and the names of your workbooks. that would help a lot in trying to trouble shoot.
also, do you have both workbooks open when you execute the macro?

[RET79]

Yes, both workbooks open. Ok, I will give u the code. I am already on the sheet I wish to copy the data:=



Range("A1").CurrentRegion.Copy

Worksheets("step22small.xls").Sheets("NetPrem").Select
Range("A1").CurrentRegion.Delete
Range("A1").PasteSpecial

It doesn't like the worksheets.sheets line and it wont paste the code.

[robfo0]

try this:

Range("A1").Copy

Workbooks("step22small.xls").Sheets("NetPrem").Range("A1").pastespecial

Application.CutCopyMode = False

im not sure what currentregion was, but i know you dont need it. Also, you had worksheets instead of workbooks when you defined your workbook. also, you didnt have a range defined. the format is generally:

workbooks("bookname").Sheets("sheetname").range("A1"). something


hope that helps

edit: you dont even really need the delete or clear contents, the paste will go over it automatically.
[ This Message was edited by: robfo0 on 2002-04-18 21:11 ]

[ This Message was edited by: robfo0 on 2002-04-18 21:14 ]

[ This Message was edited by: robfo0 on 2002-04-18 21:16 ]

[RET79]

Nearly.....

It is on the destination sheet I want to delete the contents, not on the initial sheet.....

so, based on your code, i tried this, but this doesnt work either.

Range("A1").CurrentRegion.Copy

Workbooks("step22small.xls").Sheets("NetPrem").Range("A1").CurrentRegion.ClearContents

Workbooks("step22small.xls").Sheets("NetPrem").Range("A1").PasteSpecial

PS Current region - if you record a macro of clicking ctrl-shift-star

[robfo0]

sorry RET, i edited a few times, check my post again, it should work now, heh

[RET79]

Current region is quite crucial here.

Current region means that I am not just copying and pasting A1, I am copying A1 PLUS its current region which means those cells stuck to A1 too.

If you fill A1:C3 with numbers on some sheet, now click A1, then hold down control-shift-*, then it will select the 'current region'.

So, now maybe you will see what my problem is with the deleting, in that, the region I am copying and pasting maybe smaller than the region already there, i.e. smaller than the region i am initially deleting.

does that make any sense?

[brettvba]

it would work but when you delete you loose your paste so if you go back to the original workbook and copy again then a straight paste it will work!

[brettvba]

Sub copy()

Workbooks("step22small.xls").Activate
Sheets("NetPrem").Select
Range("A1").CurrentRegion.Delete
Workbooks("originalwkb.xls").Activate
Range("A1").CurrentRegion.copy
Workbooks("step22small.xls").Activate
Sheets("NetPrem").Select
Range("A1").CurrentRegion.Paste

End Sub

[RET79]

OK.

So if you delete you lose your paste, ok I will remember that.

But with these workbooks.worksheets. etc

I have had many problems in the past with macros, in that, many times the macro will not work unless I am viewing a particular sheet, even though all sheets iam referring to are open, and also the fact that I am giving it the workbook("abc.xls").worksheet("Sheet1").range("A1") treatment. This is highly annoying, and I have resigned to putting macro buttons on the sheets on which the macro will work.

Have you any idea why this is such a problem?
It is quite depressing

Thanks for all your help anyway

RET79