Microsoft Excel Cannot access file while running a macro.

[finAnalysis]

When I run the following macro from some workbooks it says it cannot access the original file it was created in. However, I have this macro saved in my Personal.XLSB so that it can be accessed from any workbook. What am I missing?

Sub Deal_Logv4()
'
' Deal_Logv4 Macro
'

Dim Deal_Log As Variant
Dim ws As Worksheet
Dim wb As Workbook
Dim UsdRws As Long
Sheets("Deal Log").Select
UsdRws = Range("G:G").Find("*", , xlValues, , xlByRows, xlPrevious, , , False).Row
Range("A2:AB" & UsdRws).Copy
'The range needs to be moveable. Currently every time I add a column I need to change this.'

MsgBox ("Select Deal Log from this years folders")
'This will direct the user to select the Deal log that way the folder can be moved and this doesn't break'

Deal_Log = Application.GetOpenFilename(FileFilter:="Excel Files,*.xl*;*.xm*")
If Deal_Log <> False Then
Workbooks.Open Filename:=Deal_Log
End If

Sheets("All Data").Select
'If headers change and tab names change this argument and the next one will break'

Range("G4").End(xlDown).Select
ActiveCell.Offset(1).Select
'Moves down one cell for the next time the macro is run'

Selection.End(xlToLeft).Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Close savechanges:=True

MsgBox ("Deal Log has been updated!")
End Sub

 

[finAnalysis]

When I run the following macro from some workbooks it says it cannot access the original file it was created in. However, I have this macro saved in my Personal.XLSB so that it can be accessed from any workbook. What am I missing?

View attachment 56282

Sub Deal_Logv4()
'
' Deal_Logv4 Macro
'

Dim Deal_Log As Variant
Dim ws As Worksheet
Dim wb As Workbook
Dim UsdRws As Long
Sheets("Deal Log").Select
UsdRws = Range("G:G").Find("*", , xlValues, , xlByRows, xlPrevious, , , False).Row
Range("A2:AB" & UsdRws).Copy
'The range needs to be moveable. Currently every time I add a column I need to change this.'

MsgBox ("Select Deal Log from this years folders")
'This will direct the user to select the Deal log that way the folder can be moved and this doesn't break'

Deal_Log = Application.GetOpenFilename(FileFilter:="Excel Files,*.xl*;*.xm*")
If Deal_Log <> False Then
Workbooks.Open Filename:=Deal_Log
End If

Sheets("All Data").Select
'If headers change and tab names change this argument and the next one will break'

Range("G4").End(xlDown).Select
ActiveCell.Offset(1).Select
'Moves down one cell for the next time the macro is run'

Selection.End(xlToLeft).Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Close savechanges:=True

MsgBox ("Deal Log has been updated!")
End Sub

SOLVED!

 

[smozgur]

Good to hear you got the solution.

If you would like to post the solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.

 

[finAnalysis]

Good to hear you got the solution.

If you would like to post the solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.

The problem was that I had the macro running from a workbook that had moved locations even though I thought I had it saved to my Personal.xlsb. I solved it by moving the macro to my Personal.xlsb so that I could run it without fear of moving/renaming my files.