Try ths code
Private Sub Workbook_Open()
Dim i, j, k As Long
Dim SWA1, SWA2, SWA3 As String
SWA1 = ""
SWA2 = ""
SWA3 = ""
Sheets("UPS").Activate
For j = 2 To Sheets("UPS").Range("a1").End(xlDown).Row
If Sheets("UPS").Cells(j, 2).Value <= DateSerial(Year(Now), Month(Now),
Day(Now)) Then
SWA1 = SWA1 & vbNewLine & Sheets("UPS").Cells(j, 1).Value
End If
Next j
Sheets("Router").Activate
For j = 2 To Sheets("Router").Range("a1").End(xlDown).Row
If CDate(Sheets("Router").Cells(j, 2).Value) <= DateSerial(Year(Now),
Month(Now), Day(Now)) Then
SWA2 = SWA2 & vbNewLine & Sheets("Router").Cells(j, 1).Value
End If
Next j
Sheets("SMPS").Activate
For j = 2 To Sheets("SMPS").Range("a1").End(xlDown).Row
If CDate(Sheets("SMPS").Cells(j, 2).Value) <= DateSerial(Year(Now),
Month(Now), Day(Now)) Then
SWA3 = SWA3 & vbNewLine & Sheets("SMPS").Cells(j, 1).Value
End If
Next j
MsgBox "UPS" & ":-" & vbNewLine & SWA1 & vbNewLine & "------------" &
vbNewLine & "Router" & ":-" & vbNewLine & SWA2 & vbNewLine & "------------"
& vbNewLine & _
"SMPS" & ":-" & vbNewLine & SWA3
Sheets("UPS").Activate
End Sub
--
*Regards*
* *
*Ashish Koul*
*akoul*.*blogspot*.com <http://akoul.blogspot.com/>
*akoul*.wordpress.com <http://akoul.wordpress.com/>
My Linkedin Profile <http://in.linkedin.com/pub/ashish-koul/10/400/830>
P Before printing, think about the environment.
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
