troubleshooting Question

Need VBA Formula to add reference to cells in new worksheet

Avatar of Jeffrey Smith
Jeffrey SmithFlag for United States of America asked on
Microsoft Excel
4 Comments1 Solution551 ViewsLast Modified:
Hi Experts,

I'm struggling with trying to add a VBA Formula that refers to designated cells on a new worksheet that was added via code earlier in the procedure.  My existing code (below) is returning (on a separate "Summary" worksheet) literal strings like "= strNewWS3 & D4" instead of translating same to the newly added (& renamed) sheet name, followed by an "!" and the referenced Cell value in the new sheet, and then returning the value in the referenced Cell.

Sub add_Sheet()
    
    Dim strNewWS, strNewWS2 As String, strNewWS3 As String
    Dim iLastRow As Long
    Dim i As Integer
    Dim myCheck As Boolean
    
   On Error GoTo add_Sheet_Error

    strNewWS = ActiveSheet.Cells(3, 4).Value
    strNewWS2 = strNewWS
    i = 2
    Do
        myCheck = False
        For Each wsh In Worksheets
            If wsh.Name = strNewWS2 Then
                strNewWS2 = strNewWS & " (" & i & ")"
                myCheck = True
            End If
        Next wsh
        i = i + 1
    Loop Until myCheck = False
    strNewWS = strNewWS2
    
    Sheets("Blank").Copy After:=Sheets(Sheets.Count)
     
    Sheets(Sheets.Count).Name = strNewWS
    
    strNewWS3 = strNewWS & "!"
    
    With Sheets("Summary")
    
    iLastRow = .Range("A" & Rows.Count).End(xlUp).Row
    
    .Range("A" & iLastRow + 1).Formula = "= strNewWS3 & D4"
    .Range("B" & iLastRow + 1).Formula = "= strNewWS3 & D10"
    .Range("C" & iLastRow + 1).Formula = "= strNewWS3 & Q17"
    .Range("D" & iLastRow + 1).Formula = "= strNewWS3 & Q25"
    .Range("E" & iLastRow + 1).Formula = "= strNewWS3 & Q28"
    
    End With
    
    SortWorksheets

   On Error GoTo 0
   Exit Sub

add_Sheet_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure add_Sheet of Module Module1"
    
End Sub

I appreciate any insights.

Jeff
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros