View original (French)

Automatic Excel Sheet Creation

Solved
tontonl70 Posted messages 146 Status Member -  
 Ericoco -
Hello,
I would like to create an Excel file with a main page (sheet1) where I would enter company names with an autofilter. On sheet2, I want to create a standard form with the details of the companies from sheet1 (business name, address, number...).
In fact, I would like that as soon as I enter a company in column "A" of sheet1 and confirm with "ENTER", a new sheet is automatically created, named after the company, and containing the standard form that I will just have to complete. I think I need to use macros, but I don't know how they work. Or maybe I should use a database software like the one from OpenOffice that I am not familiar with...
Thank you in advance for your help.
Configuration: Windows XP Firefox 3.0.6

22 answers

  • 1
  • 2
  1. pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 773
     
    Hi,
    I tried to set up your request to make it automatic.
    I did this:

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
    Dim val As String
    Sheets("Sheet1").Select
    ActiveSheet.Range("A1").Select
    Do While ActiveCell <> ""
    ActiveCell.Offset(1, 0).Select
    Loop
    val = ActiveCell.Offset(-1, 0).Value
    Sheets("Sheet2").Select
    Sheets("Sheet2").Copy After:=Sheets(2)
    ActiveSheet.Name = val
    End Sub

    But this assumes that column A does not have any empty cells from A1 to the end, that you enter the new company at the end of the column each time, and to make it work, you double-click on Sheet1.
    So it works anyway.
    5
  • 1
  • 2