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

SereneSea

New Member
Joined
Feb 2, 2022
Messages
43
Office Version
  1. 2016
Platform
  1. Windows
Hi all,
I hope you can help me::biggrin:
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
ABCD
1TitleSubtitle
2Catssiamese
3DogGerman shepard
4BirdPigeon
5
6
7
8
9
10
Master

and
template:
Template.xlsm
ABCDEFGHIJ
1
2TitleInformation
3Subtitle
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!


VBA Code:
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
 
Ok, try
VBA Code:
        ActiveSheet.Name = c.Value: ActiveSheet.Range("A2").Resize(2).Value = Application.Transpose(c.Resize(, 2).Value)
But it looks as though A2 & A3 are merged cells, which are an abomination & should be avoided like the plague.
 
Upvote 0
Solution

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Thanks for the merging tip, i have unmerged the cells now,.
As for the code: YES thank you thank you thank you :) it worked perfectly, :) I realized I had pasted the code with rows count ,2, and changed it back to 1, . You are amazing! Thank you (y):):):):):):)
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,224,504
Messages
6,179,144
Members
452,891
Latest member
JUSTOUTOFMYREACH

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top