Solved

Sheet references removed from metrics when copy is done

Posted on 2014-11-05
5
132 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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

744 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

16 Experts available now in Live!

Get 1:1 Help Now