Auto Generate Worksheet from a List

[Closed]
Report
Posts
2
Registration date
Tuesday October 15, 2013
Status
Member
Last seen
October 16, 2013
-
Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
-
Hi

Can anybody help me please..........:-)

I am trying to set up a "defect register" in an excel worksheet whereby information inputted into a table on worksheet 1 (Basingstoke) is used to create and fill in a template worksheet (DefectIssueSheet) with the contents of column A used as the title of the new sheet produced.

Could anybody please tell me how I can write a Macro that auto-generates and labels a worksheet for every defect item in the list (A6:A60 on the first sheet) and have each sheet be a copy of the "DefectIssueSheet" template?

Many Thanks in advance for any given

Hayley

3 replies

Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
486
Hi Hayley,

Here you go:

Sub RunMe()
Dim x, lRow As Integer

x = 5
lRow = Sheets("Basingstoke").Range("A1").End(xlDown).Row

Do
x = x + 1
Sheets("DefectIssueSheet").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = Sheets("Basingstoke").Range("A" & x).Value
Loop Until x = lRow

End Sub

Best reagrds,
Trowa
Posts
2
Registration date
Tuesday October 15, 2013
Status
Member
Last seen
October 16, 2013

Thanks Trowa

I think that I have obviously set my defect issue sheet template up wrong as the new worksheets created for the list are using the first line of information contained in the list.

Is there a way of ensuring that the whole line of infomation is used to input the relevant cells of the template defect issue sheet? For example column A of the "Basingstoke" Sheet should be used to fill cell C3 of the "defectissuesheet", column B of the "Basingstoke" sheet should be used to fill cell L3 of the "DefectIssueSheet", column C of the "Basingstoke" sheet should be used to fill cell C4 of the "DefectIssueSheet" etc

Many Thanks

Hayley
Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
486
Hi Hayley,

Don't know what comes after "etc" but the following code covered the 3 cell locations:
Sub RunMe()
Dim x, lRow As Integer

x = 5
lRow = Sheets("Basingstoke").Range("A1").End(xlDown).Row

Do
x = x + 1
Sheets("DefectIssueSheet").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = Sheets("Basingstoke").Range("A" & x).Value
ActiveSheet.Range("C3").Value = Sheets("Basingstoke").Range("A" & x).Value
ActiveSheet.Range("L3").Value = Sheets("Basingstoke").Range("B" & x).Value
ActiveSheet.Range("C4").Value = Sheets("Basingstoke").Range("C" & x).Value
Loop Until x = lRow

End Sub

Best regards,
Trowa