Need Urgent help if possible please help. Lookup the workbook name.

[pedie]

I have code

Sub try121()
Dim wb As Workbook
Windows("[COLOR=blue]Backup Data September 28, 2010.xlsm[/COLOR]").Activate
Sheets("Dater").Select
LstRw = Cells(Rows.Count, "O").End(xlUp).Row
  Range("O1:O" & LstRw).AutoFilter Field:=1, Criteria1:="Y"
  Range("B2:B" & LstRw).SpecialCells(xlCellTypeVisible).Copy _
   ThisWorkbook.Sheets("Data").Cells(Rows.Count, "G").End(xlUp)(2)
  AutoFilterMode = False
ThisWorkbook.Activate
End Sub

The thing is that my file name changes every day ("Backup Data September 28, 2010.xlsm") just the date.
Can some hel me set up how to look for the workbook and select
like Backup Data & month & i &,.xlsm etc???

Not sure how to set this question up...

i want to be able to look for file name through loop or something....
yesterday someone gave me this code which might help but not sure how to set this up.

Sub try2()
Dim ws As Worksheet
For i = 1 To 30  'to  6? to whatever.
  On Error Resume Next
  Set ws = Workbooks("Backup Data September (" & i & "), 2010.xls").ActiveSheet
  On Error GoTo 0
  If Not ws Is Nothing Then
    Workbooks("Try.xlsm").Activate
    ws.Range("A1").Copy
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Exit For
  End If
Next i
End Sub

Thanks for helping!

 

[pedie]

Hi Brian, thanks for helping!

Actually the file is already open so i dont have to open it again. But I sure do need your help. Is the code below the right way it is coded??? Asumming Setember 12, 2010.xlsm is the file name???

Sub try121()
Dim wb As Workbook
BookDate = InputBox("Please enter the date of the file to open")
Myfile = MonthName(Month(Now)) & " " & BookDate & ", " & Year(Now) & ".xlsm"
Windows(Myfile).Activate
Sheets1.Select
Cells(1).Copy
ThisWorkbook.Activate
Cells(1).Select
ActiveSheet.Paste
End Sub

 

[meldoc]

How are you entering the date into the InputBox?

 

[pedie]

Just the date number
eg: like 23, 21, 1 etc

 

[pedie]

I think it is working now

BookDate = InputBox("Please enter the date of the file to open")
Myfile = MonthName(Month(Now)) & " " & BookDate & ", " & Year(Now) & ".xlsm"

from the code above if in case myfile is out of range and not found then i want a msgbox box file not open

 

[pedie]

Just need to know how to handle the error if the file is out of range (which mean not opened)now..thanks

 

[meldoc]

patience Pedie

Try this.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> try121b()<br><SPAN style="color:#00007F">Dim</SPAN> wb <SPAN style="color:#00007F">As</SPAN> Workbook<br>BookDate = InputBox("Please enter the date of the file to open")<br>Myfile = MonthName(Month(Now)) & " " & BookDate & ", " & Year(Now) & ".xlsm"<br><br><SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br><SPAN style="color:#00007F">Set</SPAN> wb = Myfile<br><SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br><SPAN style="color:#00007F">If</SPAN> wb <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> MsgBox "Error Occured, file not found": <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br>Windows(Myfile).Activate<br>Sheets1.Select<br>Cells(1).Copy<br>ThisWorkbook.Activate<br>Cells(1).Select<br>ActiveSheet.Paste<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[pedie]

Brian, thanks alot! sorry was running out of patience..this is perfect!
I think i'll cont, with my project again tomorrow

Thanks again!

Thanks Dan!!!

Bye guys!

 

[meldoc]

Goodnight Pedie,

 

[pedie]

Good night Brian!