?
Solved

run command on multiple sheets at once

Posted on 2012-09-17
3
Medium Priority
?
305 Views
Last Modified: 2012-09-17
I have a code that is set to autorun on open but need the command to run for one sheet and then another. when I choose to activate the second sheet and run the command again i get the following Compile Error: Duplicate declaration in current scope. Here is the code.

Private Sub Workbook_Open()
Workbooks("Master Inventory Report PO numbers.xlsm").RefreshAll
Worksheets("Filled POs").Activate
 Range("C1:C99999").Select
    Selection.ClearContents
    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
 

 
  Worksheets("Due POS").Activate
      Selection.ClearContents
    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 & "WTF" & Error)
  Stop
  Resume Next
 

End Sub
0
Comment
Question by:natevelli2
3 Comments
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 38406220
Sounds like your declaring variables that are already active.

Check out this page.

http://www.ozgrid.com/VBA/variable-scope-lifetime.htm

try commenting out your constants.  It code be since you don't dim those, they carry over.  

-SA
0
 
LVL 35

Accepted Solution

by:
Norie earned 2000 total points
ID: 38406300
Instead of repeating the code for each worksheets you can loop through the worksheets and then run the code on each one.
Option Explicit

Private Sub Workbook_Open()
Const HEADER_ROWS As Long = 1
Const OUTPUT_TO_COLUMN As Long = 3
Const DELIMITER As String = vbNewLine
Dim ws As Worksheet
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
Dim arrWSNames
Dim I As Long

    Workbooks("Master Inventory Report PO numbers.xlsm").RefreshAll

    arrWSNames = Array("Filled POs", "Due POS")

    For I = LBound(arrWSNames) To UBound(arrWSNames)

        Set ws = Worksheets(arrWSNames(I))
        
        ws.Range("C1:C99999").ClearContents

        On Error GoTo Whoops
        Set A_Range = ws.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, ws.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), ws.UsedRange)
            Else
                Set A_Range = A_Range.Offset(1, 0)
            End If
        Loop

    Next I

    Exit Sub
Whoops:
    MsgBox (Err & "WTF" & Error)
    Stop
    Resume Next


End Sub

Open in new window

0
 

Author Closing Comment

by:natevelli2
ID: 38407023
Worked like a charm. Thank you
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question