The code I have works great as separate procedures. I decided to combine the 2 procedures as the code is mostly the same with a few differences in which cells are copied to the array to save to different log sheets. Each Procedure has a Inputbox to get the year of interest (2014, 2015, 2016....) that will be used to open AND in the event the Log does not exist Save a new one from a master using the year code in the file name. Based on a cell value I am doing an If_Then_Else to determine to run one procedure or both. If I run them separately as individual procedures they work fine. When I combined them I decided to call one procedure (SaveToLog_Audit) from inside (SaveToLog_Replc). I removed the Date input from (SaveToLog_Audit) letting (SaveToLog_Replc) get that date code and pass it along. I don't want to have (SaveToLog_Audit) ask for a date a second time as it run since I basically want it to be inobtrusive (no need to ask twice when I can pass 'Ans' to it.
I have tried setting public Ans and used Explicit at the beginning with both procedures in same module --> didn't work. I tried Dim Ans As String after option Explicit --> Didn't work. I tried Public Ans as String --> didn't work. Pretty much I couldn't get it to pass the variable 'Ans" no matter what I tried. I'm still pretty new to coding VBA so any help would be great. Showing the basic code, only the parts that have issues. I apologize in advance if I didn't tag this correctly.
Option Explicit
Public Ans As String
Public Sub SaveToLog_Replc()
' Determine scrap and whether to save to Replacement_Credit tracking log
Dim ScrapQty As Range
' Date entry for proper Dated log save
Dim Ans As String
Dim c As Boolean
Dim fs As Object
' Declares path, newFile and fName variables
Dim path As String, newFile As String, fName As String
Set Sheet2 = ActiveSheet
Set ScrapQty = Worksheets("Main").Range("M_Qty_Scrap")
Application.ScreenUpdating = False
' >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Ans = InputBox("Enter Log Year" & _
"" & vbCrLf, "Year Selection", Format(Date, "YYYY"))
If Ans = "" Then
Exit Sub
End If
' >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
' If scrap qty is greater than Zero (0) save to both Logs
If ScrapQty > 0 Then
' If True
MsgBox "Saving to Replacement Log and Audit Log"
' xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
' Save to Audit Log then Save to Replacement Log
' --> 'Ans' VALUE NEEDS TO PASS TO THE PROCEDURE
' --> 'Ans' DOES NOT PASS TO THIS PROCEDURE AT END OF MODULE
---> Call SaveToLog_Audit
' xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
' +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
' Sub routine to Save Data to Replacement Log
' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' Load Array code here for SaveToLog_Replc
' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' ***************************************************************
'Check if Replacement Log exists. If not open Master, rename then save it. If it exists open it.
Set fs = CreateObject("Scripting.FileSystemObject")
' Filename to check for
c = fs.fileExists("S:\RECORDS\Logs\Replacement Log " & Ans & ".xls")
If Not c Then
'MsgBox "The file doesn't exist!"
' File doesn't exist. Open the Blank Master
Workbooks.Open Filename:="S:\RECORDS\Logs\_MASTER Replacement Log.xls"
fName = "Replacement Log " & Ans & ".xls" ' Set fName to new FileName
newFile = fName ' Sets new filename as fName
path = "S:\RECORDS\Logs\" ' Path to Incoming Audit Logs
ActiveWorkbook.SaveAs Filename:=path & newFile ' Saves as newFile
Else
'MsgBox "The file exists! Saving data to it."
Workbooks.Open Filename:= "S:\RECORDS\Logs\Replacement Log " & Ans & ".xls"
End If
' ***************************************************************
' Unprotect Sheet and Show All Data code here
' Find LastRow. Set NextCell position code here
' Set the size of the new array and copy MyAr code here
' Draw Border Code here
' **********************************************
ActiveWorkbook.Save ' Saves Destination Workbook
ActiveWindow.Close ' Closes Destination Workbook
Application.ScreenUpdating = True
' Confirms Save to Log File
MsgBox "Your Data has been saved to the Log File: " & vbCrLf & vbCrLf _
& "'Replacement Log " & Ans & ".xls'", vbInformation, "Log Save Confirmation"
' +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Else
' If False
MsgBox "Saving to Audit Log Only."
' xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
' Save to Audit Log as Normal
' --> 'Ans' VALUE NEEDS TO PASS TO THE PROCEDURE
' --> 'Ans' DOES NOT PASS TO THIS PROCEDURE AT END OF MODULE
---> Call SaveToLog_Audit
' xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
End If
End Sub
----------------------------------------------------------------------------------------
Public Sub SaveToLog_Audit()
' Date entry for proper Dated log save
Dim Ans As String
Dim c As Boolean
Dim fs As Object
' Declares path, newFile and fName variables
Dim path As String, newFile As String, fName As String
Set Sheet2 = ActiveSheet
Application.ScreenUpdating = False
' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' Load Array code here
' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' For debug
' --> 'Ans' DOES NOT PASS FROM (SaveToLog_Replc) PROCEDURE IN THIS MODULE
MsgBox "The value of Ans is: " & vbCrLf & vbCrLf & "'" & Ans & "'"
' ********************************************************
' Checks if Log File exists. If not open the Master, rename then save it. If it exists open it.
Set fs = CreateObject("Scripting.FileSystemObject")
' Filename to check for
c = fs.fileExists("S:\RECORDS\Logs\" & Ans & " Audit Log.xls")
If Not c Then
'MsgBox "The file doesn't exist!" original code
' File doesn't exist. Open the Blank Master
Workbooks.Open Filename:="S:\RECORDS\Logs\_Master Audit Log.xls"
' Set fName to new FileName
fName = Ans & " Audit Log.xls"
' Sets new filename as fName saves to this directory
newFile = fName
' Path to Incoming Audit Logs
path = "S:\RECORDS\Logs\"
' Saves Blank Master as newFile
ActiveWorkbook.SaveAs Filename:=path & newFile
Else
'Workbooks.Open "C:\filename.xls"
Workbooks.Open Filename:= _
"S:\RECORDS\Logs\" & Ans & " Audit Log.xls"
End If
' ********************************************************
' Unprotect Sheet and Show All Data code here
' Find LastRow. Set NextCell position code here
' Set the size of the new array and copy MyAr code here
' Draw Border Code here
' **********************************************
ActiveWorkbook.Save ' Saves Destination Workbook
ActiveWindow.Close ' Closes Destination Workbook
Application.ScreenUpdating = True
' Confirms Save to Log File
MsgBox "Your Data has been saved to the Log File: " & vbCrLf & vbCrLf _
& "'" & Ans & " Audit Log.xls'", vbInformation, "Log Save Confirmation"
End Sub
Bookmarks