copying a formula from one workbook to another. Lost the will to live

[madmiddle]

Evening peeps,


I have been trying to figure out what is wrong with my code for the past 4 weeks and have got no closer to fixing it. basically the workbook that i have created is a way of me keeping track of the time that I work but from time to time I tweak the master workbook and then use a macro to copy in the times from the old workbook. If I use .value then it copies it across with no trouble, but if I use the the .formula I have a run-time error '1004': Application-defined or object-defined error. any pointer or help greatly received as i' really struggling.

The code in question is:

For x = 4 To 6    
     For y = 4 To 402
        If Workbooks("old.xlsm").Sheets("Flexi Planner").Cells(y, x).Value <> "" Then 
               Workbooks(planner).Sheets("Flexi Planner").Cells(y, x).Formula = Workbooks("old.xlsm").Sheets("Flexi Planner").Cells(y, x).Formula
        End If
    Next y
Next x

Thank You in advance

Andy

 

[dchaney]

For x = 4 To 6    
     For y = 4 To 402
        If Workbooks("old.xlsm").Sheets("Flexi Planner").Cells(y, x).Value <> "" Then 
               Workbooks([COLOR=#ff0000][B]planner[/B][/COLOR]).Sheets("Flexi Planner").Cells(y, x)[COLOR=#ff0000].Formula[/COLOR] = Workbooks("old.xlsm").Sheets("Flexi Planner").Cells(y, x).Formula
        End If
    Next y
Next x

Where are you getting the name of the planner workbook from? I can see in your code where there are no "" around it this may be causing you issues?

Also, I do not think you need the first .formula

 

[madmiddle]

Where are you getting the name of the planner workbook from? I can see in your code where there are no "" around it this may be causing you issues?

Also, I do not think you need the first .formula

Thanks for the reply,

I've bloody cracked it......

sorry planner is from:

planner = ActiveWorkbook.Name

with asking about the first formula I put in the code:

Debug.Print Workbooks(planner).Sheets("Flexi Planner").Cells(Y, x).Formula
Debug.Print Workbooks("old.xlsm").Sheets("Flexi Planner").Cells(Y, x).Formula

The first one printed a result but the second produced the same error so i'd narrowed it down to the second workbook (old.xslm). I spent the following couple of hours looking at the differences and noticed that i have locked the cells (so that it only shows the result from the formula.

now the code is:

Workbooks("old.xlsm").Sheets("Flexi Planner").Unprotect
For x = 4 To 6
    For Y = 4 To 402
        If Workbooks("old.xlsm").Sheets("Flexi Planner").Cells(Y, x).Value <> "" Then
            'Debug.Print Workbooks(planner).Sheets("Flexi Planner").Cells(Y, x).Formula
            'Debug.Print Workbooks("old.xlsm").Sheets("Flexi Planner").Cells(Y, x).Formula
            Workbooks(planner).Sheets("Flexi Planner").Cells(Y, x).Formula = Workbooks("old.xlsm").Sheets("Flexi Planner").Cells(Y, x).Formula
        End If
    Next Y
Next x
Workbooks("old.xlsm").Sheets("Flexi Planner").Protect

I'm slightly annoyed it was that simple..

Andy

 

[dchaney]

Well, I am glad you figured it out