Maintaining cell 'variable' values even after the the macro has en

[tomwashere2]

I currently have a macro that pulls data based on variables stored in cells
on a worksheet such as range("a5") which might be a Windows Login ID etc.

The problem is that when the program ends... It wipes out the data stored
on those cells on the worksheet. I would like it to not wipe out the data
because sometimes I need to run the program more than once. I also don't
want to have to declare a variable and prompt the user for this info
everytime.

**************
My Code ...
**************
Dim URLb, URLc, Label1 As String
URLb = "http://webfiles.west.corp.com/CustomerService/KMS/signon/"

Dim Year, Year2 As String, ValueYear As Integer
Dim Month, Month2, Month3 As String, ValueMonth As Integer
Dim Day As String, ValueDay As Integer
Dim MandD, UserNT, Supe As String

UserNT = Range("C4") ' **** these are the cells/variables that are
lost
Supe = Range("C5")
ValueYear = Range("C6")
ValueMonth = Range("C7")
ValueDay = Range("C8")


Select Case ValueYear
Case Is = 2004
Year = "2004-aa/"
Year2 = "2004"
Case Is = 2005
Year = "2005-aa/"
Year2 = "2005"
Case Is = 2006
Year = "2006-aa/"
Year2 = "2006"
Case Is = 2007
Year = "2007-aa/"
Year2 = "2007"
Case Is = 2008
Year = "2008-aa/"
Year2 = "2008"
Case Is = 2009
Year = "2009-aa/"
Year2 = "2009"
Case Is = 2010
Year = "2010-aa/"
Year2 = "2010"
Case Else 'For invalid entrees
MsgBox ("Please enter a valid year such as 2005. Note, this program only
works between 2004-2010")
End
End Select


'Promt for month value and capture using Select Case structure;

Select Case ValueMonth
Case Is = 1
Month = "01-Jan"
Month2 = "-Jan/"
Month3 = "01"
Case Is = 2
Month = "02-Feb"
Month2 = "-Feb/"
Month3 = "02"
Case Is = 3
Month = "03-Mar"
Month2 = "-Mar/"
Month3 = "03"
Case Is = 4
Month = "04-Apr"
Month2 = "-Apr/"
Month3 = "04"
Case Is = 5
Month = "05-May"
Month2 = "-May/"
Month3 = "05"
Case Is = 6
Month = "06-Jun"
Month2 = "-Jun/"
Month3 = "06"
Case Is = 7
Month = "07-Jul"
Month2 = "-Jul/"
Month3 = "07"
Case Is = 8
Month = "08-Aug"
Month2 = "-Aug/"
Month3 = "08"
Case Is = 9
Month = "09-Sep"
Month2 = "-Sep/"
Month3 = "09"
Case Is = 10
Month = "10-Oct"
Month2 = "-Oct/"
Month3 = "10"
Case Is = 11
Month = "11-Nov"
Month2 = "-Nov/"
Month3 = "11"
Case Is = 12
Month = "12-Dec"
Month2 = "-Dec/"
Month3 = "12"
Case Else 'For invalid entrees
MsgBox ("Please enter a valid month (i.e. 02 for February)")
End
End Select

'Promt for day value and capture using Select Case structure;


If ValueDay < 0 Or ValueDay > 31 Then
MsgBox ("Please enter a valid number for the day i.e.(1 - 31). It is also
possible that there was no one here for the team entered for the day
entered.")
End
Else
If ValueDay > 0 And ValueDay < 10 Then
Day = "0" & ValueDay
Else
Day = ValueDay

End If
End If

' Execute regardless of Day Val (unless invalid val is entered)
Dim URLd As String

MandD = Month3 & Month2
signon = "dsignon" & Year2 & Month3 & Day & ".xls"
URLc = (URLb & Year & MandD & signon)



Workbooks.Open (URLc)


' Old

Windows(URLc).Activate
Sheets("SLC").Activate
Selection.AutoFilter Field:=2, Criteria1:=(Supe)

' Select Data
Windows("FormulaSheet.xls").Activate
Windows(URLc).Activate
Windows("FormulaSheet.xls").Activate
Columns("A:I").Select
Range("I1").Activate
Selection.ClearContents
Range("A2").Select

' Get Data and Paste
Windows(URLc).Activate
Range("A5:I65531").Select
Selection.Copy
Windows("FormulaSheet.xls").Activate
Sheets("Sheet1").Select
Range("A1").Select
ActiveSheet.Paste

Application.DisplayAlerts = False
Workbooks(URLc).Close SaveChanges:=False

' Kill Switch
Windows("FormulaSheet.xls").Activate
Sheets("Sheet1").Activate
Dim Test1, Test2 As Integer
Test1 = 0
Test2 = Range("D2")

If Test2 > Test1 Then
' Run
Sheets("Run").Select

Range("A2").Select
Selection.End(xlDown).Select
Range("A73").Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select

Workbooks.Open Filename:= _
"C:\Documents and Settings\" & UserNT & "\My Documents\Result.xls"

Windows("Result.xls").Activate
Application.Run "Result.xls!FormatResult"
Else
Sheets("Run").Select
MsgBox ("You have either entered a SUPERVISOR NAME incorrectly or it
could be that there was no one here for the team and date entered on the
sign-on report.")
End If
End Sub

 

[Norman Jones]

Hi TomWasHere2,

The problem is that when the program ends... It wipes out the data stored
on those cells on the worksheet. I would like it to not wipe out the data
because sometimes I need to run the program more than once. I also don't
want to have to declare a variable and prompt the user for this info
everytime.

Add the following new procedure:

'=============================>>
Public Sub CheckNames()
Dim nm_UserNT As Name
Dim nm_Supe As Name
Dim nm_ValueYear As Name
Dim nm_ValueMonth As Name
Dim nm_ValueDay As Name
Dim WB As Workbook

Set WB = ActiveWorkbook 'Workbooks("YourWorkbookName.xls")

With WB

On Error Resume Next
Set nm_UserNT = .Names("UserNT")
Set nm_Supe = .Names("Supe")
Set nm_ValueYear = .Names("ValueYear")
Set nm_ValueMonth = .Names("ValueMonth")
Set nm_ValueDay = .Names("ValueDay")
On Error GoTo 0

If nm_UserNT Is Nothing Then Set nm_UserNT = _
.Names.Add(Name:="UserNT ", _
RefersTo:=Range("C4").Value, _
Visible:=False)

If nm_Supe Is Nothing Then Set nm_Supe = _
.Names.Add(Name:="Supe", _
RefersTo:=Range("C5").Value, _
Visible:=False)

If nm_ValueYear Is Nothing Then Set nm_ValueYear = _
.Names.Add(Name:="ValueYear", _
RefersTo:=Range("C6").Value, _
Visible:=False)

If nm_ValueMonth Is Nothing Then Set nm_ValueMonth = _
.Names.Add(Name:="ValueMonth", _
RefersTo:=Range("C7").Value)

If nm_ValueDay Is Nothing Then Set nm_ValueDay = _
.Names.Add(Name:="ValueDay", _
RefersTo:=Range("C8").Value, _
Visible:=False)

End With

WB.Save

End Sub
'<<=============================

Then, in your existing code replace:

UserNT = Range("C4") ' **** these are the cells/variables that
are
lost
Supe = Range("C5")
ValueYear = Range("C6")
ValueMonth = Range("C7")
ValueDay = Range("C8")

With:

'==========================>>
'\\ Replacement code

UserNT = Names("UserNT").RefersTo
Supe = Names("Supe").RefersTo
ValueYear = Names("ValueYear").RefersTo
ValueMonth = Names("ValueMonth").RefersTo
ValueDay = Names("ValueDay").RefersTo

Call CheckNames

'\\ end of replacement code
'==========================>>

The purpose of the above is to read the C4:C8 data into hidden defined names
to overcome the erasure of this data when all data in columns A:I is
cleared in your code.

It may well be that these defined names could be set up directly from the
user's responses, but you do not show this code. In any event, should you
wish to do so, it would be a simple task to adapt my CheckNames procedure.

Test the suggested approach on a copy of your workbook.