Problem with macro that inserts a row, and copies formulas but not the values.

**CRIMEDOG** · 06-07-2010, 09:51 AM

I found this macro through the forum (thanks)
it inserts a row or rows, then copies the formulas from the row above - but is then supposed to delete all of the values (but not formulas)
for example: I enter the date (A1) my expected revenue (B1) then formulas calculated the next cells.
I want the macro to create row B with B1 & B2 empty but the formulas in B3...
Here is the macro.

Please Login or Register  to view this content.

It works great - but the values are copied. Did I miss something?

Thanks for the help.

**foxguy** · 06-07-2010, 02:03 PM

Please Login or Register  to view this content.

When I have this kind of problem I do this to find what went wrong.

Please Login or Register  to view this content.

Then look in the Immediate window to make sure I'm working on the right range.

**CRIMEDOG** · 06-07-2010, 04:49 PM

I added the line - but did not see any new info (Debug.Print Selection.Address)
Don't get an error - it just does not go back and delet the constants.

Maybe I'm not looking in the right place?

**foxguy** · 07-05-2010, 04:48 PM

Hi CRIMEDOG;

I just discovered this thread in my subscriptions. Sorry for not answering earlier.

Don't know if you're still looking for an answer.
The thread isn't marked "Solved", but I don't want to waste your time if you're satisfied.

**CRIMEDOG** · 07-13-2010, 12:15 PM

No I did not get it working - would love some help - would you like me to attach the file?
Mike

**royUK** · 07-13-2010, 01:06 PM

Can't see the point with Excel 2007 Tables

**foxguy** · 07-13-2010, 03:05 PM

Originally Posted by CRIMEDOG

would you like me to attach the file?
Mike

Yes, the macro works exactly like I expected it to. So there must be something in your file that's different.

**CRIMEDOG** · 07-14-2010, 09:48 AM

Here is the sample file - If you add rows between the filled in rows - it copies the values down (I just want it to copy the formulas)

Thanks for all the help!

**foxguy** · 07-14-2010, 03:16 PM

Hi CRIMEDOG;
Unfortunately, the macro is working fine. I inserted a row at Row 6 and also Row 28 and it worked fine.

When you insert a row, select every cell in the new row that you don't want there, and you should see an "=" at the front of the contents. IOW it is a formula and the macro doesn't clear it.

Specifically
1) when I was on Row 6 and inserted 1 row, these are the cells that it cleared: A7,J7,AJ7:AK7
2) when I was on Row 28 and inserted 1 row, these are the cells that it cleared: J29:L29,N29,S29:V29
If they were not cleared on your computer, I would guess that you changed something before copying it to upload to the forum.

Problem with macro that inserts a row, and copies formulas but not the values.

LinkBack

Thread Tools

Rate This Thread

Display

Problem with macro that inserts a row, and copies formulas but not the values.

Re: Problem with macro that inserts a row, and copies formulas but not the values.

Re: Problem with macro that inserts a row, and copies formulas but not the values.

Re: Problem with macro that inserts a row, and copies formulas but not the values.

Re: Problem with macro that inserts a row, and copies formulas but not the values.

Re: Problem with macro that inserts a row, and copies formulas but not the values.

Re: Problem with macro that inserts a row, and copies formulas but not the values.

Re: Problem with macro that inserts a row, and copies formulas but not the values.

Re: Problem with macro that inserts a row, and copies formulas but not the values.

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions