VBA Create a worksheet from a master list and also add title (Code added)

[SereneSea]

Hi all,
I hope you can help me:
I am trying to create a template and use VBA code. I have two sheets, one that is a "template" and second is "master". The user will input data in the master and then use a VBA code to copy and paste the "template" as many times as needed and then name the sheet with the Title from the master sheet and use add the subtitle info in A3 going down the list in the master until a blank cell is found.

Master sheet:

Template.xlsm
	A	B	C	D
1	Title	Subtitle
2	Cats	siamese
3	Dog	German shepard
4	Bird	Pigeon
5
6
7
8
9
10
Master

and
template:

Template.xlsm
	A	B	C	D	E	F	G	H	I	J
1
2	Title			Information
3	Subtitle
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
Template

The code for copying and naming spreadsheet is below, but it is currently creating sheets from both A column and B Column. I only need A column to be in A2 (Title) and sheet name, and the B column info to be used in A3 (subtitle). Hope you can help me!

Sub makeSheets()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
Set sh1 = Sheets("Template")
Set sh2 = Sheets("Master")
    For Each c In sh2.Range("A2", sh2.Cells(Rows.Count, 2).End(xlUp))
        sh1.Copy After:=Sheets(Sheets.Count)
        ActiveSheet.Name = c.Value: ActiveSheet.Range("A2") = c.Value
    Next
End Sub

 

[Fluff]

Change this

For Each c In sh2.Range("A2", sh2.Cells(Rows.Count, 2).End(xlUp))

to

For Each c In sh2.Range("A2", sh2.Cells(Rows.Count, 1).End(xlUp))

 

[SereneSea]

That works great for pasting the title, would you be able to help me on how to get the subtitle to populate from the b column information from the master? I tried adding a similar line but I probably did it incorrectly, as it does not work .

Sub makeSheets()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range, d As Range
Set sh1 = Sheets("Template")
Set sh2 = Sheets("Master")
    For Each c In sh2.Range("A2", sh2.Cells(Rows.Count, 1).End(xlUp))
        sh1.Copy After:=Sheets(Sheets.Count)
        ActiveSheet.Name = c.Value: ActiveSheet.Range("A2") = c.Value
    Next
       For Each c In sh2.Range("B2", sh2.Cells(Rows.Count, 1).End(xlUp))
        sh1.Copy After:=Sheets(Sheets.Count)
        ActiveSheet.Name = d.Value: ActiveSheet.Range("B2") = c.Value
    Next
   
End Sub

 

[Fluff]

How about

Sub makeSheets()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
Set sh1 = Sheets("Template")
Set sh2 = Sheets("Master")
    For Each c In sh2.Range("A2", sh2.Cells(Rows.Count, 1).End(xlUp))
        sh1.Copy After:=Sheets(Sheets.Count)
        ActiveSheet.Name = c.Value: ActiveSheet.Range("A2").Resize(, 2).Value = c.Resize(, 2).Value
    Next
End Sub

 

[SereneSea]

hmm. it still does not paste the info from column B Master to the A3 in the copied sheets.

 

[Fluff]

It should do. Does A2 on the new sheet have the sheet name in it?

 

[SereneSea]

Yup that part works good, but i also need the "subtitle" to be populated with the adjacent info in B column.

 

[SereneSea]

Yes the title works perfectly, but its still not filling in the Subtitle part - which for the bottom example should be Siamese as per the master sheet B column

 

[Fluff]

Are you trying to put it in A3 or in B2 as per your code?
Also is anything going into B2 in the new sheet?

 

[SereneSea]

I am trying to put it in A3. I removed the colors so its easier to see. nothing will be going in b3

Sub makeSheets()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range, d As Range
Set sh1 = Sheets("Template")
Set sh2 = Sheets("Master")
    For Each c In sh2.Range("A2", sh2.Cells(Rows.Count, 1).End(xlUp))
        sh1.Copy After:=Sheets(Sheets.Count)
        ActiveSheet.Name = c.Value: ActiveSheet.Range("A2") = c.Value
    Next
       For Each c In sh2.Range("B2", sh2.Cells(Rows.Count, 1).End(xlUp))
        sh1.Copy After:=Sheets(Sheets.Count)
        ActiveSheet.Name = d.Value: ActiveSheet.Range("A3") = c.Value
    Next
   
End Sub

I get a Run time error 91 - object variable with block variable not set. Me adding the second set of code does not work, as clearly I do not understand macro :{