Trying to copy data from one cell to another using Macro or VBA.

[Springbok1986]

Hi All,
I'm trying to automate an Excel report. So far, I've been able to do what I need to, but this last piece has me stumped. In an Excel spreadsheet I have a number of macros and some VBA code, but I am stuck. I have inserted blank rows where the data in column E changed from one value to another. The title of my posting is over simplistic, because the data in the table is dynamic and changes from week to week. Here is a section of the table I'm working on. What I would like to do is copy the name from col E3 to A2, E8 to a7, E10 to A9 etc. Recording a simple macro worked until the data changed, for instance Drew was no longer in E8 but E12 because Andy had increased in number of records.

I am sure that your Gurus will be able to resolve this for me, and save some of my ever diminishing hair

Thanks

Barrie

Please Login or Register  to view this content.

[smuzoen]

Welcome to Excel Forum - to get help with this please upload a sample workbook showing how your data is set out and how your data should appear once the code has run. You will find people reluctant to help as they may need to recreate your workbook (which takes time) and the end result may be not what you require. Please upload a sample workbook with no sensitive data showing how your data is set out and how you finally want it to appear being very specific with your requirements. Make sure you show how you want the data to finally appear. To upload a sample workbook select Go Advanced, Manage Attachments and upload a sample file making sure it contains no sensitive data.

[Springbok1986]

Thank you for pointing me in the right direction. I have included the excel file with most of the actual data and in worksheet "What I have" (Red Tab)is the current status of my report which has the data sorted by producer and the blank row inserted at the point where the value in column Ichanges form one value to another, namely Andy to Drew, etc. The Worksheet "What I want" (Green Tab) indicates how I would like it to look when done.

I will really appreciate the help.

[smuzoen]

In your sample workbook you say the name will be in J however in the workbook the namer is in Column I so you will need to adjust the code if the name is in J. You could use the following which will create a new sheet called Results

Please Login or Register  to view this content.

See attached workbook.

[Springbok1986]

Thanks Anthony, It does just what I want. Does it need to create a new worksheet as I would really like to have it run in the "What I have" worksheet?

I'm sorry it took me so long to respond, I was on vacation for a week, but am now back in the saddle, so to speak.

Barrie

---------- Post added at 02:00 PM ---------- Previous post was at 01:54 PM ----------

By the way Anthony, You were spot on with thecolumn ID, it should read column I, as you rightly assumed.

Thank you.

Barrie

[smuzoen]

Hi Barrie
Just change the code a little

Please Login or Register  to view this content.

I assumed you wanted a new sheet however the above code will just place the names in the What I Have worksheet.

[Springbok1986]

Perfect! Thank you Anthony, I really appreciate the help. You've been patient and the outcome couldn't be better. I will mark it as solved and update the "reputation" star.

Thanks again

Barrie