Workbook.sheets.range.select problems

RET79

Well-known Member
Joined
Mar 19, 2002
Messages
526
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
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top