Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Aug 2015
    Posts
    2
    Thanks
    2
    Thanked 0 Times in 0 Posts

    How to enter data into a table and have it name and populate a given template

    Hello,
    I was wondering if someone could help me out with making a document that essentially creates new, template sheets based on the information entered into an overview table. In the overview table Im hoping to have it set up by me entering into the specified columns: a date, a class (from a dropdown menu), the Prosecutor, the Defendant, a Page number, Court, and year, and in the final column would have it create the name of the sheet Id like made based off of this information. If its possible, Id like it to create the new, named sheet any time I make an entry to the overview table. I will not be deleting lines and will always be adding onto the previously used line. I also have a template for the sheet that Id like created for each, and links to populate the basic information based on the Overview table. Im still not very good with VBA, so was hoping someone might be able to help me figure out how to make this a reality? Any help would be REALLY appreciated CaseStudyGuide.xlsx

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    swartzjr,

    Here is your revised workbook where your data is entered on the "CaseStudyGuide" worksheet. If a record is completely filled in (ex Row 3 columns B to I), the code will fire by creating a new sheet from your hidden template sheet called "Template". The sheet will be renamed with the value in column I. The Case Name and Court & Date will automatically be filled in. A Hyperlink will then be applied to the value in Column I to link to the new sheet.

    HTH,
    Maud

    In the worksheet module
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column < 2 Or Target.Column > 9 Then Exit Sub
    Dim rng As Range, cell As Range, Row As Long, count As Integer
    Row = Target.Row
    count = 0
    Set rng = Range(Cells(Row, "B"), Cells(Row, "I"))
    For Each cell In rng
        If cell = "" Then
            count = count + 1
        End If
    Next cell
    If count = 0 Then CreateBriefing Row
    End Sub
    In a standard module
    Code:
    Public Sub CreateBriefing(ByVal line As Long)
    Application.ScreenUpdating = False
    With Worksheets("CaseStudyGuide")
        Worksheets("Template").Visible = True
        Sheets("Template").Copy Before:=Worksheets(Worksheets.count)
        ActiveSheet.Name = .Cells(line, 9)
        Cells(1, 2) = .Cells(line, "B")
        Cells(1, 3) = .Cells(line, "C") & "                                                                  JSwartzmiller"
        Cells(3, "C") = .Cells(line, "D") & " (P) v." & .Cells(line, "E") & " (D) (p." & .Cells(line, "F") & ")"
        Cells(4, "C") = .Cells(line, "G") & ", " & .Cells(line, "H")
        ActiveSheet.Hyperlinks.Add Anchor:=.Cells(line, "I"), Address:="", SubAddress:=ActiveSheet.Name & "!A1" ', TextToDisplay:="bob"
        Worksheets("Template").Visible = False
    End With
    Application.ScreenUpdating = True
    End Sub
    Attached Files Attached Files

  3. The Following User Says Thank You to Maudibe For This Useful Post:

    swartzjr (2015-08-13)

  4. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    BTW, you can eliminate the need for a range of cells on your sheet for your data validation by using a delimited list:
    1. Select the range of cells in the class column that will need data validation
    2. Data Menu > Data validation > Select "List" from your choices to Allow
    3. In the Source box copy/paste the following: Orietation,ALTA,Torts,Civil Procedure,Contracts
    4. Click OK

    schwartz.png

    HTH,
    Maud

  5. The Following User Says Thank You to Maudibe For This Useful Post:

    swartzjr (2015-08-13)

  6. #4
    New Lounger
    Join Date
    Aug 2015
    Posts
    2
    Thanks
    2
    Thanked 0 Times in 0 Posts
    That's exactly what I was hoping for! Thank you so much for your help and explanations!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •