Error on Code, Please Help

[Excelnoobisme]

Hi i have a worksheet name "Working on amount Mar 2011" i need to paste some data depending on the date to another worksheet. The below is my vba code which work.

Sub Testing()
ActiveSheet.Name = "Today"

Workbooks.Open Filename:= _
"C:\Program File\Shared Files\Export Amount Mar 2011.xlsx"

Dim ff As Range
Set Found = Workbooks("Export Amount Mar 2011.xlsx").Sheets("Loc1").Columns("A").Find(what:=Workbooks("Working on Amount Mar 2011.xlsm").Sheets("Today").Range("A3").Value, LookIn:=xlValues, lookat:=xlWhole)

If Found Is Nothing Then
MsgBox "Not found"
Else
Workbooks("Working on Amount Mar 2011.xlsm").Sheets("Today").Range("T12:T15").Copy
Workbooks("Export Amount Mar 2011.xlsx").Sheets("Loc1").Range(Found.Address).Offset(0, 1).PasteSpecial Paste:=xlPasteValues

End If
ActiveWorkbook.Save
ActiveWindow.Close

ActiveSheet.Name = Range("TodayDate").Value

End Sub




Now i need to change the worksheet name to be more flexible so as i dont need to change the code everymonth.The below code always give me a runtime error. Any1 can advise what is wrong?



Sub Testing()
ActiveSheet.Name = "Today"
ActiveWorkbook.Windows(1).Caption = "1"

Set objExcel = CreateObject("Excel.Application")
Workbooks.Open Filename:= _
"C:\Program File\Shared Files\Export Amount Mar 2011.xlsx"
objExcel.Visible = True
Set objExcel = Nothing
ActiveWorkbook.Activate
ActiveWorkbook.Windows(1).Caption = "2"

Dim ff As Range
Set Found = Workbooks("2.xlsx").Sheets("Loc1").Columns("A").Find(what:=Workbooks("1.xlsm").Sheets("Today").Range("A3").Value, LookIn:=xlValues, lookat:=xlWhole)

If Found Is Nothing Then
MsgBox "Not found"
Else
Workbooks("1.xlsm").Sheets("Today").Range("T12:T15").Copy
Workbooks("2.xlsx").Sheets("Loc1").Range(Found.Address).Offset(0, 1).PasteSpecial Paste:=xlPasteValues

End If
ActiveWorkbook.Save
ActiveWindow.Close

ActiveSheet.Name = Range("TodayDate").Value

End Sub

 

[tweedle]

I don't think you can refer to workbooks based on labels (someone may prove me wrong)

Since, I assume, the xlsm is open before the xlsx, then they can be referred to by their workbook index as:

Set Found = Workbooks(2).Sheets("Loc1").Columns("A").Find(what:=Workbooks(1).Sheets("Today").Range("A3").Value, LookIn:=xlValues, lookat:=xlWhole)

 

[Excelnoobisme]

Set Found = Workbooks(2).Sheets("Loc1").Columns("A").Find(what:=Workbooks(1).Sheets("Today").Range("A3").Value, LookIn:=xlValues, lookat:=xlWhole)

Still getting run-time error "9"

 

[tweedle]

Yea, after I posted, I thought about it and hard coding indexes is not a good idea. other workbooks, including personal.xl* could cause undesireable results.

 

[Excelnoobisme]

can some 1 help or suggest better way of referencing the workbook name?

 

[tweedle]

Try this as a model.

Dim wb1, wb2 As Workbook
Dim ws1, ws2 As Worksheets
 
Set wb1 = ActiveWorkbook
 
FileToOpen = Application.GetOpenFilename("Excel Files (*.xl*), *.xl*")
 
If FileToOpen <> False Then
    Set wb2 = Workbooks.Open(FileToOpen)
Else
    MsgBox "No File Selected", vbOKOnly, "Aborting"
    Exit Sub
End If
 
'<snip>
Set Found = wb2.Sheets("Loc1").Columns("A").Find(what:=wb1.Sheets("Today").Range("A3").Value, LookIn:=xlValues, lookat:=xlWhole)

you're going to have reference issues when you get down to

[COLOR=green]xxx[/COLOR].Sheets("Today").Range("T12:T15").Copy

as well.

 

[Excelnoobisme]

having problem with wb name...any1 can help?

 

[tweedle]

what's the problem exactly?
The more specific it is defined, the fewer cycles it takes to resolve.

 

[Excelnoobisme]

Thanks Tweedle, i finally make it work by ur referencing wb1,wb2. Thanks alot