declaring statements

[ermccarthy]

I have declared the following two statements in a macro I have created, unfortunatly I get an error #438:Object doesn't support this object or method, and it is always the second line that is highlighted...


workbk1 = Range("FileName").Value
acct = Workbooks(workbk1).ActiveCell.Value


here is the macro, or at least the first part of it.

path1 = Range("path1").Value
path2 = Range("path2").Value
path3 = Range("path3").Value
path4 = Range("path4").Value
Sht1 = Range("Sheet1").Value
Sht2 = Range("Sheet2").Value
Sht3 = Range("Sheet3").Value
Sht4 = Range("Sheet4").Value
workbk1 = Range("FileName").Value
acct = Workbooks(workbk1).ActiveCell.Value

Worksheets("matrix").Select
Range("D4").Select
If ActiveCell.Value <> "" Then
Workbooks.Open FileName:=path1
Worksheets(Sht1).Select
Range("F11").Select
Do Until ActiveCell = acct.Value
ActiveCell.Offset(0, 1).Select
Loop
ActiveCell.Offset(100, 0).Select
Do Until ActiveCell <> ""
ActiveCell.Offset(-1, 0).Select
Loop
Selection.Copy
Workbooks(workbk1).Active
ActiveCell.Offset(0, 3).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveCell.Offset(1, -3).Select
Else: End If


Please help me-----there may be more errors in this, but I just can't get past this so far......

[Tom Schreiner]

Hi
Maybe try this:

Change:

acct = Workbooks(workbk1).ActiveCell.Value

To:

acct = Workbooks(workbk1).ActiveSheet.ActiveCell.Value

Tom

[ermccarthy]

Didn't work, still got the same error.

[dk]

Sorry TSTom but that won't work either. Activecell is a property of either the entire Application or a Window.

ermccarthy, if there is an active cell then this line will do the trick:-

acct = ActiveCell.Value

If there is an active cell then there's no need (and you can't) qualify it with a workbook name.

HTH,
Dan

[ermccarthy]

That seemed to work until I got a little further down in the macro, the problem is I need to look on the new workbook I open for the value of the activecell on book1. When I open the second workbook, that changes the value of acct to the activecell of the newly opened active cell.

How do I work around that?

[dk]

Can you please post the full code so I can have a look on my machine?

Cheers,
Dan

[ermccarthy]

the above is the full code so far, I will repeat the steps four times using the other decleartions (path2, sht2, etc.)