Copy data range but ignore (not copy) cells with formula

**mattman1968** · 03-23-2019, 10:28 AM

I have a budgeting worksheet that contains multiple sheets and formulas. A user can enter his data into the ledger, and various financial scenarios are automatically calculated. The ledger contains some cells that contain formulas, and may be over-written by the user. That is fine since the worksheet is projecting month per month, and must be updated with actual entries to keep the projections accurate.

Occasionally I update/upgrade the budgeting worksheet, and want the user to be able to export his data from the old worksheet and import it to the new worksheet. I have created a macro that exports the data for certain ranges. I have also created a macro that imports the data range to the new worksheet.

However, my problem is that the macro to export also converts predetermined calculations from their original formula (=A1+B2, for example) to whatever the result is for that formula ($1,200, for example). This causes problems for future projections in other data ranges since the formula is now replaced with a static number that cannot change based upon other monthly deposits/withdrawals.

I have tried exporting the data minus any cells that contain formulas but have been unsuccessful. I have attached my working export code (as I have many sheets and ranges, I have only posted the minimum to show what I have working). I have also attached the code I used for ignoring cells with formulas (inspired by this post Excel VBA Copy / Paste macro: Ignore cells with Formulas). Any help is greatly appreciated. As is surely obvious, I am new to VBA and know next to nothing about it!

WORKING EXPORT CODE:

Please Login or Register  to view this content.

NON-WORKING: IGNORE CELLS WITH FORMULA

Please Login or Register  to view this content.

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #2 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer

**KOKOSEK** · 03-23-2019, 10:37 AM

Please Login or Register  to view this content.

**mattman1968** · 03-23-2019, 10:40 AM

6StringJazzer - Sorry, and thank you!

**mattman1968** · 03-23-2019, 10:43 AM

Kokosek - Appreciate the quick response. Do I just add that to my working code?

**6StringJazzer** · 03-23-2019, 10:44 AM

The problem seems to be that the Source and Range are the same. You are copying values right back to where they came from. Here is your code with commentary. I am assuming this code is in a standard module (like Module1) and not a worksheet module (like Sheet1).

Please Login or Register  to view this content.

**Fluff13** · 03-23-2019, 10:48 AM

How about

Please Login or Register  to view this content.

**mattman1968** · 03-23-2019, 03:05 PM

Thank you everyone for your suggestions. I will try them when I get more time and report back in a couple days.

**mattman1968** · 03-25-2019, 11:29 AM

I have tried several suggestions from this site, as well others, but since I am not really sure how to write code in the first place, I am getting nowhere. For the record, the code I posted as "working" I tweaked from an online source and it does exactly what I want...except it copies cells with formulas. The "working" code creates a new workbook with the same tabs, and exports the data to the same respective cells. Now if only I can edit my existing "working" code to eliminate the copying of cells with formulas? For reference here is the full "working" code minus all the sheets/data ranges.

Thanks!

Please Login or Register  to view this content.

**6StringJazzer** · 03-25-2019, 11:56 AM

I think we need to start over again with understanding what you want, because you've gotten two or three solutions here. I suppose part of the problem is that you're new to VBA.

What do you want to do for the cells that contain formulas? You said "ignore" but do you just want to leave those blank in your destination?

If you want to leave them blank, we can adapt one of the solutions given and package it up with the code you provided.

**mattman1968** · 03-25-2019, 12:17 PM

Originally Posted by 6StringJazzer

What do you want to do for the cells that contain formulas? You said "ignore" but do you just want to leave those blank in your destination?

Yes. I want to leave cells with formulas blank in the destination. I apologize if I worded incorrectly before.

**Fluff13** · 03-25-2019, 12:31 PM

Does this work for the first range in the HELOC sheet?

Please Login or Register  to view this content.

**6StringJazzer** · 03-25-2019, 12:32 PM

This is a complete replacement for the code you've provided, so hopefully it will be easy for you to put it in your file. I took the code from your first post that works, modified it based on your approach to use a Sub to do the copy, and used KOKOSEK's method for detecting a formula (although your original method was also valid). Your problem was in how you were calling copy_non_formulas, as I described in my first post.

I compiled this but cannot run it without having your file and it would be too time-consuming for me to create one based on guessing what it looks like. If this still does not work then I suggest attaching your file with any sensitive data removed or faked.

Please Login or Register  to view this content.

**mattman1968** · 03-25-2019, 01:32 PM

I appreciate the code 6stringjazzer. Unfortunately, it results in an error: Object Required. No other info is provided. I tried tweaking it a bit but got nothing. If this is an easy fix for someone, I appreciate the time and effort. I'm ready to give up though. I will try to provide a dumbed-down version of my workbook but that may not happen for a while either...real work to do

Thanks again, all!

**6StringJazzer** · 03-25-2019, 02:10 PM

When you get the error, click Debug. What line of code is highlighted when the error occurs? That error is usually easy to fix, but we need to know what object is causing it.

**mattman1968** · 03-25-2019, 03:06 PM

When the error pops up, I am not allowed to click anything else. I have to close out the dialog box, then when I select debug, nothing happens with any of the options unless I choose Debug>Run to cursor. Below are the only lines that become highlighted...but only when I place the cursor right after said line and select Debug>Run to cursor. Every other line gives a Run time error 424. Sorry if this is not helpful.

Please Login or Register  to view this content.

**6StringJazzer** · 03-25-2019, 03:29 PM

What is ActiveSheet at the time this code is executed?

I am confused by your description of what happens when an error occurs. This is what you should expect to see:
errordialog.JPG

and if you press debug you should see a line of code highlighted like this:
errorhighlight.JPG

**mattman1968** · 03-25-2019, 03:37 PM

I have seen the errors highlighted like you describe...but not in this instance. The error dialog box comes up and I am not allowed to press debug unless I close the error box.VBA.png

**6StringJazzer** · 03-25-2019, 04:03 PM

I had an error, remove the .Value as shown in red.

I also added something that should help it run faster, see additional red code below.

Please Login or Register  to view this content.

**mattman1968** · 03-25-2019, 04:36 PM

Originally Posted by 6StringJazzer

I had an error, remove the .Value as shown in red.

I also added something that should help it run faster, see additional red code below.

THANK YOU SO MUCH!!! It appears to be working as desired. I will have to test on a larger scale but I think it's good to go. I truly appreciate this!

**6StringJazzer** · 03-25-2019, 06:00 PM

Phew!

Glad it helped!

If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

If a member helped you solve your problem, consider adding to their reputation by clicking on the star icon addreputationiconsmall.jpg below their name.

Copy data range but ignore (not copy) cells with formula

LinkBack

Thread Tools

Rate This Thread

Display

Copy data range but ignore (not copy) cells with formula

Re: Copy data range but ignore (not copy) cells with formula

Re: Copy data range but ignore (not copy) cells with formula

Re: Copy data range but ignore (not copy) cells with formula

Re: Copy data range but ignore (not copy) cells with formula

Re: Copy data range but ignore (not copy) cells with formula

Re: Copy data range but ignore (not copy) cells with formula

Re: Copy data range but ignore (not copy) cells with formula

Re: Copy data range but ignore (not copy) cells with formula

Re: Copy data range but ignore (not copy) cells with formula

Re: Copy data range but ignore (not copy) cells with formula

Re: Copy data range but ignore (not copy) cells with formula

Re: Copy data range but ignore (not copy) cells with formula

Re: Copy data range but ignore (not copy) cells with formula

Re: Copy data range but ignore (not copy) cells with formula

Re: Copy data range but ignore (not copy) cells with formula

Re: Copy data range but ignore (not copy) cells with formula

Re: Copy data range but ignore (not copy) cells with formula

Re: Copy data range but ignore (not copy) cells with formula

Re: Copy data range but ignore (not copy) cells with formula

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] How to copy rows that are complete with data and ignore blank/error cells

Copy Range and Paste only Values (ignore blank cells)

[SOLVED] Formula to ignore blank cells and copy data that meets criteria?

[SOLVED] Copy data from a range of cells into a blank range based on common cell

[SOLVED] VBA Copy a range of cells values using the range of cells in a formula (which will change)

VBA Copy a range of cells values using the range of cells in a formula (which will change)

Copy range of cell data to range of cells arranged by groups

Bookmarks

Bookmarks

Posting Permissions