Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Jun 2005
    Location
    Singapore
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Worksheet to workbook VBA (Office XP)

    Hans and all

    I have constructed, of course, with the helps of the lounge a few marcos which work on a worksheet basis,. If I want to change them to a workbook basis which means the code will runs for on every sheet of the workbook, how do I change them?

    thanks always, kun

  2. #2
    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: Worksheet to workbook VBA (Office XP)

    Put them in the thisworkbook object

    Place the code (the part between the sub xxx - end sub) into a the appropriate workbook event (usually the same names preceeded by SHEET
    eg:
    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    becomes:

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)

    with the Sh object you can tell what sheet called it and have alternate code for some sheets, if desired
    Steve

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet to workbook VBA (Office XP)

    We would need to see the macro to tell you how to make it work on all worksheets. You will have to add another loop to the macro like this:

    <code>
    [code]
    Dim oWS As Worksheet
    For Each oWS In Worksheets
    'modified current code goes here
    Next oWS
    </code>

    However, how the current code has to be modified depends on what the current code is.
    Legare Coleman

  4. #4
    Star Lounger
    Join Date
    Jun 2005
    Location
    Singapore
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet to workbook VBA (Office XP)

    If I have something like this :

    For r = ActiveSheet.UsedRange.Rows.Count To 1 Step -1
    If ActiveSheet.Cells(r, 5) = 0 Then ActiveSheet.Rows®.Delete
    Next
    End Sub

    To change the code which will run in a workbook level, do I change it to :

    For r = ActiveWorkbook.UsedRange.Rows.Count To 1 Step -1
    If ActiveWorkbook.Cells(r, 5) = 0 Then ActiveWorkbook.Rows®.Delete

    Also why is it the some code start with :

    Public Sub Worksheet_ActivateSort1() while the others don't. but is also in worksheet level.

    Thanks, kun

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet to workbook VBA (Office XP)

    It is not completely clear what you mean by "run in a workbook level." If that means that you want that code snippet to run on each worksheet in the workbook, then you would have to modify it to something like this:

    <code>
    Dim oWS As Worksheet
    Dim r As Long
    For Each oWS In Worksheets
    For r = oWS.UsedRange.Rows.Count To 1 Step -1
    If oWS.Cells(r, 5) = 0 Then oWS.Rows®.Delete
    Next r
    Next oWS
    </code>

    If that is not what you mean, then could you try a more detailed explaination.

    I do not understand what you mean by "Also why is it the some code start with :

    Public Sub Worksheet_ActivateSort1() while the others don't. but is also in worksheet level."

    What does the other code start with. What do you mean by "in worksheet level?"
    Legare Coleman

  6. #6
    Star Lounger
    Join Date
    Jun 2005
    Location
    Singapore
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet to workbook VBA (Office XP)

    Yes, I want the code snippet to run on each sheet of the workbook at the same time when I click on the marco. At where do I insert your code?

    This code start with Public Sub Worksheet_Activate.. and the other doesn't

    Public Sub Worksheet_ActivateSort1()
    Dim LRow As Long '-- SORT on Col A then C
    'Find row before last row in Column A with content
    LRow = Cells(Rows.Count, 3).End(xlUp).Offset(-1, 0).Row
    Rows("3:" & LRow).Sort Key1:=Range("A3"), _
    Order1:=xlAscending, Key2:=Range("C3"), _
    Order2:=xlAscending, Header:=xlNo, _
    OrderCustom:=1, MatchCase:=False, _
    Orientation:=xlTopToBottom
    End Sub

    This doesn't, but didn't use the Worksheet_Activate

    Sub PurgeZeros()
    Dim r As Long
    For r = ActiveSheet.UsedRange.Rows.Count To 1 Step -1
    If ActiveSheet.Cells(r, 5) = 0 Then ActiveSheet.Rows®.Delete
    Next
    End Sub

    Both are for active worksheet which I called as "worksheet level". Would appreciate if you can enlighten me on this.

    Thanks always, kun

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet to workbook VBA (Office XP)

    My code replaces the code that you had in your message. Therefore, you put it where that code is.

    Worksheet_Activate is normally the name of the Sub in the module behind a worksheet that is run when the worksheet is activated. Worksheet_ActivateSort1 is just the name that you or someone has given that Sub. The Worksheet_Activate in front of Sort1 has no particular siginficance. My only guess here is that you or someone else copied a Sub name Worksheet_Activate and wanted to rename it Sort1 but didn't get the Worksheet_Activate replaced.
    Legare Coleman

Posting Permissions

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