VBA - create new sheet from a template

traxman

New Member
Joined
Oct 3, 2019
Messages
6
Hi

I am asking for your help for a VBA code which create new sheets from a template sheet.

I have already found and modified which works well :

Code:
<code class="hljs vbscript">Sub CreeOnglets()
   Application.ScreenUpdating = False
   supOnglets
   Set bd = Sheets("bd")
   bd.[A1].CurrentRegion.Sort Key1:=bd.Range("A2"), Order1:=xlAscending, Header:=xlGuess
   ligBD = 2
   Do While ligBD <= bd.[A65000].End(xlUp).Row
       nom = bd.Cells(ligBD, 1)       ' Premier nom
       Sheets("modèle").Copy After:=Sheets(Sheets.Count)
       ActiveSheet.Name = "F_" & nom
       Set fiche = Sheets("F_" & nom)
       fiche.Range("B3").Value = nom
       fiche.Range("b4").Value = bd.Cells(ligBD, "B")
       ligBD = ligBD + 1
    Loop
End Sub
Sub supOnglets()
  Application.DisplayAlerts = False
  For s = Sheets.Count To 1 Step -1
     If Left(Sheets(s).Name, 2) = "F_" Then Sheets(s).Delete
  Next s
End Sub
Sub exportOnglets()
    CheminAppli = ThisWorkbook.Path
    Application.DisplayAlerts = False
    For i = 1 To Sheets.Count
        If Left(Sheets(i).Name, 2) = "F_" Then
          Sheets(i).Select
          nonglet = ActiveSheet.Name
          ActiveSheet.Copy
          ActiveWorkbook.SaveAs Filename:=CheminAppli & "\" & nonglet
          ActiveWindow.Close
        End If
    Next i
End Sub
Sub consolideOngletsBD()
    ligBD = 2
    Set bd = Sheets("bd")
    For f = 1 To Sheets.Count
        If Left(Sheets(f).Name, 2) = "F_" Then
           bd.Cells(ligBD, "A") = Sheets(f).[B3]
           ligBD = ligBD + 1
        End If
    Next f
End Sub</code>
but the problem is that with this code the new entries are organized in rows (A, A2, A3 etc...), while I need to transform it into columns (A1, B1, C1 etc...)

How can I transform this code to select the next entry in the next column and not in the next row ?

Thank you very much for your help

Anthony
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
36,075
Office Version
365
Platform
Windows
Assuming you are talking about the first Sub out of the 4 you have posted, where should the value of B4 come from if the sheet names are coming from A1, B1 etc?
 

traxman

New Member
Joined
Oct 3, 2019
Messages
6
Thank you very much for your help :)

It is not very easy to explain for me (as I am neither english native nor an excel expert).

I use this code found in the web (http://boisgontierjacques.free.fr/pages_site/cellules.htm)

The here a database in sheet "BD", with the last names (column A), first names (in B), adrdess (in C)...

A sheet called "modèle" corresponds to the template




The template sheet:



I hope I have been clear enough. If not, how can I provide you the excel file ?

Thank you very much again

Anthony
 
Last edited by a moderator:

traxman

New Member
Joined
Oct 3, 2019
Messages
6
Sorry I don't know that I couldn't add pictures, so here is the two pictures that I wanted to share with you





Thank you again (and sorry for my mistake)
 

traxman

New Member
Joined
Oct 3, 2019
Messages
6
Okay thank you

I try to add above screenshots of the Excel sheets and try to explain my problem

Thank you

Anthony
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
36,075
Office Version
365
Platform
Windows
Do you still want one sheet per person?
 

Watch MrExcel Video

Forum statistics

Threads
1,090,400
Messages
5,414,190
Members
403,518
Latest member
FloJoMoJo

This Week's Hot Topics

Top