Automatic Excel Sheet Creation Solved

Question

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

pijaku · Accepted Answer

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.

Le Pingou · Answer

Hello,
Just a quick note, I don’t think it’s necessary to create a macro.
You create a template sheet for the data to be filled in. Then you duplicate it and give it its name.
In your home sheet, retrieve the name of the company found on the duplicated sheet in cell A2 = DuplicatedSheetName!A3 where A3 contains the company name.
Finally, you create a hyperlink in cell B2 that points to the detail sheet of the company.
Note, it’s longer to explain than to accomplish.
--

Best regards.
Jean-Pierre

tontonl70 · Answer

Thank you for your response. As I am completely new to Excel, could you explain in more detail because I'm not quite sure how to do it... Thank you.

Le Pingou · Answer

Hello,
It would be simpler if you could provide an example of the result you want to achieve.
Especially for sheet 2, as sheet 1 is clear.
Upload it to https://www.cjoint.com/ and post the link.
Thank you
--

Best regards.
Jean-Pierre

tontonl70 · Answer

Hello and thank you for your help.
Here is the Excel file https://www.cjoint.com/?djkXe82MFK
Basically, the goal is to enter names in column 1 and have a sheet with the company's name created automatically, and in that sheet, to have the template sheet (base sheet) to just fill in.
Thank you pijaku for this information, but not being familiar with it, where do I type these codes or formulas??
Thanks

Le Pingou · Answer

Hello,
Thank you for the information.
Should the hyperlinks be kept as they are?
Should the information from the selected sheet (CP, City, Contact Person, and Date of 1st Visit) be copied to the company sheet or does it come from the latter (with an instruction ='Example 1'!B7:E7)?
--

Best regards.
Jean-Pierre

Le Pingou · Answer

Hello, My proposal: https://www.cjoint.com/?djtlnQnhCy Have a good Sunday. --  Best regards. Jean-Pierre

tontonl70 · Answer

Thank you, Jean-Pierre, for your help! Yes, I want to keep the hyperlink. I tried your file but it doesn't work... Apparently, the macros are not enabled! What should I do? Thank you.

Le Pingou · Answer

Hello, You need to close the workbook and open it again... you should see a warning message and you need to accept to enable macros.... I’m waiting for your response.. --  Best regards. Jean-Pierre

tontonl70 · Answer

Super, thanks, that's basically what I wanted to do. However, the postal code, city... are not transferred to the created company file... I have the value "0" written in the fields of the selection sheet for postal code, contact person...

Le Pingou · Answer

Hello,
Yes, that's right. I take it that the company's form is filled out (except for the name since it's used in the selection sheet). The postal code, city, etc., received a link that looks for the value you would enter in the form, which is why you see 0.
Try entering the data, postal code, city, etc., in the form and then return to the selection sheet.
On the selection sheet, if you select, for example, cell "B2" which corresponds to the postal code of company 1
you can see in the formula bar that the value comes from: ='Example 1'!B7
--

Regards.
Jean-Pierre

tontonl70 · Answer

Sure, here is the translation:

Ok perfect, thank you very much for your help. Just a quick question: is it possible for the sheet created for a company to be deleted if we delete the line for that company on the selection sheet? How do you create these links with macros? Is it a specific language or are they formulas? Thank you

Le Pingou · Answer

Hello, Question: Is it possible for the sheet created for a company to be deleted if we delete the row of the company in question on the selection sheet? Answer: Yes, but a specific macro needs to be created. However, nothing prevents you from manually deleting the sheet and the hyperlink. Question: How do you create these links with macros? Answer: To see the macro, select the selection sheet, right-click on the tab, and click on View Code. The code is as follows: ' create the hyperlink in the selected sheet adr = "'" & Target.Value & "'!A1" ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=adr, TextToDisplay:=Target.Value  This is VBA (Visual Basic) language, and there are formulas only in the blue range (Postal Code, City, …) of the selected sheet.  --  Best regards. Jean-Pierre

tontonl70 · Answer

Ok, thanks for this information. Would it be complicated to create the macro for automatic deletion?

Le Pingou · Answer

Hello,
No, it's quite simple for me.
In principle, what do you remove first, the company sheet or its hyperlink?
Thank you for your reply.
It will be for next Monday...
Good night and have a nice Sunday.
--

Best regards.
Jean-Pierre

tontonl70 · Answer

I would like to delete the company's line on the selection sheet, which will consequently delete the sheet that was created. Thank you for all this help Have a good end of the weekend

Le Pingou · Answer

Hello, Here’s my proposal, a little in advance: https://www.cjoint.com/?dkwoqrpZTf --  Best regards. Jean-Pierre

tontonl70 · Answer

Thank you for your valuable help! Best wishes to you Sincerely

Le Pingou · Answer

Thank you, you're welcome. --  Best regards. Jean-Pierre

Bolinette · Answer

Hello,  Is it still possible to recover that Excel file? Indeed, the link on "cjoint" is dead...  Thank you in advance and best regards,

Bolinette · Answer

Thank you for conveying the message; however, I can't find the code in this file...  Best regards

Le Pingou · Answer

Hello Bolinette, Having had a bad experience due to requests made via private messages, I am stepping down from this position. Sorry for you. --  Regards. Jean-Pierre

Automatic Excel Sheet Creation

22 réponses

Texture issues in enshrouded

Driver his

Peritel to hdmi conversion

How to access the bios on a compaq notebook

Dsi won't turn on anymore

Search for a song with the lyrics

Left mouse click, touchpad click inactive

Codes stronghold crusader

Stuck computer system repair

Disable write protection mp3 player