Solved

Sheet references removed from metrics when copy is done

Posted on 2014-11-05
5
134 Views
Last Modified: 2014-11-07
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
0
Comment
Question by:Kathtg
  • 4
5 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 40425836
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
 

Author Comment

by:Kathtg
ID: 40426290
ah, this line -

Sheets(CopySheetName).Delete

I'll remove it and only copy, and look for a replace command instead
0
 

Author Comment

by:Kathtg
ID: 40426443
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
 

Author Comment

by:Kathtg
ID: 40428536
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
 

Author Closing Comment

by:Kathtg
ID: 40428541
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now