Workbook.sheets.range.select problems
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

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
  •  

 

 
DMCA.com