Solved as i am having some sheet names in my excel file and i want

August 21, 2012 at 06:43:51
Specs: Windows XP
as i am having some sheet names in my excel file and i want to check whether the names in my sheet are equal to the sheets names in the other workbook

See More: as i am having some sheet names in my excel file and i want

Report •

#1
August 21, 2012 at 12:33:43
✔ Best Answer
This code, when run from one workbook, will loop through each sheet in the workbook and use each Sheet name to try and pull a value from another workbook, e.g. Book1.xls.

If it can't pull the value becasue the sheet name doesn't exist, it will add that name to a list and after it has checked all sheets, it will present of message box with the list of sheet names that do not exist in Book1.xls.


Sub CheckSheetNames()
'Error handler for when a Sheet Name isn't found
 On Error GoTo NoNameFound
'Loop through the Sheets in this workbook
   For shtNum = 1 To Sheets.Count
'Store current Sheet name
     curName = Sheets(shtNum).Name
'Try to pull a value from the same Sheet name in Book1.xls
'Error will be handled if Sheet Name doesn't exist
      myFlag = Workbooks("Book1.xls").Sheets(curName).Range("A1")
    Next
'Build final message and present it
  NoName = Right(tempNoName, Len(tempNoName) - 2)
  MsgBox "The following sheets do not exist In Book1.xls: " & NoName
'Error Handler - Build temp string with missing Sheet Names
NoNameFound:
 tempNoName = tempNoName & ", " & curName
 Resume Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •
Related Solutions


Ask Question