Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 29

Thread: dim ws as workbook etc.

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

    Default

    You can copy and paste from one workbook to another with the following without selecting or activating the workbook, worksheets or range.

    workbooks("book1").workSheets("sheet1").range("a1").copy destination:=workbooks("book2").workSheets("sheet1").range("a1")

    This copies the from book1 sheet1 cell a1 to book2 sheet1 cell a1
    It's never too late to learn something new.

    Ricky

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

    Default

    davers

    its fixed width, and makes no sense its like


    0234abc2434h20122003wfwj243242tokgq etc.

    BUT, we are told by the IT dept where to put the columns in, for instance the above becomes

    02,34,abc,2,43,4,h,20/12/2003,wfw,j24 etc.

    if they had given us delimited, but they didnt. so therefore we have to open fixed width in excel using the wizard to put columns in position 0,2,4,7,8,10,11,19,22 etc. BUT my macro is designed to avoid going through that wizard crap all the time so we jsut cut and paste the 0,2,4,7,8, etc. from their emails onto column B of my sheet and the macro 'reads' where to put the columns every time.

    I sincerely apologise for making such a meal out of this issue and no doubt I have put a lot of people off this topic. It would be great if you had a better way for me to do stuff with the text file but the issue I am after is still how to define workbooks etc. properly, without hard coding and cut and paste between sheets without activating and selecting.

    RET79

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

    Default

    Ricky,

    That is what I am after

    BUT

    I dont want the file names and sheets to be hard coded like that as I am planning on using this macro again in many different workbooks therefore I need a general way of naming these sheets.

    See one of my earlier postings about this. I don't want any "book1" stuff there. I want to declare them like this

    Dim Wb as workbook
    Set Wb = ThisWorkbook

    where ThisWorkbook would be "book1" say.


  4. #14
    Board Regular zacemmel's Avatar
    Join Date
    Apr 2002
    Location
    Redmond, WA
    Posts
    636
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You should be able to set that as an object and hardcode that in there. What error are you getting?

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

    Default

    zacemmel,

    i cant remember exactly as i tried so many different things i got so many different errors, but one that came up was saying that object doesnt support this property etc.

    i am really disturbed about this whole thing as i thought i had a good grasp about defining and using workbook objects but i cant believe i am having all this trouble to do this task.

    RET79

  6. #16
    Board Regular zacemmel's Avatar
    Join Date
    Apr 2002
    Location
    Redmond, WA
    Posts
    636
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Set wb = Workbooks("book2")
    wb.Sheets("sheet1").Range("a1:a10").ClearContents

    I just did that and it worked fine for me...

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

    Default

    zacammel,

    OK, that's cool, works for me too.

    BUT, 2 things

    (1) I dont want

    Set wb = Workbooks("book2") .


    I want

    Set wb = ThisWorkbook

    since wb will always be referring to the original workbook and I don't want hard coding here as I want to use this code in other workbooks and have it still work.

    2) I don't want to specify Range like

    Range("A1:A10")

    I want the current region becuase the range will vary.

    Does

    wb.Sheets("sheet1").Range("A1").CurrentRegion.ClearContents

    work for you? This is what I need effectively.

    Many thanks,

    RET79



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

    Default

    OK guys....

    this is my best attempt but this doesnt work, if you could tell me why it would be so happy:-

    Sub openAndPutOnNetPrem()


    Dim original As Workbook
    Dim nametextfile As Workbook
    Dim sheetname As Worksheet

    Set original = ThisWorkbook

    OpenTextFileMacro

    Set nametextfile = ActiveWorkbook
    Set sheetname = ActiveSheet

    original.Sheets("NetPrem").Range("A1").CurrentRegion.ClearContents

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

    Application.CutCopyMode = False

    End Sub

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

    Default

    Play around with this. It should get you somewhere. Select a bunch of cells in a workbook first and then step through this (Press F8).

    Sub WorkbookObjectLesson()
    Dim wb As Object
    Dim rngRange As Object

    Set rngRange = Selection
    Set wb = ActiveWorkbook
    MsgBox wb.Name
    rngRange.Value = wb.Name


    End Sub

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

    Default

    davers,

    ok that was cool. do you think that the current region thing may be the problem with my code? Surely I should be able to select, on any open workbook, the current region and then clear contents without using the select or activate thing?

    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
  •