I am looking for a VB code to copy data from columns A and B to Cells C and D. I need the Data to fill top to bottom in C and D with no skipped rows or filling in extra data.
There may be skipped rows in A an B with no data and A and B are filled with a formula. Can this be done automatically ? Thanks
Last edited by rockyw; 02-07-2011 at 07:21 PM. Reason: Did not hold format
Hi rockyw,
I don't see a problem but would like to write the correct VBA code for your problem. Can you supply a short sample file. You can attach it by pressing the "Go Advanced" button below the message area and then the PaperClip that apperas above the new advanced message area. This will open a window for you to attach a sample file.
Also, why doesn't sorting and copy values only work?
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Thank you Marvin for wanting to help. I could use sort and copy but I will not always be entering the information. If someone else does it I would like it to be simple as could be.
I will attempt to attach a sample of what I'm doing.
I need the data from column A and B to move to C and D with no spaces. I have a chart that uses the data from C and D for it's information. That's why I can't have spaces. I have 3 charts, each getting information from a IF formula that goes into an area like I uploaded. I have one main sheet for entering data, then going into 3 differant charts. The data in A and B is from a formula, I have a code but it only worked if I entered data manually. If this can enter by it's self that would be great. I would then copy the code to the 3 sheets and use it for all 3 charts. Make sence? I hope so. Thanks agin for looking into this. Rocky
OK Rockyw,
Find the attached with the VBA code to do what you want that looks like this:
Please Login or Register to view this content.
Marvin Thank you, I got it to run manually and it worked by going into marcos, and run. Is that the way I have to do it? When I hit the Run VBS button I get an error. MaintChart is locked for editing. There is no way it can work as data is entered and updated by it's self? If not that's OK. Thank you.
Hi,
YES you can certainly run it manually. If this problem is solved, edit the original post and make the prefix as solved.
You didn't give me the chart so I didn't know to code around any locked chart. Run it manually or upload a sample with the problem if you want me to look at it.
I have uploaded a second file. The only thing the code needs to look at is A and B columns Level 1 Data . Then move that data to C and D without extra or skipped rows. I know this isnt simple so don't think I'm saying that. I really appriciate your helping me. I hope this file helps. What I would like it not to have to run a macro manually, can we trigger the macro as data is entered. Thanks
OK RockyW,
You can't have merged cells in C2 and D2. Let them be two separate words. See attached. This may have been part of the problem.
I moved the code to behind the worksheet. Now when anything changes on the second sheet in columns A or B it will fire the macro to rebuild columns C and D. If that doesn't work then double click the mouse in either column A or B and it will rebild C and D.
Let me know. The question with the Worksheet_Change event is I don't know if you physically change anything on this sheet or not. If you never need to double click to fire the macro that would be great. If you need to double click then I need to see more of the spreadsheet and chart etc.
Marvin I tried at and it works if I click in A or B. The problem I see is if I click again it repeats one row so now I have a double entry. If someone else does this and does not see that, that gives me a false entry on my chart. Maybe I'm going at this the wrong way, can I have my chart ignore 0 data? If there is no data in row 3 and 4 it will skip over it and not make a blank area? I have read about this but can't make it work. Is there a filter to do this? Let me know what you think. Thanks for all your work!! Have a good day. Rocky
Hi rockyw,
teylyn taught me this one...
Clikc on your chart area and then click on the graph. Right click and find "Select Data..." Click on it and get to the "Select Data Source" dialog box. On the bottom left of this window is a button called "Hidden and Empty Cells". Click this and find the dialog I think you are asking about.
See http://office.microsoft.com/en-us/ex...010048555.aspx
hope that helps.
I tried this Marvin but it still shows a gap. Thanks for the help. I will keep trying with your new tip. Thanks
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks