Dynamic Path based on Cell Value

Sowelu

New Member
Joined
Mar 18, 2009
Messages
27
Hi,

I have VBA code with array formula that looks up some values from series of spreadsheets. Which spreadsheet to use depends on the values in another column.

How do I make the formula the same for the whole range as it will substitute the necessary path into itself? :confused:


here is an example:

here is a code for VBA:

Code:
Range("I2").Select
    Selection.FormulaArray = _
        "=INDEX('1_Repl'!$C$2:$C$8,MATCH(1,IF('1_Repl'!$A$2:$A$8=H4,IF('1_Repl'!$C$2:$C$8<>0,1)),0))"
    Selection.AutoFill Destination:=Range("I2:I535"), Type:=xlFillDefault
TEST_LOOKUP.xls
ABCDEFGHIJK
11stSpreadsheet2ndSpreadsheet
2
3Number123CodeNumberData2
41230.200.211230.22Code=1,so1_Repl.xls
52340.10.1012340.1
63450002345#N/Ahastoreferto2_Repl.xls
712300.2203456#N/A
8234000.153567#N/Apathhastobedynamic
91230.240.240.243678#N/AValueofG2+"_Repl.xls"
103450.1300
11
1_Repl


Thanks a lot for help!!
 
Ok, here is what I have now.

Alex, I had a problem with data type mismatch when it tries to find cells with values "#N/A".. I searched online, and I think Iserror might work.

But this is not a big issue.
My problem now is with my second formula I get

"Unable to set FormulaArray property of the Range class"
on the line
.Cells(x, y).FormulaArray = myFormula

From what I read online my formula is too big..
I had problems with it even doing it by hand. If I have the files open and it doesn't have to write the full path in the formula (which makes it so much shorter) I can copy the formula down. But when I try to do it for a full formula with full path to the file, Excel shuts down when I try to copy formula down.
...

Any ideas?
I appreciate your help.

here is the code...

Code:
Sub foo()
Dim myCodeNum As String
Dim myFormula As String
Dim ws As Worksheet
Dim LRow As Long, x As Long, y As Long

Const ROW_WHERE_FORMULA_STARTS As Long = 2 '//First formula will be in this row
Const COLUMN_TO_PUT_FORMULA_IN As Long = 7 '//Column Number to write formulas in..."A" = 1, "B" = 2 etc.
Const COLUMN_TO_DETERMINE_LAST_ROW_BY As Long = 3 '//Excel will fill formulas down as far as last cell in this column
Const COLUMN_WITH_CODE_NUMBERS As Long = 1 '//Column with number to determine Repl sheet number

'--------------------------------------------

Set ws = ActiveSheet
Application.DisplayAlerts = False

With ws
    '//FIRST PASS-----------------------------------------------------------
    LRow = .Cells(Rows.Count, COLUMN_TO_DETERMINE_LAST_ROW_BY).End(xlUp).Row
    y = COLUMN_TO_PUT_FORMULA_IN

    For x = ROW_WHERE_FORMULA_STARTS To LRow
        
        '//Get sheet number to use in formula
        myCodeNum = .Cells(x, COLUMN_WITH_CODE_NUMBERS).Value

        '//Create a formula string
        myFormula = "=INDEX('C:\Work\OTT_Pricing\OTT_Repl_Update\[" _
 & myCodeNum & "_Repl.xls]Sheet1'!R1C5:R5000C5,MATCH(1,IF('C:\Work\OTT_Pricing\OTT_Repl_Update\[" _
 & myCodeNum & "_Repl.xls]Sheet1'!R1C1:R5000C1=RC3,IF('C:\Work\OTT_Pricing\OTT_Repl_Update\[" _
 & myCodeNum & "_Repl.xls]Sheet1'!R1C5:R5000C5<>0,1)),0))"


        '//write formula to cell
        .Cells(x, y).FormulaArray = myFormula
    Next x

    '//SECOND PASS - hard copy values----------------------------------------
    For x = ROW_WHERE_FORMULA_STARTS To LRow
        .Cells(x, y).Value = .Cells(x, y).Value
        If IsError(.Cells(x, y).Value) Then
            .Cells(x, y).ClearContents
        End If
    Next x
    
    '//THIRD PASS - do something with blank cells----------------------------
    For x = ROW_WHERE_FORMULA_STARTS To LRow
        If Len(.Cells(x, y).Value) = 0 Then
            '//Do Something with blank cell
            myCodeNum = .Cells(x, COLUMN_WITH_CODE_NUMBERS).Value
            
        myFormula = "=INDEX('C:\Work\OTT_Pricing\OTT_Repl_Update\[" _
 & myCodeNum & "_Repl.xls]Sheet1!R2C3:R5000C19,MIN(IF('C:\Work\OTT_Pricing\OTT_Repl_Update\[" _
 & myCodeNum & "_Repl.xls]Sheet1!R2C1:R5000C1=RC3,IF('C:\Work\OTT_Pricing\OTT_Repl_Update\[" _
 & myCodeNum & "_Repl.xls]Sheet1!R2C3:R5000C19>0,ROW('C:\Work\OTT_Pricing\OTT_Repl_Update\[" _
 & myCodeNum & "_Repl.xls]Sheet1!R2C3:R5000C19)-ROW('C:\Work\OTT_Pricing\OTT_Repl_Update\[" _
 & myCodeNum & "_Repl.xls]Sheet1!R2C3)+1))),MATCH(TRUE,INDEX('C:\Work\OTT_Pricing\OTT_Repl_Update\[" _
 & myCodeNum & "_Repl.xls]Sheet1!R2C3:R5000C19,MIN(IF('C:\Work\OTT_Pricing\OTT_Repl_Update\[" _
 & myCodeNum & "_Repl.xls]Sheet1!R2C1:R5000C1=RC3,IF('C:\Work\OTT_Pricing\OTT_Repl_Update\[" _
 & myCodeNum & "_Repl.xls]Sheet1R2C3:R5000C19>0,ROW('C:\Work\OTT_Pricing\OTT_Repl_Update\[" _
 & myCodeNum & "_Repl.xls]Sheet1!R2C3:R5000C19)-ROW('C:\Work\OTT_Pricing\OTT_Repl_Update\[" _
 & myCodeNum & "_Repl.xls]Sheet1!R2C3)+1))),0)>0,0))"
            
            .Cells(x, y).FormulaArray = myFormula
            
        End If
    Next x

End With
Application.DisplayAlerts = True

End Sub
 
Upvote 0

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sounds a bit like we're stretching resources even if we get it to work. I'd prefer to open the workbooks so the formulas calculate faster.

1) try at the top (under application.displayalerts = false):
Code:
Application.Calculation = xlCalculationManual

And at the bottom:
Code:
Application.Calculation = xlCalculationAutomatic

2) Open the workbooks while you run the code.

Code:
Code:
Workbooks(mypath).Open

The manual calculation should get the macro to run faster...then cross your fingers and see how fast it runs when you turn calculation back on. Your code could then close all the books when complete...

HTH ... not able to get into more detail here at work. :(

EDIT: ON SECOND THOUGHT, don't do the manual calculation right away...maybe better to see how its going one by one at first...add all the "speed up" elements when the rest is tested...
 
Last edited:
Upvote 0
Alex,

this gives run-time error '9': Subscript out of range.

Code:
Workbooks("C:\Work\OTT_Pricing\OTT_Repl_Update\1_Repl.xls").Open

I could not find a solution :(
This happens from what it looks like to office07
and I can't find how to fix it.

Regarding the calculation -- if workbooks are open -- it will calculate fine (at least when I did it by hand it worked), it takes about 5 min, so double that -- 10 min... I will not be running this very often, so it's ok, it can open and close workbooks and take its sweet time.
 
Upvote 0
Sorry :oops: Backwards, and leave off the parentheses in this one:

Code:
Workbooks[COLOR="Blue"].Open[/COLOR] "C:\Work\OTT_Pricing\OTT_Repl_Update\1_Repl.xls"

Alex
 
Upvote 0
Some more work on this. Opening and closing a bunch of workbooks is sometimes complicated so I've put in an array to hold the names, etc. Its looking a bit longish for a simple job - but the numerous workbooks are an extra challenge.

FYI if you ever want to try this "formula-wise" without VBA you would need to download an add-on called morefunc that lets you use the Indirect() formula on closed workbooks. Or of course, open all the workbooks first like we've done - which may be faster calculating anyway. (I'm hopeful this macro solution will work though).

Alex

Code:
Sub foo()
Dim myCodeNum As String
Dim myFormula As String
Dim ws As Worksheet
Dim LRow As Long, x As Long, y As Long
Dim a() As String
Dim i As Long
Dim myWBPath As String
Dim myWBName As String
Dim wb As Workbook
Dim intCount As Long

Const ROW_WHERE_FORMULA_STARTS As Long = 2 '//First formula will be in this row
Const COLUMN_TO_PUT_FORMULA_IN As Long = 7 '//Column Number to write formulas in..."A" = 1, "B" = 2 etc.
Const COLUMN_TO_DETERMINE_LAST_ROW_BY As Long = 3 '//Excel will fill formulas down as far as last cell in this column
Const COLUMN_WITH_CODE_NUMBERS As Long = 1 '//Column with number to determine Repl sheet number

'--------------------------------------------

Set ws = ActiveSheet
Application.DisplayAlerts = False

With ws
    '//FIRST PASS-----------------------------------------------------------
    LRow = .Cells(Rows.Count, COLUMN_TO_DETERMINE_LAST_ROW_BY).End(xlUp).Row
    y = COLUMN_TO_PUT_FORMULA_IN

    For x = ROW_WHERE_FORMULA_STARTS To LRow
        
        '//Get sheet number to use in formula
        myCodeNum = .Cells(x, COLUMN_WITH_CODE_NUMBERS).Value
        
        '//Get workbook Name to use in Formula
        myWBPath = "C:\Work\OTT_Pricing\OTT_Repl_Update\" & myCodeNum & "_Repl.xls"
        myWBName = FileNameOnly(myWBPath)
        
        '//Open workbook, capture its name (to close it later)
        If IsWorkbookOpen(myWBName) Then
            Set wb = Workbooks(myWBName)
        Else
            Set wb = Workbooks.Open(Filename:=myWBPath, ReadOnly:=True)
        End If
        myWBName = wb.Name
        ReDim Preserve a(0 To intCount)
        a(UBound(a)) = myWBName
        intCount = intCount + 1

        '//Create a formula string
        myFormula = "=INDEX([" & myWBName & "]Sheet1'!R1C5:R5000C5,MATCH(1,IF([" & myWBName & "]Sheet1'!R1C1:R5000C1=RC3,IF([" & myWBName & "]Sheet1'!R1C5:R5000C5<>0,1)),0))"

        '//write formula to cell
        .Cells(x, y).FormulaArray = myFormula
    
    Next x

    '//SECOND PASS - hard copy values----------------------------------------
    For x = ROW_WHERE_FORMULA_STARTS To LRow
        .Cells(x, y).Value = .Cells(x, y).Value
        If IsError(.Cells(x, y).Value) Then
            .Cells(x, y).ClearContents
        End If
    Next x
    
    '//THIRD PASS - do something with blank cells----------------------------
    For x = ROW_WHERE_FORMULA_STARTS To LRow
        If Len(.Cells(x, y).Value) = 0 Then
            
            '//Do Something with blank cell
            '//Do Something with blank cell
            myCodeNum = .Cells(x, COLUMN_WITH_CODE_NUMBERS).Value
            
        myFormula = "=INDEX('C:\Work\OTT_Pricing\OTT_Repl_Update\[" _
 & myCodeNum & "_Repl.xls]Sheet1!R2C3:R5000C19,MIN(IF('C:\Work\OTT_Pricing\OTT_Repl_Update\[" _
 & myCodeNum & "_Repl.xls]Sheet1!R2C1:R5000C1=RC3,IF('C:\Work\OTT_Pricing\OTT_Repl_Update\[" _
 & myCodeNum & "_Repl.xls]Sheet1!R2C3:R5000C19>0,ROW('C:\Work\OTT_Pricing\OTT_Repl_Update\[" _
 & myCodeNum & "_Repl.xls]Sheet1!R2C3:R5000C19)-ROW('C:\Work\OTT_Pricing\OTT_Repl_Update\[" _
 & myCodeNum & "_Repl.xls]Sheet1!R2C3)+1))),MATCH(TRUE,INDEX('C:\Work\OTT_Pricing\OTT_Repl_Update\[" _
 & myCodeNum & "_Repl.xls]Sheet1!R2C3:R5000C19,MIN(IF('C:\Work\OTT_Pricing\OTT_Repl_Update\[" _
 & myCodeNum & "_Repl.xls]Sheet1!R2C1:R5000C1=RC3,IF('C:\Work\OTT_Pricing\OTT_Repl_Update\[" _
 & myCodeNum & "_Repl.xls]Sheet1R2C3:R5000C19>0,ROW('C:\Work\OTT_Pricing\OTT_Repl_Update\[" _
 & myCodeNum & "_Repl.xls]Sheet1!R2C3:R5000C19)-ROW('C:\Work\OTT_Pricing\OTT_Repl_Update\[" _
 & myCodeNum & "_Repl.xls]Sheet1!R2C3)+1))),0)>0,0))"
            
            .Cells(x, y).FormulaArray = myFormula
            
        End If
    Next x

    '//close workbooks we opened
    For i = LBound(a) To UBound(a)
        Workbooks(a(i)).Close SaveChanges:=False
    Next i

End With
Application.DisplayAlerts = True

End Sub
'--------------------------------------------------
Function IsWorkbookOpen(strWorkbookName) As Boolean
Dim strTemp As String

On Error Resume Next
strTemp = Workbooks(strTemp).Name

If Err Then
    IsWorkbookOpen = False
Else
    IsWorkbookOpen = True
End If

End Function
'----------------------------------------------
Function FileNameOnly(Arg1 As String) As String
    FileNameOnly = _
        StrReverse(Left(StrReverse(Arg1), InStr(1, StrReverse(Arg1), "\") - 1))
End Function
 
Upvote 0
Alex, on the first pass when here

.Cells(x, y).FormulaArray = myFormula

it's unable to set FormulaArray property of the Range class. :(
 
Upvote 0
Alex, I dont know if that would help...
I have 13 workbooks that need to be open. Maybe it's easier to open them all first, and then use just Workbook name vs. going with myWBPath..
I dont know -- first formula worked well before, and if the second would work, all I need is to open 13 files and then close them without saving.

here is the code I have now..

I am having problem closing those workbooks..
what's the exact syntax?


Code:
Sub TRY()
Dim myCodeNum As String
Dim myFormula As String
Dim ws As Worksheet
Dim LRow As Long, x As Long, y As Long
Dim a() As String
Dim i As Long
Dim myWBPath As String
Dim myWBName As String
Dim wb As Workbook
Dim intCount As Long

Const ROW_WHERE_FORMULA_STARTS As Long = 2 '//First formula will be in this row
Const COLUMN_TO_PUT_FORMULA_IN As Long = 7 '//Column Number to write formulas in..."A" = 1, "B" = 2 etc.
Const COLUMN_TO_DETERMINE_LAST_ROW_BY As Long = 3 '//Excel will fill formulas down as far as last cell in this column
Const COLUMN_WITH_CODE_NUMBERS As Long = 1 '//Column with number to determine Repl sheet number

'--------------------------------------------

Set ws = ActiveSheet
Application.DisplayAlerts = False

With ws
    '//FIRST PASS-----------------------------------------------------------
    LRow = .Cells(Rows.Count, COLUMN_TO_DETERMINE_LAST_ROW_BY).End(xlUp).Row
    y = COLUMN_TO_PUT_FORMULA_IN

    For x = ROW_WHERE_FORMULA_STARTS To LRow
        
        '//Get sheet number to use in formula
        myCodeNum = .Cells(x, COLUMN_WITH_CODE_NUMBERS).Value
        
        '//Get workbook Name to use in Formula
        myWBPath = "C:\Work\OTT_Pricing\OTT_Repl_Update\" & myCodeNum & "_Repl.xls"
        myWBName = FileNameOnly(myWBPath)
        
        '//Open workbook, capture its name (to close it later)
        If IsWorkbookOpen(myWBName) Then
            Set wb = Workbooks(myWBName)
        Else
            Set wb = Workbooks.Open(Filename:=myWBPath, ReadOnly:=True)
        End If
        myWBName = wb.Name
        ReDim Preserve a(0 To intCount)
        a(UBound(a)) = myWBName
        intCount = intCount + 1

        '//Create a formula string
        myFormula = "=INDEX([" & myWBName & "]Sheet1'!R1C5:R5000C5,MATCH(1,IF([" & myWBName & "]Sheet1'!R1C1:R5000C1=RC3,IF([" & myWBName & "]Sheet1'!R1C5:R5000C5<>0,1)),0))"

        '//write formula to cell
        .Cells(x, y).FormulaArray = myFormula
    
    Next x

    '//SECOND PASS - hard copy values----------------------------------------
    For x = ROW_WHERE_FORMULA_STARTS To LRow
        .Cells(x, y).Value = .Cells(x, y).Value
        If IsError(.Cells(x, y).Value) Then
            .Cells(x, y).ClearContents
        End If
    Next x
    
    '//THIRD PASS - do something with blank cells----------------------------
    For x = ROW_WHERE_FORMULA_STARTS To LRow
        If Len(.Cells(x, y).Value) = 0 Then
            
            '//Do Something with blank cell
            '//Do Something with blank cell
            myCodeNum = .Cells(x, COLUMN_WITH_CODE_NUMBERS).Value
            
        myFormula = "=INDEX([" & myWBName & "]Sheet1!R2C3:R5000C19,MIN(IF([" & myWBName & "]Sheet1!R2C1:R5000C1=RC3,IF([" & myWBName & "]Sheet1!R2C3:R5000C19>0,ROW([" & myWBName & "]Sheet1!R2C3:R5000C19)-ROW([" & myWBName & "]Sheet1!R2C3)+1))),MATCH(TRUE,INDEX([" & myWBName & "]Sheet1!R2C3:R5000C19,MIN(IF([" & myWBName & "]Sheet1!R2C1:R5000C1=RC3,IF([" & myWBName & "]Sheet1R2C3:R5000C19>0,ROW([" & myWBName & "]Sheet1!R2C3:R5000C19)-ROW([" & myWBName & "]Sheet1!R2C3)+1))),0)>0,0))"
            
            .Cells(x, y).FormulaArray = myFormula
            
        End If
    Next x

    '//close workbooks we opened
    For i = LBound(a) To UBound(a)
        Workbooks(a(i)).Close SaveChanges:=False
    Next i

End With
Application.DisplayAlerts = True

End Sub
'--------------------------------------------------
Function IsWorkbookOpen(strWorkbookName) As Boolean
Dim strTemp As String

On Error Resume Next
strTemp = Workbooks(strTemp).Name

If Err Then
    IsWorkbookOpen = False
Else
    IsWorkbookOpen = True
End If

End Function
'----------------------------------------------
Function FileNameOnly(Arg1 As String) As String
    FileNameOnly = _
        StrReverse(Left(StrReverse(Arg1), InStr(1, StrReverse(Arg1), "\") - 1))
End Function
 
Upvote 0
Hi, sorry looks like we are getting a lot of bugs. Here's a quick fix and see if it changes anything:


The bright blue below is a syntax change...might help.

The green colored text - can you just plug in your old formula instead? I was trying to shorten that but maybe screwed it up. It could just be a bad formula.

Code:
Sub TRY()
Dim myCodeNum As String
Dim myFormula As String
Dim ws As Worksheet
Dim LRow As Long, x As Long, y As Long
Dim a() As String
Dim i As Long
Dim myWBPath As String
Dim myWBName As String
Dim wb As Workbook
Dim intCount As Long

Const ROW_WHERE_FORMULA_STARTS As Long = 2 '//First formula will be in this row
Const COLUMN_TO_PUT_FORMULA_IN As Long = 7 '//Column Number to write formulas in..."A" = 1, "B" = 2 etc.
Const COLUMN_TO_DETERMINE_LAST_ROW_BY As Long = 3 '//Excel will fill formulas down as far as last cell in this column
Const COLUMN_WITH_CODE_NUMBERS As Long = 1 '//Column with number to determine Repl sheet number

'--------------------------------------------

Set ws = ActiveSheet
Application.DisplayAlerts = False

With ws
    '//FIRST PASS-----------------------------------------------------------
    LRow = .Cells(Rows.Count, COLUMN_TO_DETERMINE_LAST_ROW_BY).End(xlUp).Row
    y = COLUMN_TO_PUT_FORMULA_IN

    For x = ROW_WHERE_FORMULA_STARTS To LRow
        
        '//Get sheet number to use in formula
        myCodeNum = .Cells(x, COLUMN_WITH_CODE_NUMBERS).Value
        
        '//Get workbook Name to use in Formula
        myWBPath = "C:\Work\OTT_Pricing\OTT_Repl_Update\" & myCodeNum & "_Repl.xls"
        myWBName = FileNameOnly(myWBPath)
        
        '//Open workbook, capture its name (to close it later)
        If IsWorkbookOpen(myWBName) Then
            Set wb = Workbooks(myWBName)
        Else
[COLOR="Blue"]            Workbooks.Open Filename:=myWBPath, ReadOnly:=True
            Set wb = ActiveWorkbook[/COLOR]
        End If
        myWBName = wb.Name
        ReDim Preserve a(0 To intCount)
        a(UBound(a)) = myWBName
        intCount = intCount + 1

        '//Create a formula string
[COLOR="Green"]        myFormula = "=INDEX([" & myWBName & "]Sheet1'!R1C5:R5000C5,MATCH(1,IF([" & myWBName & "]Sheet1'!R1C1:R5000C1=RC3,IF([" & myWBName & "]Sheet1'!R1C5:R5000C5<>0,1)),0))"[/COLOR]

        
[COLOR="Blue"]        '//Look at it (in the immediate window) - for debugging purposes
        debug.print myFormula[/COLOR]

        '//write formula to cell
        .Cells(x, y).FormulaArray = myFormula
    
    Next x

    '//SECOND PASS - hard copy values----------------------------------------
    For x = ROW_WHERE_FORMULA_STARTS To LRow
        .Cells(x, y).Value = .Cells(x, y).Value
        If IsError(.Cells(x, y).Value) Then
            .Cells(x, y).ClearContents
        End If
    Next x
    
    '//THIRD PASS - do something with blank cells----------------------------
    For x = ROW_WHERE_FORMULA_STARTS To LRow
        If Len(.Cells(x, y).Value) = 0 Then
            
            '//Do Something with blank cell
            '//Do Something with blank cell
            myCodeNum = .Cells(x, COLUMN_WITH_CODE_NUMBERS).Value
            
        myFormula = "=INDEX([" & myWBName & "]Sheet1!R2C3:R5000C19,MIN(IF([" & myWBName & "]Sheet1!R2C1:R5000C1=RC3,IF([" & myWBName & "]Sheet1!R2C3:R5000C19>0,ROW([" & myWBName & "]Sheet1!R2C3:R5000C19)-ROW([" & myWBName & "]Sheet1!R2C3)+1))),MATCH(TRUE,INDEX([" & myWBName & "]Sheet1!R2C3:R5000C19,MIN(IF([" & myWBName & "]Sheet1!R2C1:R5000C1=RC3,IF([" & myWBName & "]Sheet1R2C3:R5000C19>0,ROW([" & myWBName & "]Sheet1!R2C3:R5000C19)-ROW([" & myWBName & "]Sheet1!R2C3)+1))),0)>0,0))"
            
            .Cells(x, y).FormulaArray = myFormula
            
        End If
    Next x

    '//close workbooks we opened
    For i = LBound(a) To UBound(a)
        Workbooks(a(i)).Close SaveChanges:=False
    Next i

End With
Application.DisplayAlerts = True

End Sub
'--------------------------------------------------
Function IsWorkbookOpen(strWorkbookName) As Boolean
Dim strTemp As String

On Error Resume Next
strTemp = Workbooks(strTemp).Name

If Err Then
    IsWorkbookOpen = False
Else
    IsWorkbookOpen = True
End If

End Function
'----------------------------------------------
Function FileNameOnly(Arg1 As String) As String
    FileNameOnly = _
        StrReverse(Left(StrReverse(Arg1), InStr(1, StrReverse(Arg1), "\") - 1))
End Function
 
Last edited:
Upvote 0
Code:
Workbooks.Open Filename:=myWBPath, ReadOnly:=True
            Set wb = ActiveWorkbook

after this code it tries to apply the formula to the Active Workbook which is 1_Repl.xls that just opened up.

the sheet we are working in not active once the other get opened.
 
Upvote 0
If I have

Code:
ChDir "C:\Work\OTT_Pricing\OTT_Repl_Update"
    Workbooks.Open Filename:="C:\Work\OTT_Pricing\OTT_Repl_Update\1_Repl.xls"
    Workbooks.Open Filename:="C:\Work\OTT_Pricing\OTT_Repl_Update\2_Repl.xls"
    Workbooks.Open Filename:="C:\Work\OTT_Pricing\OTT_Repl_Update\3_Repl.xls"

How do I close those?
Not using ActiveWorkbook.Close ?
I need to specify to close exactly the ones listed.
 
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top