Confusing Problem - Help Please

[torweb]

I've pasted this code to copy cells from one work sheet to another, then remove the pasted rows if they're blank. It works very strange...that is, sometimes it doesn't and returns this error.

Run-Time Error 1004 Cannot Use That Command on Overlapping Sections

The strange part is, sometimes it works and sometimes it doesn't. The complete code is (with the bold section where the error occurs).

With Sheets("Daily Meals Report")
.Range("B3:G259").Value = Sheets("Joural CalcSheet").Range("A2:F259").Value
With .Range("B3:G259")
.Replace What:="0", Replacement:="", LookAt:=xlWhole
.SpecialCells(xlBlanks).EntireRow.Delete
End With
End With

This ones got me very confused...any help out there.

Thanks in advance

 

[Yogi Anand]

Hi torweb:

If I have understood you correctly, please try ...

With Sheets("Daily Meals Report")
    .Range("B3:G7").Value = Sheets("Joural CalcSheet").Range("A3:F7").Value
    .Range("B3:G7").SpecialCells(xlBlanks).EntireRow.Delete
End With

Does it work for you now?

 

[torweb]

Yogi Anand,

Thanks for the reply, but still some strange issue. Maybe I can explain my probelm better.

I have a worksheet with 5 columns. Col A contains food items, Col B the Amount Consumed, then C thru F containing nutritional information. I want to only copy rows of data if Col B contains an amount larger than zero...an amount actually eaten. If not, I want to skip that row and move to the next row. The end goal, only copying rows of data if the person actualy ate something...a Daily Total of items eaten along with their nutritional information.

I replace my "With/End" statement with yours but still get strange problems and the same error codes.

Thank you for your help!!!

 

[Yogi Anand]

Hi torweb:

Please try ...

With Sheets("Daily Meals Report")
    .Range("B3:G7").Value = Sheets("Joural CalcSheet").Range("A3:F7").Value
    On Error Resume Next
    .Range("B3:G7").SpecialCells(xlBlanks).EntireRow.Delete
    For Each cell In .Range("C3:C7")
        Debug.Print Cells(cell.Row, "C")
        If Not IsNumeric(Cells(cell.Row, "C")) Or Cells(cell.Row, "C") <= 0 Then cell.EntireRow.Delete
    Next cell
End With

Does it work for you now?

 

[torweb]

Yogi Anand,

Much closer...no Debug Errors, however I'm still copying data from the first col in worksheet Joural CalcSheet and copying into worksheet Daily Meals Report. One issue is my qualifying cols, the one we're checking for zeros, contain a formulat. Could the formula be the rood of the current problem?

It seems to be working on everything to the right of the qualifying col but not to the left which is the col that contains the actual food item.

Thank you very much for your help!

 

[Yogi Anand]

....
Much closer...no Debug Errors, however I'm still copying data from the first col in worksheet Joural CalcSheet and copying into worksheet Daily Meals Report. One issue is my qualifying cols, the one we're checking for zeros, contain a formulat. Could the formula be the rood of the current problem?

It seems to be working on everything to the right of the qualifying col but not to the left which is the col that contains the actual food item.
....

Hi torweb:

Please post some sample data, the formula you refered to, the result you got on applying the code, and what you think is the right result with an explanation as to why you think that is the correct result ...

and then let us take it from there.

 

[torweb]

Yogi Anand,

Thanks again for the reply....I'll post your request in the morning...Just got home from a lecture...Early A.M. West Cost Time.


Regards,,

Torweb

 

[torweb]

Yogi Anand,

I just chopped up my code and have determined that if the column contains a formula, the code you suggested treats it as a value >0. If I delete the formulas within that column and substitute either a zero or number greater than zero, it works great and only copies those rows with a number greater than zero in that cell.

To summerize:

I'm trying to copy and paste, only rows that have a qualifying cell which is an Amount Value derrived from a formula in each cell (=SUM('Daily Journal'!M10:P10)*'Daily Journal'!E10) which is either a zero or amount of servings greater than zero.

I would need the code to see the cells inquestion as a numeric value. Hopefully this will clear things up a bit...and thanks for you help!!

 

[Yogi Anand]

Yogi Anand,

I just chopped up my code and have determined that if the column contains a formula, the code you suggested treats it as a value >0. If I delete the formulas within that column and substitute either a zero or number greater than zero, it works great and only copies those rows with a number greater than zero in that cell.
....

Hi torweb:


I am working with the information you had provided earlier that there is data in columns A through E of 'Joural CalcSheet', with Food Values in column B. And you wanted to copy only those rows to 'Daily Meals Sheet' columns B through G where the FoodValue was >0

I just checked that the code I provided does work whether the FoodValue entry in 'Joural CalcSheet' is a number or a formula.

So, I don't know what else you have in your sheet -- you may want to do some debugging -- Good Luck!