G
Guest
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
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