Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Toronto, Ontario, Canada
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Changing activesheet (Excel 2003 VBA)

    All I really want to know is how to change the activesheet to a different worksheet.
    I also want to confirm that to lookup values in another workbook I would simply create a variable of type workbook and set that variable to the path of the workbook, correct?

    Example:
    Dim wkbTest as Workbook
    wkbTest = "c:test.xls"
    wkbTest.ActiveSheet("Sheet3")


    Is that right?

  2. #2
    Super Moderator
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,507
    Thanks
    0
    Thanked 4 Times in 4 Posts

    Re: Changing activesheet (Excel 2003 VBA)

    1. You'll have to make sure that the 'other' workbook is Open. Some code samples for checking and opening that are in the Excel Forum. And once open you can either dim it and use the variable or specify the workbook, much as in the following notes about worksheets.

    2. ActiveWorksbook.Worksheets("mysheet").Activate

    However, it's not necessary to activate a worksheet to read or write data from/to it, you just need to specify the sheet in the code, or dim a range in that sheet and use the variable. So if "mysheet" is active you can still do things like

    ActiveWorksbook.Worksheets("othersheet").Range("A1 ").Value = 999

    without activating "othersheet".
    -John ... I float in liquid gardens
    [acronym title="Gday mate!"][/acronym] [acronym title="What up Dude?"][/acronym] UTC -7±DS

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 Posts

    Re: Changing activesheet (Excel 2003 VBA)

    You can use code like this:

    Dim wkbTest As Workbook
    ' Open an existing workbook
    Set wkbTest = Workbooks.Open("c:test.xls")
    ' Activate a specific worksheet
    wkbTest.Worksheets("Sheet3").Activate

    In many situations, it is not necessary to activate a worksheet. You can retrieve cell values or modify the worksheet even if it is not the active worksheet. Neither does the workbook have to be the active workbook. The workbook must be open, though.

    MsgBox wkbTest.Worksheets("Sheet3").Range("B37")

    wkbTest.Worksheets("Sheet3").Range("B37") = "Woody's Lounge"

  4. #4
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Toronto, Ontario, Canada
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing activesheet (Excel 2003 VBA)

    If I am copying values from wbkTest to the workbook which contains this macro, am I going to need to constantly change the active workbook, or can I just refer to the workbooks by storing them in variables?

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing activesheet (Excel 2003 VBA)

    You should be able to use code like this:

    Dim wkbTest As Workbook
    Dim wkbTarget as Workbook
    ' Open an existing workbook
    Set wkbTest = Workbooks.Open("c:test.xls")
    'Open target workbook
    Set wkbTarget = Workbooks.Open("c:Target.xls")
    wkbTest.Worksheets("Sheet1").Range("A1:G10").Copy Destination:=wkbTarget.WOrksheets("SHeet3").Range( "C3")
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Toronto, Ontario, Canada
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing activesheet (Excel 2003 VBA)

    I get an error saying the Target workbook is already open.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 Posts

    Re: Changing activesheet (Excel 2003 VBA)

    Change

    Set wkbTarget = Workbooks.Open("c:Target.xls")

    to

    Set wkbTarget = Workbooks("c:Target.xls")

  8. #8
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Toronto, Ontario, Canada
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing activesheet (Excel 2003 VBA)

    I get a subscript out of range error.

  9. #9
    Super Moderator
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,507
    Thanks
    0
    Thanked 4 Times in 4 Posts

    Re: Changing activesheet (Excel 2003 VBA)

    <P ID="edit" class=small>(Edited by JohnBF on 21-Jul-05 08:25. Syntax error corrected)</P>If the workbook is open exclude the path.

    How you need to do this is something like (untested air code):

    On error resume next
    Set wkbTarget = Workbooks.Open("c:Target.xls") 'opens it , doesn't error out if it's already open
    On error goto 0
    If wkbTarget is nothing then _ ' it was already open so set the reference
    Set wkbTarget =Workbooks("Target.xls")
    ...

    HTH.
    -John ... I float in liquid gardens
    [acronym title="Gday mate!"][/acronym] [acronym title="What up Dude?"][/acronym] UTC -7±DS

  10. #10
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Toronto, Ontario, Canada
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing activesheet (Excel 2003 VBA)

    Awesome!! Thanks for all the help guys!

Posting Permissions

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