• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 349
  • Last Modified:

Auto Run macros in excel on a specific worksheet tab

I have several scripts i need to autorun when an excel sheet is opened. there are several sheets in this file and need just the one to autorun even though it might be hidden or not the main sheet that opens with the file here is what i have but I also need this sheet to run a auto refresh too before running the others.


Private Sub Workbook_Open()
     'change C6 to your desired cell
     
    Range("C1:C99999").Select
    Selection.ClearContents
     
End Sub
Private Sub Make_Severely_Denormalized()
  Const HEADER_ROWS As Long = 1
  Const OUTPUT_TO_COLUMN As Long = 3
  Const DELIMITER As String = vbNewLine
  Dim A_Range As Range
  Dim B_Range As Range
  Dim A_temp As Range
  Dim B_temp As Range
  Dim B_Cell As Range
  Dim Concat As String

On Error GoTo Whoops
  Set A_Range = Range("A1").Offset(HEADER_ROWS)
  Do While Not A_Range Is Nothing
    Set B_Range = A_Range.Offset(0, 1)

    ' some helper ranges
    If A_Range.Offset(1, 0).Value = "" Then
      Set A_temp = Range(A_Range, A_Range.End(xlDown).Offset(-1, 0))
    Else
      Set A_temp = A_Range.Offset(1, 0)
    End If
    Set B_temp = Range(B_Range, B_Range.End(xlDown)).Offset(0, -1)

    ' determine how high "B" is WRT no change in "A"
    Set B_Range = Range(B_Range, B_Range.Resize( _
      Application.Intersect(A_temp, B_temp, ActiveSheet.UsedRange).Count))

    ' loop through "B" and build up the string
    Concat = ""
    For Each B_Cell In B_Range
      Concat = Concat & B_Cell.Value & DELIMITER
    Next
    Concat = Left(Concat, Len(Concat) - Len(DELIMITER))

    ' do the needful
    A_Range.Offset(0, OUTPUT_TO_COLUMN - 1).Value = Concat

    ' find the next change in "A"
    If A_Range.Offset(1, 0).Value = "" Then
      Set A_Range = Application.Intersect(A_Range.End(xlDown), ActiveSheet.UsedRange)
    Else
      Set A_Range = A_Range.Offset(1, 0)
    End If
  Loop
  Exit Sub
Whoops:
  MsgBox (Err & " " & Error)
  Stop
  Resume Next
End Sub
0
natevelli2
Asked:
natevelli2
1 Solution
 
natevelli2Author Commented:
Got it to work but once the code executes I need it to go back to the main sheet. Here are the last few commands

    ' find the next change in "A"
    If A_Range.Offset(1, 0).Value = "" Then
      Set A_Range = Application.Intersect(A_Range.End(xlDown), ActiveSheet.UsedRange)
    Else
      Set A_Range = A_Range.Offset(1, 0)
    End If
  Loop
  Exit Sub
Whoops:
  MsgBox (Err & " " & Error)
  Stop
  Resume Next

WorkSheets("Master Report").Activate
End Sub


The Activate command dos not want to work. Please help
0
 
Chris BottomleyCommented:
You have the lines:


 Loop
  Exit Sub

Place your activate between these two lines!

Chris
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now