Microsoft Excel - Creating a copy of two worksheets but changing the source data when changing the ws name

If I am using a set of 2 worksheets where worksheet 2 is pulling data that s typed into worksheet 1 (one is a detail and two a summary) and I want to duplicate the two worksheets for another trade scope and re-name them. Is there a way to re-direct the links to the name of the new worksheet without changing each individual cell? 

Hello Robyne,

Have you tried selecting both sheets and doing the sheet copy simultaneously?  Then rename the sheets individually.

Regards

Harry

The issue is that the actual worksheet contains links to the first worksheet in like 35 places that have to be changed each time. Thank you Harry!

Hello Robyne,

What to you mean by links?  Are these just formulae or are they hyperlinks?

Harry

Harry, it is one worksheet that contains a complete breakdown of a subcontractor's scope of work in detail and the subsequent worksheet is a summary which is just pulling in certain information from the prior sheet. The issue is that one some projects there might be ten scopes which involves making ten sets of two worksheets within one workbook. On other projects there might me 35 scopes of work which means that we are creating one set (2-worksheets) for each scope. But the summary page is pulling data from like 35 cells (and not in a string) and each one has to be changed when the worksheet is renamed. Does this make sense? 

Hello Robyne,

When you say the second sheet is 'pulling data' from the first, I assume that this is just formulae that refer to the first sheet.  So, if you select sheet1, hold shift and select sheet2, you have selected both sheets.  Then right click on one of the tabs and a pop-up will appear that includes an option to move or copy sheets.  Open that and tick the create copy box.  When that is done you will have new sheets names sheet1(1) and sheet2(1) which you can then name to sheet3 and sheet4 and all the formulae references on sheet4 will be to the cells in sheet3 in which you will be able to enter your data for the new scope.  Just repeat as required for as many scopes as you need.

If this isn't what you want, maybe if you could post a (sanitised) sample workbook, it might be possible to help further.

Harry

Using free Spire.XLS, you could duplicate any worksheet and create any number of copies within the workbook.

Workbook workbook = new Workbook();
workbook.LoadFromFile(@"..\copy worksheets.xls");
//get the worksheet that you want to duplicate
Worksheet worksheet = workbook.Worksheets[0];
//add worksheets and name them
workbook.Worksheets.Add("copied sheet1");
workbook.Worksheets.Add("copied sheet2");
//copy worksheet to the newly added worksheets
workbook.Worksheets[1].CopyFrom(workbook.Worksheets[0]);
workbook.Worksheets[2].CopyFrom(workbook.Worksheets[0]);
workbook.SaveToFile(@"..\copy worksheets.xls");