I am getting the following error when I run the macro below on line:
sh.Cells(1, 1) = "Path:"

Run-Time error '-2147221080(800401A8)':
Automation Error

Does anyone have an idea of what might be going on. This macro has ran
for 2 years without a problem. Thanks!


Sub getRMFlist()


Dim sh As Worksheet, lstAttr As Integer
Dim mypath As String, myName As String
Dim rw As Integer, fattr, strAttr As String

'goto worksheet
Sheets("RMF File List").Select

'set a variable to refer to the active sheet in this workbook
Set sh = ActiveSheet
'set key values
lstAttr = vbNormal + vbReadOnly + vbHidden

lstAttr = lstAttr + vbSystem + vbDirectory
lstAttr = lstAttr + vbArchive

Sheets("RMF File List").Select
Application.DisplayAlerts = False
Sheets("RMF File List").Delete
Sheets.Add.Name = "RMF File List"
Range("A1").Select


Sheets("Input").Select
Range("d10").Select
curdirloc = Selection.Value
Sheets("RMF File List").Select

' Set the directory to be analyzed
mypath = curdirloc
' Retrieve the first entry.
myName = Dir(mypath, lstAttr)
'put labels on the sheet at the top of the columns
sh.Cells(1, 1) = "Path:"

I am not positive, but it may have to do with Set sh = Activesheet and then
deleting that sheet and creating a new sheet with the same name. Perhaps use
Set sh = Worksheets("RMF File List").

Mike F

"mburkett" wrote in message
oups.com...
I am getting the following error when I run the macro below on line:
sh.Cells(1, 1) = "Path:"

Run-Time error '-2147221080(800401A8)':
Automation Error

Does anyone have an idea of what might be going on. This macro has ran
for 2 years without a problem. Thanks!


Sub getRMFlist()


Dim sh As Worksheet, lstAttr As Integer
Dim mypath As String, myName As String
Dim rw As Integer, fattr, strAttr As String

'goto worksheet
Sheets("RMF File List").Select

'set a variable to refer to the active sheet in this workbook
Set sh = ActiveSheet
'set key values
lstAttr = vbNormal + vbReadOnly + vbHidden

lstAttr = lstAttr + vbSystem + vbDirectory
lstAttr = lstAttr + vbArchive

Sheets("RMF File List").Select
Application.DisplayAlerts = False
Sheets("RMF File List").Delete
Sheets.Add.Name = "RMF File List"
Range("A1").Select


Sheets("Input").Select
Range("d10").Select
curdirloc = Selection.Value
Sheets("RMF File List").Select

' Set the directory to be analyzed
mypath = curdirloc
' Retrieve the first entry.
myName = Dir(mypath, lstAttr)
'put labels on the sheet at the top of the columns
sh.Cells(1, 1) = "Path:"

Hello,

Mike is right, change "sh.cells(1,1)" to "Activesheet.cells(1,1)" and
it shouldn't happen.


ps- why are you "select"ing so much?


HTH,
JP


On Oct 26, 3:48 pm, mburkett wrote:
I am getting the following error when I run the macro below on line:
sh.Cells(1, 1) = "Path:"

Run-Time error '-2147221080(800401A8)':
Automation Error

Does anyone have an idea of what might be going on. This macro has ran
for 2 years without a problem. Thanks!