Application defined or object defined error help

Liz_I3

Well-known Member
Joined
Dec 30, 2002
Messages
647
Office Version
  1. 2016
Platform
  1. Windows
This is crazy if I run the below code from the module or have the module open and run it from the view macro dropdown on the excel sheet it works perfect. But if I close the module and run it from the view macro dropdown (from the menu ) I get the Application defined or object defined error when it hits line Worksheets(wksCurSheet.Name).Name = wksCurSheet.CodeName

Thanks
L

Sub MergeExcelFiles()
Dim fnameList, fnameCurFile As Variant
Dim countFiles, countSheets As Integer
Dim wksCurSheet As Worksheet
Dim wbkCurBook, wbkSrcBook As Workbook

fnameList = Application.GetOpenFilename(FileFilter:="Microsoft Excel Workbooks (*.xls;*.xlsx;*.xlsm),*.xls;*.xlsx;*.xlsm", Title:="Choose Excel files to merge", MultiSelect:=True)

If (vbBoolean <> VarType(fnameList)) Then

If (UBound(fnameList) > 0) Then
countFiles = 0
countSheets = 0

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Set wbkCurBook = ActiveWorkbook

For Each fnameCurFile In fnameList
countFiles = countFiles + 1

Set wbkSrcBook = Workbooks.Open(FileName:=fnameCurFile)

For Each wksCurSheet In wbkSrcBook.Sheets

countSheets = countSheets + 1

Worksheets(wksCurSheet.Name).Name = wksCurSheet.CodeName ' error generated if module is not open??

wksCurSheet.Copy After:=wbkCurBook.Sheets(wbkCurBook.Sheets.Count)

Next

wbkSrcBook.Close SaveChanges:=False

Next

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

MsgBox "Processed " & countFiles & " files" & vbCrLf & "Merged " & countSheets & " worksheets", Title:="Merge Excel files"
End If

Else
MsgBox "No files selected", Title:="Merge Excel files"
End If
End Sub
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Try
VBA Code:
For Each wksCurSheet In wbkSrcBook.Worksheets

countSheets = countSheets + 1

wksCurSheet.Name = wksCurSheet.CodeName

wksCurSheet.Copy After:=wbkCurBook.Sheets(wbkCurBook.Sheets.Count)

Next
 
Upvote 0
Thanks I tried that but it gave me "Method 'Name of Object_Worksheet Failed" error. Could it have anything to do with my main workbook (where the code is) loosing focus? But it is strange that it runs error free when the developers vba module is open .
 
Upvote 0
That code works quite happily for me & cannot replicate the error you are getting, so not sure what the problem is.
 
Upvote 0
It also works for me too as long as I have the Developers module open, but as soon as I close it and run it from the view macros menu it fails. Before that part of the code it asks the user to select files. After the selected files (example 4) if the module is not open should I bring focus back to my main workbook? if so how?
 
Upvote 0
Wit the code I posted it shouldn't matter which workbook is active.
 
Upvote 0
It just does not make sense why it works if the code window is open but when I close it I get errors :(
 
Upvote 0
I've encountered that very occasionally, but not in this instance. And whilst your code did not work, with the changes I suggested there were no problems at my end.
 
Upvote 0
Thanks. I did a work around beginning of macro open visual basic editor at the closed it. Not the best but it worked
 
Upvote 0
Glad you sorted it & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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