Populate multiple templates with existing table

[Luin29]

Hello All,

I have been tasked with creating a macro that will allow for creating worksheets fasterfor the company's field technicians, and I have hit a road block.

My current project is to create a macro that will generate a series of worksheets (the number of which will equal the number of wells to be sampled) and populate these worksheets with preliminary data from a summary worksheet.

I have been able to write a macro that will generate the series of worksheets, which is based on a template I have created, but I can not figure out how to populate the cells in each worksheet. The data to be populated into the sheets is arranged in rows.

Any help that can be provided will be greatly appreciated.

Thank you.

 

[My Aswer Is This]

We would need to have a lot more details.
Like what are the sheet names
What is the summary sheet name
What rows on the summary sheet go into what rows on the other sheets.
How do we identify what row on the summary sheet goes into the other sheets.

 

[Luin29]

Sure:

• What are the sheet names?: The sheet names will be listed in column A (the location ID)
• What is the summary sheet name?: Summary
• How do we identify what row on the summary sheet goes into the other sheets?: The rows will be identified using the location ID

The table below is an example of the data that will be populated into the worksheets. Also, the sheets will be named after the location ID.

The break down of where the data in the table below is to be populated is as follows:

Location ID into cell G3
Date into cell G2
Monitoring well diameter into cell C7
Monitoring well total depth into cell C8
Screen interval into cell C9


[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]Location ID[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Monitoring well diameter[/TD]
[TD="align: center"]Monitoring well total depth[/TD]
[TD="align: center"]Screen Interval[/TD]
[/TR]
[TR]
[TD="align: center"]MW-1[/TD]
[TD="align: center"]3/23/2017[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]5-15[/TD]
[/TR]
[TR]
[TD="align: center"]MW-2[/TD]
[TD="align: center"]3/23/2017[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]15-25[/TD]
[/TR]
[TR]
[TD="align: center"]MW-3[/TD]
[TD="align: center"]3/23/2017[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]35[/TD]
[TD="align: center"]25-35[/TD]
[/TR]
</tbody>[/TABLE]

Here is the macro I have so far:

Sub CreateSheetsFromAList()
On Error GoTo Errorcatch
Dim MyCell As Range, MyRange As Range
    Set MyRange = Sheets("Summary").Range("A8")
    Set MyRange = Range(MyRange, MyRange.End(xlDown))
    For Each MyCell In MyRange
        Sheets.Add after:=Sheets(Sheets.Count) 'creates a new worksheet
        Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
        Worksheets("Low-flow template").Cells.Copy ActiveSheet.Range("A1")
    Next MyCell
Exit Sub
Errorcatch: MsgBox Err.Description
End Sub

 

[My Aswer Is This]

Try this:
This script will create all the sheets and enter your data.

Sub CreateSheetsFromAList_Mod()
'Modified 3-23-17 4:40 PM EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Sheets("Summary").Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To Lastrow
        Sheets("Low-flow template").Copy After:=Sheets(Sheets.Count)
        ActiveSheet.Name = Sheets("Summary").Cells(i, 1).Value
        Range("G3").Value = Sheets("Summary").Cells(i, 1).Value
        Range("G2").Value = Sheets("Summary").Cells(i, 2).Value
        Range("C7").Value = Sheets("Summary").Cells(i, 3).Value
        Range("C8").Value = Sheets("Summary").Cells(i, 4).Value
        Range("C9").Value = Sheets("Summary").Cells(i, 5).Value
        
    Next
Application.ScreenUpdating = True
End Sub

 

[Luin29]

Thank you, this works great.

Unfortunately, I am receiving an application-defined or object-defined error on the following line of code:

ActiveSheet.Name = Sheets("Summary").Cells(i, 1).Value

How can I fix this and prevent a duplicate Low-flow template worksheet?

 

[My Aswer Is This]

I assume your creating a duplicate sheet message is that true?
You cannot have two sheets with the same name.

Did the script create the sheets or what?
You said: Thank you, this works great.

What did it do that was great.

The script looks down Column A on sheet summary starting in row (1)
and creates a copy of the sheet named "Low-flow template" and names the sheets according to the values in column A of sheet Summary

And then enters your data

Are you always wanting to create new sheets or are you sometimes just wanting to update data in sheets?

 

[My Aswer Is This]

If you have "Low-flow template" in column "A" that's true you will be making a duplicate.
Why do you need that name in columns "A"

 

[Luin29]

The script did create copies of the sheet named "low-flow template", named the sheets according to the values in column A of the "summary" sheet, and then entered the data, which is exactly what I need it to do. I think that the error that is occurring is due to the script creating one to many copies, resulting in having two sheets with the same name.

For this project, I will always be creating new sheets. Column A only has the location ID's in it, along with the header that is skipped.

 

[My Aswer Is This]

OK:
So your saying it made one two many sheets. And named or tried to name two of them the same.

What sheet name did it make a duplicate of?

Is that value entered twice in column "A"?

The script did create copies of the sheet named "low-flow template", named the sheets according to the values in column A of the "summary" sheet, and then entered the data, which is exactly what I need it to do. I think that the error that is occurring is due to the script creating one to many copies, resulting in having two sheets with the same name.

For this project, I will always be creating new sheets. Column A only has the location ID's in it, along with the header that is skipped.

 

[My Aswer Is This]

Try changing this line of code:

For i = 1 To Lastrow

To this:

For i = 2 To Lastrow

This will tell the script to start looking for sheet names in row (2) of column "A" and not Row (1)