MS Excel VBA to run a macro against another spreadsheet

Experts,

Attached is a Master spreadsheet with a macro and forms to open another spreadsheet to apply its macro.  

The goal is to force this macro to run against the other open spreadsheet; however the following code applies the macro only to itself.  Can you please help?
SSReportBuilder.xls
LVL 1
CFMIFinancial Systems AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
Hi,

Within the Private Sub cmdOK_Click() event code of the "UserForm1" form, remove (delete the entire line, or comment-out, as you see fit) the defintion of:

Dim wbTarget            As Workbook


That is, so that the routine now reads something like this:

Private Sub cmdOK_Click()
cmdOK.Enabled = False
Dim app                 As Application
'Dim wbTarget            As Workbook                                ' *** REMOVED
Dim strReturnedValue    As String
Dim SSFileName          As String
Dim FullName            As String


If Risk = -1 Then

'================================================
   'Supply/Change fullname
'================================================

FULL_PATH = Workbooks("SSReportBuilder.xls").Worksheets(1).Cells(1)

'================================================
   'Set a reference to the new instance
'================================================
Set app = New Application

With app
.Visible = True
.WindowState = xlMinimized

'================================================
   'Open Workbook
'================================================
FullName = UserForm1.SSFileName
Set wbTarget = app.Workbooks.Open(UserForm1.SSFileName)

'================================================
    'Run selected macro
'================================================
    Application.Run "SSreportBuilder.xls!McrRisk"
       
'================================================
    'Close Workbook and Kill the instance
'================================================
    'Workbooks(FullName).Close savechanges:=False
        wbTarget.Close False
        .Quit
End With
cmdOK.Enabled = False
MsgBox "The Spreadsheet has been updated using the correct formats"
Else
    MsgBox "Please select a Macro"
End If

End Sub

Open in new window



Now, change the "Module2" code module to read as follows:

Public wbTarget                                         As Workbook ' *** ADDED
Sub McrRisk()
'
' McrRisk Macro
' Format entire worksheet
' Thousands

  wbTarget.Activate                                                 ' *** ADDED
  Worksheets(1).Select                                              ' *** ADDED
  
    Range( _
        "D13:O13,D16:O16,D19:O19,D22:O22,D24:O24,D27:O27,D30:O30,D33:O33,D36:O36,D40:O40,D41:O41,D45:O48,D50:O50,D54:O56,D58:O58" _
        ).Select
    Range("O58").Activate
     Selection.NumberFormat = "#,##0,;(#,##0,)"
' Decimals
    Range( _
        "D14:O14,D17:O17,D20:O20,D25:O25,D28:O28,D31:O31,D34:O34,D37:O37" _
        ).Select
    Range("O37").Activate
     Selection.NumberFormat = "#,##0.00;(#,##0.00)"
' Numbers
 Range("D61,D63,D64,D65,D67,E61,E63,E64,E65,E67,F61,F63,F64,F65,F67,G61,G63,G64,G65,G67,H61,H63,H64,H65,H67,I61,I63,I64,I65,I67,J61,J63,J64,J65,J67,K61,K63,K64,K65,K67,L61,L63,L64,L65,L67,M61,M63,M64,M65,M67,N61,N63,N64,N65,N67,O61,O63,O64,O65,O67").Select
    Range("O67").Activate
    Selection.NumberFormat = "#,##0;(#,##0)"
' Percentage
    Range("D42,D43,D52,D59,E42,E43,E52,E59,F42,F43,F52,F59,G42,G43,G52,G59,H42,H43,H52,H59,I42,I43,I52,I59,J42,J43,J52,J59,K42,K43,K52,K59,L42,L43,L52,L59,M42,M43,M52,M59,N42,N43,N52,N59,O42,O43,O52,O59").Select
    Range("L59").Activate
    Selection.NumberFormat = "0.00%;(0.00%)"
' Header Copy
    Range("N10").Select
    Selection.Copy
    Range("O10").Select
    ActiveSheet.Paste
' Header Update
    Range("O9").Select
    ActiveCell.FormulaR1C1 = "Variance"
' Header Delete
    Range("C9:C12").Select
    Selection.ClearContents
' Include Color
    Range("G14:I67").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.149998474074526
        .PatternTintAndShade = 0
    End With
        Range("M14:O67").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.149998474074526
        .PatternTintAndShade = 0
    End With
Range("C2").Select

End Sub

Open in new window



A workbook is attached containing these changes.

BFN,

fp.
Q-28258096.xls
0
CFMIFinancial Systems AnalystAuthor Commented:
Hello,

I made the suggested corrections but the macro still runs on the master.  Please see the attached spreadsheet; perhaps I incorrectly modified the code.
SSReportBuilder.xls
0
CFMIFinancial Systems AnalystAuthor Commented:
Hello,

I used your workbook and received an error, "Subscript out of range" and the code highlighted was:
Hello,

FULL_PATH = Workbooks("SSReportBuilder.xls").Worksheets(1).Cells(1)
0
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

[ fanpages ]IT Services ConsultantCommented:
Hello,

That is the line you added yesterday.
0
[ fanpages ]IT Services ConsultantCommented:
You seem to have added the McrRisk() routine to the UserForm, instead of Module2.

Please go back to my attachment & use that.
0
CFMIFinancial Systems AnalystAuthor Commented:
Hello,

I used your attached workbook and received an error, "Subscript out of range" and the code highlighted was the below statement:

FULL_PATH = Workbooks("SSReportBuilder.xls").Worksheets(1).Cells(1)

so I remarked that line and the code executed, however, it ran against itself and not the opened additional spreadsheet.  I so need the macro to run against another workbook.
0
CFMIFinancial Systems AnalystAuthor Commented:
Is this a great strategy to have another spreadsheet use the Templates macro?  Open the Template, open a selected workbook, create a new worksheet, copy the macro, close the Template, run the macro, delete the new worksheet and save the spreadsheet.
0
[ fanpages ]IT Services ConsultantCommented:
Oh, I see the problem!

I assumed something about your MS-Excel environment that may not be true.

To counter this, I have changed this line:

Set wbTarget = app.Workbooks.Open(UserForm1.SSFileName)

to read:

Set wbTarget = Workbooks.Open(UserForm1.SSFileName)

(Note the removal of the "app." prefix)

The entire listing for the "UserForm1" code module should now read:

Private Sub cmdCancel_Click()
    Unload Me
End Sub
Private Sub cmdOK_Click()
cmdOK.Enabled = False
Dim app                 As Application
Dim wbTarget            As Workbook                                ' *** REINSTATED!
Dim strReturnedValue    As String
Dim SSFileName          As String
Dim FullName            As String

If Risk = -1 Then

'================================================
   'Supply/Change fullname
'================================================

FULL_PATH = Workbooks("SSReportBuilder.xls").Worksheets(1).Cells(1)

'================================================
   'Set a reference to the new instance
'================================================
Set app = New Application

With app
.Visible = True
.WindowState = xlMinimized

'================================================
   'Open Workbook
'================================================
FullName = UserForm1.SSFileName
'Set wbTarget = app.Workbooks.Open(UserForm1.SSFileName)
Set wbTarget = Workbooks.Open(UserForm1.SSFileName)

'================================================
    'Run selected macro
'================================================
    Application.Run "SSreportBuilder.xls!McrRisk"
       
'================================================
    'Close Workbook and Kill the instance
'================================================
    'Workbooks(FullName).Close savechanges:=False
        wbTarget.Close False
        .Quit
End With
cmdOK.Enabled = False
MsgBox "The Spreadsheet has been updated using the correct formats"
Else
    MsgBox "Please select a Macro"
End If

End Sub
Private Sub cmdSelect_Click()
    Dim fileToOpen As Variant
    
    fileToOpen = Application.GetOpenFilename("Excel files(*.xls*),*.xls*", , "Select File")
    If fileToOpen <> False Then
        SSFileName.Value = fileToOpen
    End If

End Sub
Private Sub OptionButton1_Click()

End Sub
Public Sub SpreadsheetPath()
    Dim FULL_PATH As String

End Sub
Private Sub DoAll()
Dim wbkX As Workbook
For Each wbkX In Application.Workbooks
wbkX.Activate
Application.Run ThisWorkbook.Name & "!McrRisk"
Next wbkX
End Sub
Sub WoksheetsByCodeName()
Dim ws As Worksheet
Dim lngRow As Long
Dim lngCount As Long
For Each ws In Worksheets
lngRow = 8
'Select   case is case sensitive
Select Case UCase(ws.CodeName)
Case "SHEET14"
Case Else ' do calculations for all other sheets
With ws
Do
lngCount = .Range("A" & lngRow).End(xlDown).Row - lngRow
Application.Run "SSreportBuilder.xls!McrRisk"
If lngRow > 100 Then Exit Do
Loop
End With
End Select
Next ws
End Sub

Open in new window



I have also simplified the code within the "Module2" code module:

Sub McrRisk()
'
' McrRisk Macro
' Format entire worksheet
' Thousands

  Worksheets(1).Select                                              ' *** ADDED
  
    Range( _
        "D13:O13,D16:O16,D19:O19,D22:O22,D24:O24,D27:O27,D30:O30,D33:O33,D36:O36,D40:O40,D41:O41,D45:O48,D50:O50,D54:O56,D58:O58" _
        ).Select
    Range("O58").Activate
     Selection.NumberFormat = "#,##0,;(#,##0,)"
' Decimals
    Range( _
        "D14:O14,D17:O17,D20:O20,D25:O25,D28:O28,D31:O31,D34:O34,D37:O37" _
        ).Select
    Range("O37").Activate
     Selection.NumberFormat = "#,##0.00;(#,##0.00)"
' Numbers
 Range("D61,D63,D64,D65,D67,E61,E63,E64,E65,E67,F61,F63,F64,F65,F67,G61,G63,G64,G65,G67,H61,H63,H64,H65,H67,I61,I63,I64,I65,I67,J61,J63,J64,J65,J67,K61,K63,K64,K65,K67,L61,L63,L64,L65,L67,M61,M63,M64,M65,M67,N61,N63,N64,N65,N67,O61,O63,O64,O65,O67").Select
    Range("O67").Activate
    Selection.NumberFormat = "#,##0;(#,##0)"
' Percentage
    Range("D42,D43,D52,D59,E42,E43,E52,E59,F42,F43,F52,F59,G42,G43,G52,G59,H42,H43,H52,H59,I42,I43,I52,I59,J42,J43,J52,J59,K42,K43,K52,K59,L42,L43,L52,L59,M42,M43,M52,M59,N42,N43,N52,N59,O42,O43,O52,O59").Select
    Range("L59").Activate
    Selection.NumberFormat = "0.00%;(0.00%)"
' Header Copy
    Range("N10").Select
    Selection.Copy
    Range("O10").Select
    ActiveSheet.Paste
' Header Update
    Range("O9").Select
    ActiveCell.FormulaR1C1 = "Variance"
' Header Delete
    Range("C9:C12").Select
    Selection.ClearContents
' Include Color
    Range("G14:I67").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.149998474074526
        .PatternTintAndShade = 0
    End With
        Range("M14:O67").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.149998474074526
        .PatternTintAndShade = 0
    End With
Range("C2").Select

End Sub

Open in new window



Please download this (attached) revision & rename it from my choice of "Q-28258096b.xls" to "SSReportBuilder.xls" when saving locally, before you open it.

I simply renamed the file to attach to this thread, so that if you downloaded it you would know which question the workbook referred to.


Sorry for the inconvenience.


To summarise:

Please rename "Q-28258096b.xls" to "SSReportBuilder.xls" before trying this workbook.

Thank you.
Q-28258096b.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CFMIFinancial Systems AnalystAuthor Commented:
Excellent, the code works and executes on the selected workbook!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.