Definining Strings in one macro to be used with other macros called 1

[RooSXL]

I have two macros. In the first certains cells in the worksheet contain the files that I need to open and copy information from them once I have inserted in a specific cell the location I need the information from. This needs to be repeated for each sheet in my workbook. I need to be able to use the defined string to activate/deactivate the other file. Can this be done? Here is a sample of my two macros. I know the coding is a little long and it could be simpler, therefore any improvement suggestions are welcomed.

Thanks,

OZ

Sub Update_WIPFlow_AllPlants()
     ' Loop through all sheets in the workbook by activating each at a time
Dim DIRECTORY As String
Dim UNITFLOW As String
    
    Range("Z1").Select
    DIRECTORY = ActiveCell.Value
    Range("Z2").Select
    UNITFLOW = ActiveCell.Value
    Workbooks.Open Filename:=(DIRECTORY + UNITFLOW)
    Windows("ME Performance Report Plants.xls").Activate
     For i = 3 To Sheets.Count
     Sheets(i).Activate
     Application.Run "'ME Performance Report Plants.xls'!Get_UnitsFlow"
    Next i
    Windows(UNITFLOW).Activate
    ActiveWindow.Close

Sub Get_UnitsFlow()

    Range("U1").Select
    Selection.Copy
    Windows(UNITFLOW).Activate
    Range("Q21").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False
    Range("B3:G8,J3:R8,U3:W8").Select
    Selection.Copy
    Windows("ME Performance Report Plants.xls").Activate
    Range("A12").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

 

[Zathras]

Look in the VBA help file under the topic "Understanding Scope and Visibility"

You can declare Public variables in a module by placing the definition at the top before any Subs or Functions. That variable can then be referenced from any module or form in the project.

 

[byundt]

OZ,
I'm not quite sure what you are doing, but it would be good practice to do the following:
1) Avoid selecting cells. It makes the code run slower, the user loses the cursor location, and it takes more statements than necessary to do the job.
2) Avoid activating windows, worksheets and workbooks where possible. Same reasons as #1 above
3) Do use variables to point to workbooks, worksheets and ranges that you use more than once. This makes the code much easier to follow, as well as faster & more compact.

With these concepts in mind, I've rewritten your code. It executes, but will probably need tweaking before it meets your objectives.

Sub Update_WIPFlow_AllPlants()
     ' Loop through all sheets in the workbook by activating each at a time
Dim DIRECTORY As String
Dim UNITFLOW As String
Dim ws As Worksheet, wsUnitFlow As Worksheet
Dim wb As Workbook, wbUnitFlow As Workbook
Dim i As Long
On Error Resume Next

    Set wbUnitFlow = Workbooks("UnitFlow.xls")      'Strictly a guess on my part
    Set wsUnitFlow = wbUnitFlow.Worksheets(1)       'Strictly a guess on my part
    
    DIRECTORY = [Z1]    'DIRECTORY= value from cell Z1 on active worksheet
    UNITFLOW = [Z2]
    Workbooks.Open Filename:=(DIRECTORY + UNITFLOW)
    With Workbooks("ME Performance Report Plants.xls")
        For i = 3 To .Sheets.Count
            Application.Run "'ME Performance Report Plants.xls'!Get_UnitsFlow", .Sheets(i)
        Next i
    End With
    wbUnitFlow.Close
End Sub

Sub Get_UnitsFlow(ws As Worksheet)
Dim wsUnitFlow As Worksheet, wsME As Worksheet
Dim ar As Range
Set wsUnitFlow = Workbooks("UnitFlow.xls").Worksheets(1)
Set wsME = Workbooks("ME Performance Report Plants.xls").Worksheets(1)
With ws
    wsUnitFlow.Range("Q21") = .Range("U1")
    For Each ar In .Range("B3:G8,J3:R8,U3:W8").Areas
        ar.Copy
        wsME.Range("A12").Offset(0, ar.Column - 2).PasteSpecial xlPasteValues
    Next ar
End With
End Sub

Brad