[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 288
  • Last Modified:

VBA code is not copied over

Hello,

I've written the following code that I use to replace user workbooks with the latest version.  The only problem is any code changes are not being copied over.  For example, if I make a code change in the master workbooks, that change is not being reflected in the user workbooks.

Public Sub ReplaceAllUserWorkbooks()
    ' Comments:
    ' Params  :
    ' Created : 08/16/12 08:48 JV
    ' Modified:
    

    On Error GoTo PROC_ERR
    
    Dim strPathOfSelectedFolder As String
    Dim strFilter As String
    Dim strDestinationPath As String
    Dim strSourcePath As String
    Dim strSilo As String
        
    If Left(ThisWorkbook.Name, 6) <> "EA_IT2" Then
        MsgBox "The Update All Workbooks process is not enabled for this workbook", vbOKOnly, "Macro Disabled"
        Exit Sub
    End If
    
    strPathOfSelectedFolder = Mid(ThisWorkbook.FullName, 1, InStrRev(ThisWorkbook.FullName, "\"))
    'Set wbSource = ThisWorkbook
   'strSourcePath = ThisWorkbook.FullName
    'Replace ProjectID worksheet in each silo workbook with updated projectid worksheet
    strFilter = Dir(strPathOfSelectedFolder) 'change or add formats
    Do While strFilter <> "" 'will start LOOP until all files in folder sPath have been looped through
        strSilo = Left(strFilter, 6)
        Select Case strSilo
            Case "MFS_EA", "WOS_EA", "AFS_EA", "SGP_EA", "TRS_EA", "RES_EA"
                strDestinationPath = strPathOfSelectedFolder & strFilter
                Set wbDestination = ThisWorkbook
                wbDestination.SaveCopyAs strDestinationPath
                
                Call ProtectWorksheetsInWorkbook
                'wbDestination.Close
                'Set wbDestination = Nothing
            Case Else
                'Do Nothing
        End Select
        strFilter = Dir
    Loop ' End of LOOP
    MsgBox "All user JE_Upload workbooks have been update. Backup starting.", vbOKOnly, "Update Complete"
    Call BackupPreviousTemplate

   
PROC_EXIT:
    Exit Sub

PROC_ERR:
    MsgBox Err.Description, vbCritical, "Admin.ReplaceAllUserWorkbooks"
    Resume PROC_EXIT

Open in new window

0
chtullu135
Asked:
chtullu135
  • 3
2 Solutions
 
Anthony BerenguelCommented:
Not sure if this is they problem but Make sure that your code is NOT saved in your personal macro file, but rather in the workbook itself.

From the visual basic editor window press ctrl+r to view the projects of your open workbooks. Make sure the code your other users need to access is saved in the workbook they will be using, not your personal macro file.
0
 
chtullu135Author Commented:
Hello aebea,

Attached is a screen shot of the vba project window.  It looks fine to me
VBA-Project.bmp
0
 
chtullu135Author Commented:
I found the problem.  I am executing the code from a command button on the excel sheet.  I was calling the wrong macro.  I forgot to point the command button to the newer macro. I'm going to give aebea credit for responding with information that will be usefull in the future
0
 
chtullu135Author Commented:
I found the problem.  I am executing the code from a command button on the excel sheet.  I found that I was calling the wrong macro.  I forgot to point the command button to the newer macro. I'm going to give aebea credit for responding with information that will be usefull in the future
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now