Add Cell Value To Existing Tab Name - VBA

voice Example.xls

My situation is as follows: I have multiple workbooks that each contain three worksheets, the worksheets from one or more of these workbooks needs to be added to another workbook.

Since the worksheet names in all workbooks are the same, I need to add the data from a cell (account number) in the file to the existing worksheet names. Using the attached file as an example, the value of cell A8 on the DMC-UPS-Summary worksheet needs to be added to each worksheet name in this file.