VBA Code - Not working when another user uses the macro

M

MHamid

Active Member
Joined
Jan 31, 2013
Messages
472
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hello,

I created a macro/vba code that works perfectly in my excel. However, when I copy the code and paste it into another user's excel, the save function does not work.
Does someone have any ideas as to why this is happening?

The other users have the same version of Excel that I have, which is 2010.

Below is the part of the code where the macro stopped working. Any assistance would be greatly appreciated.

Code: 
Sub DailyCases()
'
' DailyCases Macro
   
    Rows("1:3").Delete Shift:=xlUp
    Columns("D:D").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("D1").FormulaR1C1 = "BU"
    Range("D2").Select
    
    Sheets("Vendor Details Sheet").Select
    Rows("1:3").Delete Shift:=xlUp
    
    Sheets("DDR Details Sheet").Select
     
    Dim lRow1 As Long
    Dim lRow2 As Long
    lRow1 = Sheets("DDR Details Sheet").Range("A" & Rows.Count).End(xlUp).Row
    lRow2 = Sheets("Vendor Details Sheet").Range("A" & Rows.Count).End(xlUp).Row
    
    Range("D2:D" & lRow1).FormulaR1C1 = "=IF((OR(RC[-1]=""USPB HNW"",RC[-1]=""USPB UHNW"")),""USPB"",IF((OR(RC[-1]=""INTERNATIONAL PRIVATE BANKING"")),""LATAM"",IF((OR(RC[-1]<>""USPB HNW"",RC[-1]<>""USPB UHNW"",RC[-1]<>""INTERNATIONAL PRIVATE BANKING"")),""NON-GWM"","""")))"
    Columns("D:D").EntireColumn.AutoFit
    Range("B3").Select
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$BO" & lRow1), , xlYes).Name _
        = "Table1"
    Range("A1:BO" & lRow1).Select
    ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleMedium6"
    
    Sheets("Vendor Details Sheet").Select
    Columns("A").TextToColumns
    Columns("B:C").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("B1").FormulaR1C1 = "Received Date"
    Range("C1").FormulaR1C1 = "Business Unit"
    
    Range("B2").FormulaR1C1 = "=VLOOKUP(RC[-1],'DDR Details Sheet'!C1:C56,56,0)"
    Range("B2:B" & lRow2).FillDown
    Columns("B:B").NumberFormat = "m/d/yyyy"
    With Columns("B:B")
        .HorizontalAlignment = xlCenter
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    
    Range("C2").FormulaR1C1 = "=VLOOKUP(RC[-2],'DDR Details Sheet'!C1:C4,4,0)"
    Range("C2:C" & lRow2).FillDown
    With Columns("C:C")
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlTop
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("C2").Select
    
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$K" & lRow2), , xlYes).Name _
        = "Table2"
    Range("A1:K" & lRow2).Select
    ActiveSheet.ListObjects("Table2").TableStyle = "TableStyleMedium6"
    Range("B5").Select
    
    ActiveWorkbook.SaveAs Filename:= _
    "J:\NARESHARE04\CORP_SEC\DUE DILIGENCE\Intake & Assessment\Referencing Team\Daily Cases Reporting\" & "Daily Cases Reporting" & Format(Now(), "mm-dd-yy_hhmmAMPM") & ".xlsx" _
    , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

    ActiveWorkbook.Close
    Workbooks.Open ("J:\NARESHARE04\CORP_SEC\DUE DILIGENCE\Intake & Assessment\Referencing Team\Daily Cases Reporting\" & "Daily Cases Reporting" & Format(Now(), "mm-dd-yy_hhmmAMPM") & ".xlsx")

    Sheets("DDR Details Sheet").Select
    Range("A2").Select
    ActiveWindow.SmallScroll Down:=-15
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Table1", Version:=xlPivotTableVersion14).CreatePivotTable TableDestination _
        :="Sheet1!R3C1", TableName:="PivotTable9", DefaultVersion:= _
        xlPivotTableVersion14
    Sheets("Sheet1").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable9").PivotFields("Case Level Research")
        .Orientation = xlPageField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable9").PivotFields("Case Level Research"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable9").PivotFields("Case Level Research"). _
        CurrentPage = "Full"
    With ActiveSheet.PivotTables("PivotTable9").PivotFields("BU")
        .Orientation = xlColumnField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable9").PivotFields("Case Status")
        .Orientation = xlColumnField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("PivotTable9").PivotFields("Date Received")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable9").AddDataField ActiveSheet.PivotTables( _
        "PivotTable9").PivotFields("Case Number"), "Sum of Case Number", xlSum
    With ActiveSheet.PivotTables("PivotTable9").PivotFields("Sum of Case Number")
        .Caption = "Count of Case Number"
        .Function = xlCount
    End With
    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "BU Daily Pivot"
    Sheets("BU Daily Pivot").Select
    With ActiveWorkbook.Sheets("BU Daily Pivot").Tab
        .Color = 12611584
        .TintAndShade = 0
    End With
    Sheets("Vendor Details Sheet").Select
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Table2", Version:=xlPivotTableVersion14).CreatePivotTable TableDestination _
        :="Sheet2!R3C1", TableName:="PivotTable10", DefaultVersion:= _
        xlPivotTableVersion14
    Sheets("Sheet2").Select
    Cells(3, 1).Select
    Sheets("Sheet2").Select
    Sheets("Sheet2").Move Before:=Sheets(2)
    With ActiveSheet.PivotTables("PivotTable10").PivotFields("Vendor Name ")
        .Orientation = xlColumnField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable10").PivotFields("Business Unit")
        .Orientation = xlColumnField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("PivotTable10").PivotFields("Received Date")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable10").AddDataField ActiveSheet.PivotTables( _
        "PivotTable10").PivotFields("Case Number"), "Sum of Case Number", xlSum
    With ActiveSheet.PivotTables("PivotTable10").PivotFields("Sum of Case Number")
        .Caption = "Count of Case Number"
        .Function = xlCount
    End With
    Sheets("Sheet2").Select
    Sheets("Sheet2").Name = "Assist Daily Pivot"
    Sheets("Assist Daily Pivot").Select
    With ActiveWorkbook.Sheets("Assist Daily Pivot").Tab
        .Color = 10498160
        .TintAndShade = 0
    End With
    ActiveWorkbook.ShowPivotTableFieldList = False
    Sheets("BU Daily Pivot").Select
    ActiveSheet.PivotTables("PivotTable9").PivotSelect "'Date Received'[All]", _
        xlLabelOnly + xlFirstRow, True
    Range("B7").Select
    Sheets.Add
    Sheets("Sheet3").Select
    Sheets("Sheet3").Name = "Dashboard"
    Sheets("Dashboard").Select
    With ActiveWorkbook.Sheets("Dashboard").Tab
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = 0.399975585192419
    End With
    Sheets("BU Daily Pivot").Select

    Sheets("BU Daily Pivot").Select
    Range("D10").Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlBarClustered
    ActiveChart.SetSourceData Source:=Range("'BU Daily Pivot'!$A$3:$Q$36")
    ActiveChart.Parent.Cut
    Sheets("Dashboard").Select
    Range("I5").Select
    ActiveSheet.Paste
    ActiveWindow.Zoom = 85
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveSheet.Shapes("Chart 1").IncrementLeft 145.5881889764
    ActiveSheet.Shapes("Chart 1").IncrementTop -0.8823622047
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveSheet.Shapes("Chart 1").ScaleWidth 1.4585389585, msoFalse, _
        msoScaleFromTopLeft
    ActiveSheet.Shapes("Chart 1").ScaleHeight 1.2331649964, msoFalse, _
        msoScaleFromTopLeft
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.ShowAllFieldButtons = False
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.Axes(xlValue).MajorGridlines.Select
    Selection.Delete
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.Axes(xlValue).Select
    Selection.Delete
    ActiveWorkbook.SlicerCaches.Add(Sheets("BU Daily Pivot").PivotTables("PivotTable9"), "BU"). _
        Slicers.Add ActiveSheet, , "BU", "BU", 197.25, 429, 144, 187.5
    ActiveWorkbook.SlicerCaches.Add(Sheets("BU Daily Pivot").PivotTables("PivotTable9"), _
        "Case Status").Slicers.Add ActiveSheet, , "Case Status", "Case Status", 234.75 _
        , 466.5, 144, 187.5
    ActiveWorkbook.SlicerCaches.Add(Sheets("BU Daily Pivot").PivotTables("PivotTable9"), _
        "Case Sub-status").Slicers.Add ActiveSheet, , "Case Sub-status", _
        "Case Sub-status", 272.25, 504, 144, 187.5
    ActiveWorkbook.SlicerCaches.Add(Sheets("BU Daily Pivot").PivotTables("PivotTable9"), _
        "Date Received").Slicers.Add ActiveSheet, , "Date Received", "Date Received", _
        309.75, 541.5, 144, 187.5
    ActiveSheet.Shapes.Range(Array("Date Received")).Select
    ActiveSheet.Shapes.Range(Array("BU")).Select
    ActiveSheet.Shapes("BU").IncrementLeft -378.5293700787
    ActiveSheet.Shapes("BU").IncrementTop -147.352992126
    ActiveWorkbook.SlicerCaches("Slicer_BU").Slicers("BU").NumberOfColumns = 2
    ActiveWorkbook.SlicerCaches("Slicer_BU").Slicers("BU").NumberOfColumns = 3
    ActiveWorkbook.SlicerCaches("Slicer_BU").Slicers("BU").NumberOfColumns = 4
    ActiveWorkbook.SlicerCaches("Slicer_BU").Slicers("BU").NumberOfColumns = 3
    ActiveSheet.Shapes("BU").ScaleWidth 1.4632349081, msoFalse, msoScaleFromTopLeft
    ActiveSheet.Shapes("BU").ScaleHeight 0.2609415223, msoFalse, _
        msoScaleFromTopLeft
    ActiveSheet.Shapes.Range(Array("Date Received")).Select
    ActiveSheet.Shapes("Date Received").IncrementLeft -492.3529133858
    ActiveSheet.Shapes("Date Received").IncrementTop -206.4705511811
    ActiveSheet.Shapes("Date Received").ScaleWidth 1.9564949694, msoFalse, _
        msoScaleFromTopLeft
    ActiveWorkbook.SlicerCaches("Slicer_Date_Received").Slicers("Date Received"). _
        NumberOfColumns = 2
    ActiveWorkbook.SlicerCaches("Slicer_Date_Received").Slicers("Date Received"). _
        NumberOfColumns = 3
    ActiveWorkbook.SlicerCaches("Slicer_Date_Received").Slicers("Date Received"). _
        NumberOfColumns = 4
    ActiveSheet.Shapes("Date Received").ScaleHeight 1.0397644094, msoFalse, _
        msoScaleFromTopLeft
    ActiveSheet.Shapes.Range(Array("Case Status")).Select
    ActiveSheet.Shapes("Case Status").IncrementLeft -130.5882677165
    ActiveSheet.Shapes("Case Status").IncrementTop -184.4117322835
    ActiveWorkbook.SlicerCaches("Slicer_Case_Status").Slicers("Case Status"). _
        NumberOfColumns = 2
    ActiveSheet.Shapes("Case Status").ScaleWidth 1.2702209099, msoFalse, _
        msoScaleFromTopLeft
    ActiveSheet.Shapes("Case Status").ScaleHeight 0.3668233071, msoFalse, _
        msoScaleFromTopLeft
    Columns("A:A").ColumnWidth = 1.57
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveSheet.Shapes("Chart 1").IncrementLeft 36.1764566929
    ActiveSheet.Shapes("Chart 1").IncrementTop 0.8823622047
    ActiveSheet.Shapes.Range(Array("Case Sub-status")).Select
    ActiveSheet.Shapes("Case Sub-status").IncrementLeft -165
    ActiveSheet.Shapes("Case Sub-status").IncrementTop -146.4706299213
    ActiveSheet.Shapes("Case Sub-status").ScaleWidth 1.4816174541, msoFalse, _
        msoScaleFromTopLeft
    ActiveWorkbook.SlicerCaches("Slicer_Case_Sub_status").Slicers("Case Sub-status" _
        ).NumberOfColumns = 2
    ActiveSheet.Shapes("Case Sub-status").ScaleHeight 0.5150588976, msoFalse, _
        msoScaleFromTopLeft
    ActiveSheet.Shapes.Range(Array("BU")).Select
    ActiveSheet.Shapes.Range(Array("BU", "Date Received")).Select
    ActiveSheet.Shapes.Range(Array("BU", "Date Received", "Case Sub-status")). _
        Select
    ActiveSheet.Shapes.Range(Array("BU", "Date Received", "Case Sub-status", _
        "Case Status")).Select
    ActiveWorkbook.SlicerCaches("Slicer_BU").Slicers("BU").Style = _
        "SlicerStyleLight2"
    ActiveWorkbook.SlicerCaches("Slicer_Date_Received").Slicers("Date Received"). _
        Style = "SlicerStyleLight2"
    ActiveWorkbook.SlicerCaches("Slicer_Case_Sub_status").Slicers("Case Sub-status" _
        ).Style = "SlicerStyleLight2"
    ActiveWorkbook.SlicerCaches("Slicer_Case_Status").Slicers("Case Status").Style _
        = "SlicerStyleLight2"
    
    Range("B2:W3").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    Selection.Font.Bold = True
    ActiveCell.FormulaR1C1 = "DAILY CASES BY BUSINESS UNIT"
    Range("B2:W3").Select
    Selection.Font.Size = 11
    Selection.Font.Size = 12
    Selection.Font.Size = 14
    Selection.Font.Size = 16
    Selection.Font.Size = 18
    Selection.Font.Size = 20
    Selection.Font.Size = 22
    Selection.Font.Size = 24
    Selection.Font.Size = 26
    Selection.Font.Size = 24
    Range("I29:J29").Select
    Sheets("Assist Daily Pivot").Select
    Range("F14").Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlBarClustered
    ActiveChart.SetSourceData Source:=Range("'Assist Daily Pivot'!$A$3:$M$33")
    ActiveChart.SetElement (msoElementDataLabelOutSideEnd)
    ActiveChart.Parent.Cut
    Sheets("Dashboard").Select
    Range("M31").Select
    ActiveSheet.Paste
    ActiveSheet.ChartObjects("Chart 6").Activate
    ActiveSheet.Shapes("Chart 6").ScaleWidth 1.4411765092, msoFalse, _
        msoScaleFromTopLeft
    ActiveSheet.Shapes("Chart 6").ScaleHeight 1.2336599592, msoFalse, _
        msoScaleFromTopLeft
    ActiveSheet.ChartObjects("Chart 6").Activate
    ActiveChart.ShowAllFieldButtons = False
    ActiveSheet.ChartObjects("Chart 6").Activate
    ActiveChart.Axes(xlValue).MajorGridlines.Select
    Selection.Delete
    ActiveSheet.ChartObjects("Chart 6").Activate
    ActiveChart.Axes(xlValue).Select
    Selection.Delete
    ActiveSheet.ChartObjects("Chart 6").Activate
    ActiveSheet.Shapes("Chart 6").IncrementLeft -7.9411023622
    ActiveSheet.Shapes("Chart 6").IncrementTop 22.9411811024
    Rows("2:4").Select
    Selection.Copy
    Rows("2:3").Select
    Application.CutCopyMode = False
    Selection.Copy
    Rows("30:30").Select
    ActiveSheet.Paste
    Range("B30:W31").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "DAILY ASSIST CASES"
    Range("J37").Select
    ActiveSheet.ChartObjects("Chart 6").Activate
    ActiveChart.PlotArea.Select
    ActiveChart.ChartArea.Select
    ActiveWorkbook.SlicerCaches.Add(Sheets("Assist Daily Pivot").PivotTables("PivotTable10"), _
        "Received Date").Slicers.Add ActiveSheet, , "Received Date", "Received Date", _
        216, 447.75, 144, 187.5
    ActiveWorkbook.SlicerCaches.Add(Sheets("Assist Daily Pivot").PivotTables("PivotTable10"), _
        "Business Unit").Slicers.Add ActiveSheet, , "Business Unit", "Business Unit", _
        253.5, 485.25, 144, 187.5
    ActiveWorkbook.SlicerCaches.Add(Sheets("Assist Daily Pivot").PivotTables("PivotTable10"), _
        "Vendor Name ").Slicers.Add ActiveSheet, , "Vendor Name ", "Vendor Name ", 291 _
        , 522.75, 144, 187.5
    ActiveSheet.Shapes.Range(Array("Vendor Name ")).Select
    ActiveSheet.Shapes.Range(Array("Received Date")).Select
    ActiveSheet.Shapes("Received Date").IncrementLeft -435
    ActiveSheet.Shapes("Received Date").IncrementTop 173.8235433071
    ActiveWorkbook.SlicerCaches("Slicer_Received_Date").Slicers("Received Date"). _
        NumberOfColumns = 2
    ActiveWorkbook.SlicerCaches("Slicer_Received_Date").Slicers("Received Date"). _
        NumberOfColumns = 3
    ActiveWorkbook.SlicerCaches("Slicer_Received_Date").Slicers("Received Date"). _
        NumberOfColumns = 4
    ActiveSheet.Shapes("Received Date").ScaleWidth 1.9641546369, msoFalse, _
        msoScaleFromTopLeft
    ActiveSheet.Shapes("Received Date").ScaleHeight 0.9327059318, msoFalse, _
        msoScaleFromTopLeft
    ActiveSheet.Shapes.Range(Array("Business Unit")).Select
    ActiveSheet.Shapes("Business Unit").IncrementLeft -178.2352755906
    ActiveSheet.Shapes("Business Unit").IncrementTop 137.6470866142
    ActiveWorkbook.SlicerCaches("Slicer_Business_Unit").Slicers("Business Unit"). _
        NumberOfColumns = 2
    ActiveSheet.Shapes("Business Unit").ScaleWidth 1.1829040901, msoFalse, _
        msoScaleFromTopLeft
    ActiveSheet.Shapes("Business Unit").ScaleHeight 0.3609411024, msoFalse, _
        msoScaleFromTopLeft
    ActiveSheet.Shapes.Range(Array("Vendor Name ")).Select
    ActiveSheet.Shapes("Vendor Name ").IncrementLeft -216.1765354331
    ActiveSheet.Shapes("Vendor Name ").IncrementTop 176.4706299213
    ActiveWorkbook.SlicerCaches("Slicer_Vendor_Name").Slicers("Vendor Name "). _
        NumberOfColumns = 2
    ActiveWorkbook.SlicerCaches("Slicer_Vendor_Name").Slicers("Vendor Name "). _
        NumberOfColumns = 3
    ActiveWorkbook.SlicerCaches("Slicer_Vendor_Name").Slicers("Vendor Name "). _
        NumberOfColumns = 2
    ActiveSheet.Shapes("Vendor Name ").IncrementLeft -0.8822834646
    ActiveSheet.Shapes("Vendor Name ").IncrementTop -1.7647244094
    ActiveSheet.Shapes("Vendor Name ").ScaleWidth 1.4617033027, msoFalse, _
        msoScaleFromTopLeft
    ActiveSheet.Shapes("Vendor Name ").ScaleWidth 0.8365127753, msoFalse, _
        msoScaleFromTopLeft
    ActiveSheet.Shapes("Vendor Name ").ScaleWidth 1.0701576604, msoFalse, _
        msoScaleFromTopLeft
    ActiveSheet.Shapes("Vendor Name ").ScaleHeight 0.528, msoFalse, _
        msoScaleFromTopLeft
    ActiveSheet.Shapes("Vendor Name ").ScaleWidth 1.121751329, msoFalse, _
        msoScaleFromTopLeft
    Range("B30:W31").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With
    Range("B2:W3").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With
    Range("K52").Select
    
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SetElement (msoElementDataLabelOutSideEnd)
    
    ActiveWindow.DisplayHeadings = False
    ActiveWindow.DisplayGridlines = False
    ActiveWorkbook.Save
End Sub

Thank you,
Miriam
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
Sorry, I just realized that I added the whole code. I meant to only mention the area where the code is not working. Which is:

Code: 
    ActiveWorkbook.SaveAs Filename:= _
    "J:\NARESHARE04\CORP_SEC\DUE DILIGENCE\Intake & Assessment\Referencing Team\Daily Cases Reporting\" & "Daily Cases Reporting" & Format(Now(), "mm-dd-yy_hhmmAMPM") & ".xlsx" _
    , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
 
Upvote 0
Does the other user have the EXACT same drive mappings and permissions as you (meaning can they access and write to "J:\NARESHARE04\CORP_SEC\DUE DILIGENCE\Intake & Assessment\Referencing Team\Daily Cases Reporting\")? I know in our office, users often have different drive letter mappings.

If that is not the issue, please tell us exactly what is happening. Are you getting an error message? If so, what does it say?
 
Upvote 0
Hello,

Yes, that was the error. Mine is mapped the way it is in the code, but looks like my co-worker has it mapped as "J:\DUE DILIGENCE\Intake & Assessment\Referencing Team\Daily Cases Reporting\".

Is there a way create a code that will go to the other directory if the first one is not found?


Thank you,
Miriam
 
Upvote 0
If it is mapped to a network drive, you can use the UNC naming convention (i.e. what J is actually mapped to). It would look something like:
Code: 
[COLOR=#333333]"\\servermame\subfoldername\DUE DILIGENCE\Intake & Assessment\Referencing Team\Daily Cases Reporting\"[/COLOR]
You may not have a subfolder, or you may have multiple sub-folders before "DUE DILIGENCE". It all depends how far in the server that J is mapped.
 
Upvote 0
Hello,

I have located the UNC path and changed the code to include the UNC path.
However, now I am getting an error code "Compile Error: Syntax error" at the location where I need the file to open up again after it has been closed.
Code: 
    Workbooks.Open (\\NAEAST.AD.JPMORGANCHASE.COM\AMERCORP$\CORPRE\NARESHARE04\CORP_SEC\DUE DILIGENCE\Intake & Assessment\Referencing Team\Daily Cases Reporting\" & "Daily Cases Reporting" & Format(Now(), "mm-dd-yy_hhmmAMPM") & ".xlsx")

What is causing this error now?

Thank you,
Miriam
 
Upvote 0
Never mind, I see my mistake. I forgot to add the open quotation mark prior to \\NAEAST.
It works now. Thank you.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Changing recorded macro to have dynamic selection of cells when trying to format a sheet.
Replies
4
Views
600
Michael M
Michael M
G
  • Solved
Insert Table and slicer multiple sheets except a particular one.
Replies
5
Views
556
DanteAmor
DanteAmor
X
  • Question
How to get range of dynamic data for Pivot Table SourceData?
Replies
1
Views
604
Alex Blakenburg
A
2
  • Question
button location
Replies
3
Views
639
GWteB
GWteB
E
  • Question
Can anyone help me get my VBA code working? I have spent tons of time but can't quite fix it.
Replies
3
Views
2K
profray
P

Forum statistics

Threads
1,214,830
Messages
6,121,835
Members
449,051
Latest member
excelquestion515

Share this page

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