Results 1 to 4 of 4
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Code Assistance (XP)

    The following code works quite well as long as Sheet2 is active. It fails to run if another sheet is active which of course is what I am after.

    Sub Test()
    oWbook = "Book3.xls"
    oHeading = Array("CODE", "JAN", "FEB", "MAR", "APR", "MAY", "JUN", "JUL", "AUG", "SEP", "OCT", "NOV", "DEC")
    For i = 0 To 12
    Workbooks(oWbook).Sheets("Sheet2").Range(Cells(2, i + 2), Cells(2, i + 2)) = oHeading(i)
    Next i
    End Sub


    Thanks,
    John

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

    Re: Code Assistance (XP)

    You have to fully qualify each reference:

    Workbooks(oWbook).Sheets("Sheet2").Range(Workbooks (oWbook).Sheets("Sheet2").Cells(2, i + 2), Workbooks(oWbook).Sheets("Sheet2").Cells(2, i + 2)) = oHeading(i)

    This is tedious, so define an object variable:

    Dim wsh As Worksheet
    Set wsh = Workbooks(oWbook).Sheets("Sheet2")
    wsh.Range(wsh.Cells(2, i + 2), wsh.Cells(2, i + 2)) = oHeading(i)

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code Assistance (XP)

    Thank you Hans it works perfectly.

    Have a great weekend.
    John

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Code Assistance (XP)

    In addition to Hans' suggestion about the object variable, you can do it without the object variable if you use a "with.. End with":

    with Workbooks(oWbook).Sheets("Sheet2")
    .Range(.Cells(2, i + 2), .Cells(2, i + 2)) = oHeading(i)
    end with

    Steve

Posting Permissions

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