Create sheet with info base on list

[mdorey]

Hello all,

It is been long time since i came here asking your help. I hope i can find once again help from you all.

I'll put it by steps:

Step 1: I need help to create a VBA that will copy a Template sheet to a new workbook (as many times the list has starting on row 2 until the last) the list will extrated from my work program.

Step 2: For each sheet created copy the info from Column C of that row to the sheat created C3, Column AC to E3, Column M to G3. And for last, on the list that i have exported in column G i have a list of names with the format Surname and Name seperated by comma, the surname would be paste on A5 and the name on the A7.

Many thanks for any attention on this matter,
Have a nice day

 

[DanteAmor]

It did put them all in the same file

However it created another 31 blank sheets besides the copied ones...

Check the cells in column A, if there are cells with spaces

 

[mdorey]

no... only numbers

 

[DanteAmor]

no... only numbers

delete all your sheet or on a new sheet just put your numbers

 

[mdorey]

delete all your sheet or on a new sheet just put your numbers

You are a STAR man thank you very much for all your help on this matter.

About the other sheets that are created i'll just delete them :D the code already helps a lot

Thank you very very much

 

[DanteAmor]

You are a STAR man thank you very much for all your help on this matter.

About the other sheets that are created i'll just delete them :D the code already helps a lot

Thank you very very much

After these lines add the code so that in the new book you only have an additional sheet.

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    [COLOR=#0000ff]Application.SheetsInNewWorkbook = 1[/COLOR]

 

[mdorey]

After these lines add the code so that in the new book you only have an additional sheet.

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    [COLOR=#0000ff]Application.SheetsInNewWorkbook = 1[/COLOR]

It worked :D

It created the newworkbook with just one extra sheet

Once again you are a star thank you very much for all of your help

 

[DanteAmor]

It worked :D

It created the newworkbook with just one extra sheet

Once again you are a star thank you very much for all of your help

If you want to delete that sheet:

Sub Create_Sheet()
    Dim l1 As Workbook, l2 As Workbook
    Dim sh1 As Worksheet, sh2 As Worksheet
    Dim c As Range, wNames As Variant
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
   [COLOR=#0000ff] Application.SheetsInNewWorkbook = 1[/COLOR]
    
    Set l1 = ThisWorkbook
    Set sh1 = l1.Sheets("List")         '
    Set sh3 = l1.Sheets("Template")
    
    Set l2 = Workbooks.Add
    
    For Each c In sh1.Range("A2", sh1.Range("A" & Rows.Count).End(xlUp))
        sh3.Copy after:=l2.Sheets(l2.Sheets.Count)
        Set sh2 = l2.Sheets(l2.Sheets.Count)
        wNames = Split(sh1.Range("G" & c.Row).Value, ",")
        sh2.Range("C3").Value = Mid(sh1.Range("C" & c.Row).Value, 5)
        sh2.Range("E3").Value = sh1.Range("AC" & c.Row).Value
        sh2.Range("G3").Value = sh1.Range("M" & c.Row).Value
        sh2.Range("A5").Value = wNames(0)
        sh2.Range("A7").Value = IIf(UBound(wNames) = 1, wNames(1), "")
    Next
[COLOR=#0000ff]    l2.sheets(1).delete[/COLOR]
    MsgBox "End"
End Sub

 

[mdorey]

I will set that up then it is just perfect

 

[DanteAmor]

It is a pleasure