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.

Need to see the code to find what is removing the cell data.

Mike F
"tomwashere2" wrote in message
...
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.

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 ' Declare Day Variables
Dim MandD, UserNT, Supe As String

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

UserNT = Range("C4")
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").Select
Range(Selection, Selection.End(xlToRight)).Select
Range("A5:I5").Select
Range(Selection, Selection.End(xlDown)).Select
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\SpecialFormat.xls"

Windows("SpecialFormat.xls").Activate
Application.Run "SpecialFormat.xls!SubR"
Else
Sheets("Run").Select
MsgBox ("Please enter a valid Supe Name")
End If
End Sub


"Mike Fogleman" wrote:

Need to see the code to find what is removing the cell data.

Mike F
"tomwashere2" wrote in message
...
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.