Application defined or object defined error help

Liz_I3

Well-known Member
Joined
Dec 30, 2002
Messages
645
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

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,547
Office Version
  1. 365
Platform
  1. Windows
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
 

Liz_I3

Well-known Member
Joined
Dec 30, 2002
Messages
645
Office Version
  1. 2016
Platform
  1. Windows
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 .
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,547
Office Version
  1. 365
Platform
  1. Windows
That code works quite happily for me & cannot replicate the error you are getting, so not sure what the problem is.
 

Liz_I3

Well-known Member
Joined
Dec 30, 2002
Messages
645
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,547
Office Version
  1. 365
Platform
  1. Windows
Wit the code I posted it shouldn't matter which workbook is active.
 

Liz_I3

Well-known Member
Joined
Dec 30, 2002
Messages
645
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

It just does not make sense why it works if the code window is open but when I close it I get errors :(
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,547
Office Version
  1. 365
Platform
  1. Windows
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.
 

Liz_I3

Well-known Member
Joined
Dec 30, 2002
Messages
645
Office Version
  1. 2016
Platform
  1. Windows
Thanks. I did a work around beginning of macro open visual basic editor at the closed it. Not the best but it worked
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,547
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,538
Messages
5,636,897
Members
416,948
Latest member
Jkpang

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
Top