Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Workbook.sheets.range.select problems

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Stockton, California
    Posts
    281
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Stockton, California
    Posts
    281
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Stockton, California
    Posts
    281
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

  9. #9
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  10. #10
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •