Sheet references removed from metrics when copy is done

I have two workbooks.  
  -  One workbook has the following sheets:  Metrics, tester 1, tester 2  
   - The second workbook has the following sheets:  Tester 1, tester 2

The tester works off of the second workbook.  Once they have completed adding their information, they hit a copy button and it automatically copies the entire sheet to the first workbook.  The sheet name remains the same in both workbooks.

The first workbook has a Metrics tab that contains formulas counting information on the tester 1, and tester 2 tabs.  The problem is that when the new sheet is copied in, the references to those sheets in all formulas are removed.  Is there a way to stop it from removing these references?

here is the code to the copy button:
Private Sub AutomaticCopy_Click()

    Dim docCheckOut As String
    Dim CopySheetName As String
    Dim CopyFromWorkbookName As String
    Dim Msg As String, Ans As Variant

    docCheckOut = "https://sharepoint.siriusware.com/Production/testing/4401%20Test%20Assets/Test%20Cases_Trina.xlsm"
    CopyToWorkbookName = "Release 4_4_01_01 Test Cases.xlsm"
    CopySheetName = ActiveSheet.Name
    CopyFromWorkbookName = ActiveWorkbook.Name
   
   
    Msg = "This will copy the '" & CopySheetName & "' sheet to the '" & CopyToWorkbookName & "' workbook.  Are you sure?"
    Ans = MsgBox(Msg, vbYesNo)
    Application.DisplayAlerts = False
    Select Case Ans
    Case vbYes
         
       
    'docCheckOut = Application.InputBox( _
    'prompt:="Enter the full location path and name of the SharePoint document to copy the sheet within")

    'CopySheetName = Application.InputBox( _
    'prompt:="Enter the name of the sheet to be copied")
   
    'CopyFromWorkbookName = Application.InputBox( _
    'prompt:="Enter the name of the Workbook to copy the sheet FROM")
   
    'CopyToWorkbookName = Application.InputBox( _
    'prompt:="Enter the name of the Workbook to copy the sheet INTO")
   
     'Check Out the Workbook - if not already checked out.
        If Workbooks.CanCheckOut(docCheckOut) = True Then
            Workbooks.CheckOut docCheckOut
        Else
            MsgBox "Unable to check out this document at this time.  This document is already checked out."
        End If
   
    'Now open the workbook that was checked out.
        Workbooks.Open Filename:= _
        docCheckOut, UpdateLinks:=xlUpdateLinksAlways
       
    ' Delete the sheet in the new workbook
        Application.DisplayAlerts = False
        Application.ScreenUpdating = False
        On Error Resume Next
        Sheets(CopySheetName).Delete
       ' On Error GoTo 0
       
       
     ' Copy the sheet to the functional area workbook
   
        Windows(CopyFromWorkbookName).Activate
          Sheets(CopySheetName).Select
        Sheets(CopySheetName).Copy Before:=Workbooks(CopyToWorkbookName).Sheets(4)
       
               
    'Delete the command button located on the test sheet from the ReleaseTests workbook
       ActiveSheet.Shapes.Range(Array("AutomaticCopy")).Select
       Selection.Delete
       ActiveSheet.Shapes.Range(Array("GenericCopy")).Select
       Selection.Delete
     
       
    'Save and Check In the Workbook
    Windows(CopyToWorkbookName).Activate
    ActiveWorkbook.Save
    If ActiveWorkbook.CanCheckIn = True Then
        ActiveWorkbook.CheckIn
   
     
        Else
    'Save the ReleaseTests workbook and close it
        Windows(CopyToWorkbookName).Activate
        ActiveWorkbook.Save
        ActiveWorkbook.Close
        MsgBox "Unable to check in this document.  An error has occurred. Sheet was saved and copied."
         
     End If
     
     MsgBox "The sheet has been successfully saved and copied.  Please verify."
     
               
    Case vbNo
    GoTo Quit:
    End Select
Quit:
End Sub
KathtgAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rory ArchibaldCommented:
Instead of deleting the sheets from the target workbook, you should copy all cells from the source to A1 on the Target, then the formulas will remain intact.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
KathtgAuthor Commented:
ah, this line -

Sheets(CopySheetName).Delete

I'll remove it and only copy, and look for a replace command instead
0
KathtgAuthor Commented:
Ok, I have no idea how to do that suggestion.  I've decided to try and change this function into a Countifs to use my formula instead:

Function myCountIf(rng As Range, criteria) As Long
    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Metrics" And ws.Name <> "TFSData" Then
            myCountIf = myCountIf + WorksheetFunction.CountIf(ws.Range(rng.Address), criteria)
        End If
    Next ws
End Function

It is likely a cleaner way to do it.  I tried changing the myCountIf line to:

myCountIfs = myCountIfs + WorksheetFunction.CountIfs(ws.Range(rng.Address), criteria, ws.Range(rng.Address), criteria)  

but it doesn't work.  Anyone know what is wrong with it?  This function will loop through every sheet except TFSData and Metrics.
0
KathtgAuthor Commented:
I couldn't figure this one out so I decided to change the formulas instead - turns out this seems to be a better idea as it will make the formulas dynamic and not sheet dependent
0
KathtgAuthor Commented:
I'm sure the solution was correct but I needed to understand more than the one sentence telling me the action to take to execute.  I didn't adopt that solution because I couldn't figure it out.  I went another direction.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.