I need a macro which will add sheets using a template and name them from a column of cells

**rdunaway** · 02-04-2014, 06:54 PM

Hi everyone, I'm new here and don't have a lot of experience with macros but here is my problem.

Step 1: I have data coming from a data dump which is placed on the "Summary" sheet of my excel workbook. I need to take column A (beginning at A8) and create a new sheet (based on "Template" located in the same workbook) for each name in column A.

Step 2: I need to place the name of the new sheets in cell C4 of each sheet so I can do a lookup using C4 as my lookup_value, so this can't be a formula like "=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)" because lookup doesn't function properly.

Step 3: Delete "Template", so that the only things left are the "Summary" sheet and the new sheets.

Each department will have their own workbook with a "summary" sheet and different sheet names although they will all start off with the same data dump and will all be in the same format. Each department could also have a different number of new sheets added depending on information from the data dump.

I found a utility (ASAP Utilities) which will do the steps I need done, but it won't record in the macro.

Help please Budget time is fast approching and each department needs their worksheets.

Thanks so much for your help

**jmac1947** · 02-04-2014, 10:07 PM

Hi rdunaway, and welcome to the forums

It is always an excelletn idea to add a sample workbook showign as clearly as possible your input and your expected results, doesnt have to be huge but should clearly show what you are expecting "success" to look like.

I am sure you will get lots of responses to your issue from some of the experts here. I do have a working solution to what seems like exactly what you are trying to do but it isnt where I can get to it right now. When I get back to my desktop I will check the thread and respond if you dont already have a solution.

Adding a sample is done from the "GO Advance" link at the bottom right corner of the posting window

cheers

**rdunaway** · 02-05-2014, 12:48 PM

Hi jmac1947,
I'm not only new to macros but new to forums as well. Your advice is very much appreciated. I have attached an excel spreadsheet as an example of what I need. I have included some explanations within the sheets. I hope it is helpful. Thanks again for any assistance.

rdunaway

**JOHN H. DAVIS** · 02-05-2014, 02:27 PM

Maybe:

Please Login or Register  to view this content.

**rdunaway** · 02-05-2014, 04:49 PM

Hi John H. Davis,

Thank you for your contribution. Unfortunately the macro copies the Header formats of "Budget Summary" to "Template" sheet and changes the heading on "Template", then creates the first "new" sheet, naming it Template (2) and errors out on line "ActiveSheet.Name = y" with the following:

Run-time error '1004':

Cannot rename a sheet to the same name as another sheet, a referenced object library or a workbook referenced by Visual Basic.

I found this code from the internet and modified it to fit my data. It works great for creating the sheets:

Sub rdunaway()

Dim ws1 As Worksheet
Set ws1 = ThisWorkbook.Worksheets("Template")

Dim MyCell As Range, MyRange As Range

Set MyRange = Sheets("Invoice Summary").Range("A8")
Set MyRange = Range(MyRange, MyRange.End(xlDown))

For Each MyCell In MyRange
ws1.Copy ThisWorkbook.Sheets(Sheets.Count)
ThisWorkbook.Worksheets("Template(2)").Name = MyCell.Value
Next MyCell

End Sub

All I need now is to put the sheet name into cell C4 of each new sheet (to satisy the lookup_value needed for cell B5) Any Ideas???

Thanks John H. Davis for your contribution, but not exactly what I'm looking for.

**JOHN H. DAVIS** · 02-05-2014, 05:01 PM

The error is caused by your data in your sample. In Column A of Sheet "Budget Summary" at the Bottom you have "Template". I had to remove it to get it too work. I think you put it there to show a sample.

**rdunaway** · 02-05-2014, 06:17 PM

Hi John H. Davis

Opps, Sorry about that you are correct and the macro does work YEAH!!!! Thank you so much. It still copies formats from "Budget Summary" and pastes them to "Template" , but i commented out these 3 lines and it works perfect.

ws1.Range("A1:K1").Copy ws.Range("A1")
ws1.Range("B4").Copy ws.Range("B3")
ws1.Range("B5").Copy ws.Range("B4")

Thanks so much!!

**rdunaway** · 02-05-2014, 06:21 PM

Hi John H. Davis

Opps, Sorry about that you are correct and the macro does work YEAH!!!! Thank you so much. It still copies formats from "Budget Summary" and pastes them to "Template" , but i commented out these 3 lines and it works perfect.

ws1.Range("A1:K1").Copy ws.Range("A1")
ws1.Range("B4").Copy ws.Range("B3")
ws1.Range("B5").Copy ws.Range("B4")

Thanks so much!!

**jmac1947** · 02-05-2014, 06:50 PM

rdunaway jmac.xlsm

Hi rdunway,

Here is an example of how you might do it

**JOHN H. DAVIS** · 02-06-2014, 07:14 AM

You're welcome. Glad to help out and thanks for the feedback.

I need a macro which will add sheets using a template and name them from a column of cells

LinkBack

Thread Tools

Rate This Thread

Display

I need a macro which will add sheets using a template and name them from a column of cells

Re: I need a macro which will add sheets using a template and name them from a column of c

Re: I need a macro which will add sheets using a template and name them from a column of c

Re: I need a macro which will add sheets using a template and name them from a column of c

Re: I need a macro which will add sheets using a template and name them from a column of c

Re: I need a macro which will add sheets using a template and name them from a column of c

Re: I need a macro which will add sheets using a template and name them from a column of c

Re: I need a macro which will add sheets using a template and name them from a column of c

Re: I need a macro which will add sheets using a template and name them from a column of c

Re: I need a macro which will add sheets using a template and name them from a column of c

Thread Information

Users Browsing this Thread

Similar Threads

Macro to copy and paste values from diff sheets into a template worbook

Macro to create multiple sheets from template and fill with datas from one sheet

Creating a macro to reformat multiple sheets into a new template

[SOLVED] Filling cells into template based on selection with data in seperate sheets

Macro to create new sheets based on template no longer working

Bookmarks

Bookmarks

Posting Permissions