Combining snippets of VBA to create new sheet and add formatted cells to another page.

Not honestly certain I will be able to explain this very well but I try. I managed to create my very own code to Create a new sheet, and name the sheet from a userform. Now as far as that part goes I do not need any assistance as of yet. However, I am trying to have 4 items from the userform added to another sheet ("Welcome"). My problem is I cant seem to find the last row and skip a row to input the data and formatting. Not sure what to give here so I will give the three snippets. One is from a recorded macro for the formatting I tried to edit.

This first one is what I think or thought I had to add in order to get the values from the textbox's to show in the welcome sheet

   With Worksheets("Welcome")
      .Cells(emptyRow, 1).Value = txtTerm.Value
      .Cells(emptyRow, 2).Value = "Course Names:"
      .Cells(emptyRow, 3).Value = txtCourse1.Value
      .Cells(emptyRow, 4).Value = txtCourse2.Value
      .Cells(emptyRow, 5).Value = txtCourse3.Value
   End With

Here is the recorded macro that I may or not have messed up trying to edit it.

    Sheets("Welcome").Select
    Range("A" & Rows.Count).End(xlUp).Offset(2).Select
    Dim xlEdgeType As Variant
    
    '******* This is a single cell which Should also have Term Name *********
    For Each xlEdgeType In Array(xlEdgeLeft, xlEdgeTop, xlEdgeRight, _
            xlEdgeBottom, xlInsideVertical, xlInsideHorizontal)
        With ActiveSheet.Range("A26").Borders(xlEdgeType)
            .LineStyle = xlContinuous
            .ThemeColor = 2
            .TintAndShade = 0
            .Weight = xlThin
        End With
    '******* This is for the above single cell to color it green *********
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent6
            .TintAndShade = 0.799981688894314
            .PatternTintAndShade = 0
        End With
    Next xlEdgeType
    '******* This is for 4 rows 8 Columns with borders around them *********
    For Each xlEdgeType In Array(xlEdgeLeft, xlEdgeTop, xlEdgeRight, _
            xlEdgeBottom, xlInsideVertical, xlInsideHorizontal)
        With ActiveSheet.Range("A:H").Borders(xlEdgeType)
            .LineStyle = xlContinuous
            .ThemeColor = 2
            .TintAndShade = 0
            .Weight = xlThin
        End With
    Next xlEdgeType

I need both of snippets somehow combined with this part which is for the form that creates the new worksheet.

Private Sub shtCreate_Click()
 
 Sheets("MasterSheet").Visible = True
 Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = txtTerm.Value
  
 If SelCourse1.Value = "Weighted" Then
 Sheets("MasterSheet").Columns("G:L").Copy Destination:=Sheets(txtTerm.Value).Range("A1")
 Range("A1").Value = txtCourse1.Text
 Else:
 Sheets("MasterSheet").Columns("A:F").Copy Destination:=Sheets(txtTerm.Value).Range("A1")
 Range("A1").Value = txtCourse1.Text
 End If
 
 If SelCourse2.Value = "Weighted" Then
 Sheets("MasterSheet").Columns("S:X").Copy Destination:=Sheets(txtTerm.Value).Range("G1")
 Range("G1").Value = txtCourse2.Text
 Else:
 Sheets("MasterSheet").Columns("M:R").Copy Destination:=Sheets(txtTerm.Value).Range("G1")
 Range("G1").Value = txtCourse2.Text
 End If
 
 If SelCourse3.Value = "Weighted" Then
 Sheets("MasterSheet").Columns("AE:AJ").Copy Destination:=Sheets(txtTerm.Value).Range("M1")
 Range("M1").Value = txtCourse3.Text
 Else:
 Sheets("MasterSheet").Columns("Y:AD").Copy Destination:=Sheets(txtTerm.Value).Range("M1")
 Range("M1").Value = txtCourse3.Text
 End If

 Sheets("MasterSheet").Visible = False
    Unload Me
End Sub

Hopefully this all made sense and someone can lend me hand here is the workbook
https://www.dropbox.com/s/rgliaz7ef1...cept.xlsm?dl=0


Modified the last piece of code to a more efficient version it copies the corresponding columns and pastes them instead of copying the page and hide the columns I don't need.

Add something like this...

    Dim NextRow As Long
    
    Sheets("Welcome").Select
    
    NextRow = Range("A" & Rows.Count).End(xlUp).Offset(2).Row
    If NextRow < 8 Then NextRow = 8
    
    '******* This is a single cell which Should also have Term Name *********
    With Range("A" & NextRow)
        With .Borders
            .LineStyle = xlContinuous
            .ThemeColor = 2
            .TintAndShade = 0
            .Weight = xlThin
        End With
        '******* This is for the above single cell to color it green *********
        With .Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent6
            .TintAndShade = 0.799981688894314
            .PatternTintAndShade = 0
        End With
         '******* This is for 4 rows 8 Columns wi (relative to Term Name cell) *********
        With .Range("A2:H5").Borders
            .LineStyle = xlContinuous
            .ThemeColor = 2
            .TintAndShade = 0
            .Weight = xlThin
        End With
        
        'Column Labels
        With .Range("C2:H2")
            .Value = Array("Points Received", "Points Possible", "GPA", "Weighted", "Percentage", "Letter Grade")
            .HorizontalAlignment = xlCenter
        End With
        
        'Data
        .Range("A1").Value = txtTerm.Value
        .Range("A2").Value = "Course Names:"
        .Range("A3").Value = txtCourse1.Value
        .Range("A4").Value = txtCourse2.Value
        .Range("A5").Value = txtCourse3.Value
        
    End With

Originally Posted by Mr_Bill

The three rows of empty cells will all have a formula like ='Fall 2014'!E2 and to write that I thinking will need something like "="txtTerm.Value"!E2" just guessing

You're welcome.

Very close. Try Something like...
"=" & txtTerm.Value & "!E2"

Or this to put the Value instead of a formula
.Range("C3").Value = Sheets(txtTerm.Value).Range("E2").Value

Originally Posted by Mr_Bill

Would you happen to know how I could make this line .Range("A1").Value = txtTerm.Value a link to that sheet and go in the same place as it would have if was just the text?

I understand the hyperlink to the sheet part. It don't get the "same place as it would have if was just the text" part.

This creates a hyperlink to sheet txtTerm.Value Range A1

    ActiveSheet.Hyperlinks.Add Anchor:=.Range("A1"), _
                               Address:="", _
                               SubAddress:=Sheets(txtTerm.Value).Range("A1").Address(0, 0, xlA1, True), _
                               TextToDisplay:=Sheets(txtTerm.Value).Range("A1").Value