Macro only works once as range value has changed

**kennected** · 03-27-2012, 01:59 PM

I'm trying to build a macro that allows me to add a column, copy data from a static area of my sheet and paste it into the new cells. My problem is 2 things: I don't know enough about Excel, and the fact that the range needed for the next run of this macro has changed since cells have been added and the coordinates have changed.

Please Login or Register  to view this content.

Any help is appreciated.

**Paul** · 03-27-2012, 02:03 PM

Hi Kennected,

Can you explain in words how you would like the data to move? E.g. "Every time I run the macro I want to copy the last used cell in column C to the next available cell in column I..."

**kennected** · 03-28-2012, 09:11 AM

Thanks Paul.

I would like the data to be moved in the form of a copy (from the static, template cells elsewhere on the sheet) and paste (to the new home created by the inserted cell. The macro as it is written, has trouble when it goes to paste the copied new cells into the place where the cell inserting took place. The "insert cell" shifts the columns and that gives the column headers new names, eg: column I becomes J. The next time this macro runs the paste range would need to be J6 because of the previous shift due to the cell insert taking place. The macro's instructions are still saying to paste the data copied into I6 (macro not knowing that the cell (column names) have shifted. This makes the macro only good for one run as it is written. The insert cell, copy from other area on sheet, paste into new cell, needs to be done daily.
Hope this mess makes sense, I appreciate the help.

**kennected** · 03-28-2012, 09:13 AM

Also, I hope Click here to read the Forum Rules is part of your signature and not an indication that I 've done something wrong. I've reviewed the rules and don't think I 've done a faux pas.

**Paul** · 03-29-2012, 04:00 PM

Hi Kennected,

Everything below the line in the post (starting with my name) is part of my signature and just serves as reminders. It appears in all of my posts, you did not break any of them that I can see.

As for the issue at hand, does the header (e.g. in row 1) of column I also get shifted to the right each time you run the macro? If so, the macro can search row 1 for the specific header and then go down to the current row and insert data. Or is there some other way the macro would know how to find the correct column?

**kennected** · 04-01-2012, 05:09 PM

Book1.xls

Oooo! Thanks Paul, you onto something here! So you're saying, if I put in a new row (which I've done in my attached file example) and put a keyword as a header for the column (like fruit type) that I want the macro to look for before inserting a column there, the column header name would no long be the location that the macro uses?

Can you give me an example of what the syntax would look like if I always wanted the macro to insert a new column to the left of the"Fruit Types" column?

**Paul** · 04-02-2012, 02:28 PM

I think it would look something like this in your case:

Please Login or Register  to view this content.

If "Fruit type" is found in row 1, a new column is inserted just before it, then the value in C6 is copied to row 6 of the found column (even though it has shifted to the right by 1).