add to current macro - link to cell on newly added worksheet

[HRIS]

I have created a macro to add a new entry, via inputbox, to my "Summary Sheet" and then add a worksheet named after that new entry. I am trying to find a way to link cells in the same row as the new entry on the Summary Sheet to corresponding cells in my newly added sheet. My problem (I think ) is referencing a sheet for which I won't know the name until after it is created.

One other thing I should add - I may have to adjust it later to be a formula based upon the data in the cell on the newly added sheet, rather than just being equal to the cell.

Any suggestions are appreciated! My code is below:

Sub TestAttendance()
' TestAttendance Macro
 
    Rows("4:4").Select
    Selection.Insert Shift:=xlDown
    Range("A4").Select
    ActiveCell.FormulaR1C1 = InputBox("EMPLOYEE NAME (Example: Doe,John S.)")
 
    If ActiveCell.FormulaR1C1 <> vbNullString Then
        Range("A4:B4").Select
            With Selection
                Selection.Merge
                .HorizontalAlignment = xlLeft
            End With
        Sheets("Attendance").Select
        Cells.Select
        Selection.Copy
        Sheets("Attendance").Select
        Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = Sheets("Summary Sheet").Range("A4")
        Cells.Select
        Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False
        Range("D3").Select
        ActiveCell.FormulaR1C1 = Sheets("Summary Sheet").Range("A4")
        Sheets("Summary Sheet").Select
        Range("B4").FormulaR1C1 = "=MySheet!R[-1]C[3]"
        Range("C4").FormulaR1C1 = "='MySheet'!R[68]C[33]"
        Range("D4").FormulaR1C1 = "='MySheet'!R[69]C[32]"
        Range("E4").FormulaR1C1 = "='MySheet'!R[70]C[31]"
        Range("F4").FormulaR1C1 = "='MySheet'!R[67]C[30]"
        Range("G4").FormulaR1C1 = "=SUM(RC[-4]:RC[-1])"
        Range("A1").Select
    ElseIf ActiveCell.FormulaR1C1 = vbNullString Then Exit Sub
    End If
End Sub

 

[JoeMo]

Not sure I understand specifically what you are requesting, but I did clean up your code so it's not wasting so much time selecting cells and sheets. Try this on a copy of your workbook. Untested by me so it may need some tweaking.

Sub TestAttendance()
' TestAttendance Macro
 
    Rows("4:4").Insert Shift:=xlDown
    Range("A4").FormulaR1C1 = InputBox("EMPLOYEE NAME (Example: Doe,John S.)")
 
    If Range("A4").Value <> vbNullString Then
        With Range("A4:B4")
            .Merge
            .HorizontalAlignment = xlLeft
        End With
        With Sheets("Attendance")
            .Cells.Copy
        End With
        Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = Sheets("Summary Sheet").Range("A4")
        Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False
        Range("D3").FormulaR1C1 = Sheets("Summary Sheet").Range("A4")
        With Sheets("Summary Sheet")
            .Activate
            .Range("B4").FormulaR1C1 = "=MySheet!R[-1]C[3]"
            .Range("C4").FormulaR1C1 = "=MySheet!R[68]C[33]"
            .Range("D4").FormulaR1C1 = "=MySheet!R[69]C[32]"
            .Range("E4").FormulaR1C1 = "=MySheet!R[70]C[31]"
            .Range("F4").FormulaR1C1 = "=MySheet!R[67]C[30]"
            .Range("G4").FormulaR1C1 = "=SUM(RC[-4]:RC[-1])"
            .Range("A1").Select
        End With
    
    End If
End Sub

 

[HRIS]

Not sure I understand specifically what you are requesting, but I did clean up your code so it's not wasting so much time selecting cells and sheets. Try this on a copy of your workbook. Untested by me so it may need some tweaking.

Thank you for the clean up. I tried it out and it worked great!

Hopefully I can clarify on my question.
Summary of the macro: the macro inserts a line onto my summary sheet and asks the user (via inputbox) the name of the employee, then adds a sheet and names it based upon the cell where the inputbox entered the employee name. At the end of my macro I am wanting to input a formula on my summary sheet that returns the exact same data as a cell on my newly added sheet, or a variation of that data.

Examples:
I want C4 on my summary sheet to show the data from AJ70 on my new sheet.

I want D4 on my summary sheet to show 1/3 * the new sheet's AJ71.

I would like these to update if data is changed on the added sheets.

I apologize if I'm still not being clear! I appreciate you taking the time to look at my macro!

 

[JoeMo]

Thank you for the clean up. I tried it out and it worked great!

Hopefully I can clarify on my question.
Summary of the macro: the macro inserts a line onto my summary sheet and asks the user (via inputbox) the name of the employee, then adds a sheet and names it based upon the cell where the inputbox entered the employee name. At the end of my macro I am wanting to input a formula on my summary sheet that returns the exact same data as a cell on my newly added sheet, or a variation of that data.

Examples:
I want C4 on my summary sheet to show the data from AJ70 on my new sheet.

I want D4 on my summary sheet to show 1/3 * the new sheet's AJ71.

I would like these to update if data is changed on the added sheets.

I apologize if I'm still not being clear! I appreciate you taking the time to look at my macro!

Is that what the formulas at the end of the macro are intended to do? You have named the new sheet earlier in the macro, so it's available by name. You can just use that name followed by ! if the name contains no spaces or use ' (apostrophes) to surround the name and follow the right ' with ! if the name has spaces in it.

 

[HRIS]

I apologize, but I'm pretty new to VBA and am not sure how to reference the sheet name. You are correct, the formulas w/ "MySheetName" are where i'm wanting it to go. I don't have a specific sheet name, rather it would be whatever the user puts in the inputbox. Everything I've tried to put there to reference that variable sheet name has not worked out...

Can you provide further direction for my very simple understanding of how this works?

Thank you again!

 

[JoeMo]

I apologize, but I'm pretty new to VBA and am not sure how to reference the sheet name. You are correct, the formulas w/ "MySheetName" are where i'm wanting it to go. I don't have a specific sheet name, rather it would be whatever the user puts in the inputbox. Everything I've tried to put there to reference that variable sheet name has not worked out...

Can you provide further direction for my very simple understanding of how this works?

Thank you again!

Try this:

Sub TestAttendance()
' TestAttendance Macro
Dim mySht As Worksheet

    Rows("4:4").Insert Shift:=xlDown
    Range("A4").Value = InputBox("EMPLOYEE NAME (Example: Doe,John S.)")
 
    If Range("A4").Value <> vbNullString Then
        With Range("A4:B4")
            .Merge
            .HorizontalAlignment = xlLeft
        End With
        With Sheets("Attendance")
            .Cells.Copy
        End With
        Set mySht = Worksheets.Add(After:=Worksheets(Worksheets.Count))
        With mySht
            .Name = Sheets("Summary Sheet").Range("A4").Value
            .Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
                False, Transpose:=False
            .Range("D3").FormulaR1C1 = Sheets("Summary Sheet").Range("A4")
        End With
        Set mySht = ActiveSheet
        With Sheets("Summary Sheet")
            .Activate
            .Range("B4").FormulaR1C1 = "='" & mySht.Name & "'!R[-1]C[3]"
            .Range("C4").FormulaR1C1 = "='" & mySht.Name & "'!R[68]C[33]"
            .Range("D4").FormulaR1C1 = "='" & mySht.Name & "'!R[69]C[32]"
            .Range("E4").FormulaR1C1 = "='" & mySht.Name & "'!R[70]C[31]"
            .Range("F4").FormulaR1C1 = "='" & mySht.Name & "'!R[67]C[30]"
            .Range("G4").FormulaR1C1 = "=SUM(RC[-4]:RC[-1])"
            .Range("A1").Select
        End With
    
    End If
End Sub

 

[HRIS]

This worked perfectly! Thank you for all of your help and patience!!!