Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 3 of 3 FirstFirst 123
Results 21 to 29 of 29

Thread: dim ws as workbook etc.

  1. #21
    Board Regular
    Join Date
    Feb 2002
    Posts
    255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The problem is that you're saying Range("a1") and then saying current region. Range("a1").currentregion is cell a1. Try selection.currentregion or define a range object and just select the range object.

    Let me know if either of these suggestions work.

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

    Default

    Just for information...

    It is this line which the VBE doesn't like in my above code:

    nametextfile.sheetname.Range("A1").CurrentRegion.Copy original.Sheets("NetPrem").Range("A1")

    and it comes up with the error message

    object doesnt support this property or method.

    even when i break it up using Select like this:

    nametextfile.sheetnamed.Range("A1").CurrentRegion.Select


    that doesnt work either.

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

    Default

    in fact, even this doesnt work

    nametextfile.sheetnamed.Range("A1").Select


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

    Default

    so doesnt it like

    Set nametextfile = ActiveWorkbook
    Set sheetname = ActiveSheet

    for some reason? Even though I put

    Dim sheetname as worksheet
    Dim nametextfile as workbook

    at the beginning ?




  5. #25
    Board Regular
    Join Date
    Mar 2002
    Posts
    363
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Your code:
    nametextfile.sheetnamed.Range("A1").Select

    has a "d" at the end of sheetname, which is differernt from your Dim and Set statements.
    It's never too late to learn something new.

    Ricky

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

    Default

    Ricky,

    I hate to say this, and trust me I am not taking the ****, but, that was a typo.

    RET79

  7. #27
    Board Regular
    Join Date
    Mar 2002
    Posts
    363
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try this:

    nametextfile.Sheets(sheetname.Name).Range("A1").Select
    It's never too late to learn something new.

    Ricky

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

    Default

    PERFECTION!!!!

    Thanks to everyone so much for your help. Ricky, your killer tip finally pushed it, thanks ever for your help!!!!!

    Sub openAndPutOnNetPrem()


    Dim original As Workbook
    Dim nametextfile As Workbook
    Dim sheetnamed As Worksheet
    Set original = ThisWorkbook

    OpenTextFileMacro

    Set nametextfile = ActiveWorkbook
    Set sheetnamed = ActiveSheet
    original.Sheets("NetPrem").Range("A1").CurrentRegion.ClearContents
    nametextfile.Sheets(sheetnamed.Name).Range("A1").CurrentRegion.Copy
    original.Sheets("NetPrem").Range("A1").PasteSpecial
    Application.CutCopyMode = False

    nametextfile.Close False



    End Sub

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

    Default

    or maybe like this neglecting the paste special, this is now lightning quick, so much faster than when messing about with windows.activate and select, WOWOWOWOWOWOW


    Sub openAndPutOnNetPrem()


    Dim original As Workbook
    Dim nametextfile As Workbook
    Dim sheetnamed As Worksheet
    Set original = ThisWorkbook

    OpenTextFileMacro

    Set nametextfile = ActiveWorkbook
    Set sheetnamed = ActiveSheet
    original.Sheets("NetPrem").Range("A1").CurrentRegion.ClearContents
    nametextfile.Sheets(sheetnamed.Name).Range("A1").CurrentRegion.Copy _
    original.Sheets("NetPrem").Range("A1")
    Application.CutCopyMode = False

    nametextfile.Close False



    End Sub

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
  •