Confusing Problem - Help Please

torweb

Board Regular
Joined
Dec 1, 2003
Messages
136
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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Hi torweb:

If I have understood you correctly, please try ...
Code:
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?
 
Upvote 0
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!!!
 
Upvote 0
Hi torweb:

Please try ...
Code:
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?
 
Upvote 0
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!
 
Upvote 0
torweb said:
....
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.
 
Upvote 0
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
 
Upvote 0
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!!
 
Upvote 0
torweb said:
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!
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top