Need to edit the value of one cell in column J after data is pasted into another sheet.

Hi guys

I have the following code which copies a row and pastes it into another sheet based on the value in column J.

Once the code code has copied the information over to the new sheet I want it to revert the value in column J (Status column) back to "in progress". How can I do this?

Here is the code I have so far which copies the row of information between sheets.

Please Login or Register  to view this content.

I can't see the workbook you're working with, but why not just add

Please Login or Register  to view this content.

Obviously, replace the sheet name and range appropriately for what you want.

This won't work unfortunately.

I need the code to change the value of the cell in column J that has been COPIED over as per the code above that I have written.

I don't want to change the value of every value in column J.

If you provide a sample workbook, I could try doing that.

Hi,

I have inserted an old version of my sample file which contains the same code.

Another issue that would be great to solve is when it moves the record across, it seems to unhide randomly between 20-30 rows of data below it.

When it copies the information across I don't want any rows to UNHIDE from the source sheet.

I would really appreciate your help on both of these issues :D

I'll have to look at this tomorrow. I am having trouble figuring out which piece of code I should be working with and what the intended result is. Until I know that I can only give this general advice: You have to somehow identify which rows to copy over. In that same step, you should be able to change the status of that row to "Completed."

Hi,

This is great however it is missing one function which my previous code used to do.

Basically ina ll of the sheets,every row is hidden EXCEPT for rows that have data in them.

When this code copies the data into the new sheet, after it pastes the data in, it needs to unhide the row that it has pasted the data into.

Also in regards to that code it changes every the status to "In Progress" for which ever sheet I am moving the row over to.

Is there a way to add multiple versions. so if the row is being taken to Delte sheet. Status in the row of data will be "Deleted". If moved to the workstack it would be "On Pause", once the data has been pasted in.

thank you for your help/

In regards to the string could you give me an example of how this works? For instance if it was to go to WIP it would be "Inprogress" if it's to go tot he workstack sheet it would be "on pause".

Thanks!

Hi thanks for your help,

I just used my original code and added the line

Target.Value "In Progress" and then changed that for each individual "If". this changed the value of the cell in the G column before it is pasted over.

Please Login or Register  to view this content.

I've tried copying the code above into my sheet and it doesn't work. The pop up dialog to ask if I want to move the record doesn't even appear.

Did you copy it into the "This Workbook" VBA sheet? My code is for the workbook, not a specific sheet. Doing it that way means that if you want to change the code, you only have to change it in one place.

Fabulous!!

There is one tiny other thing in regards to this code I hope you can solve.

I don't know if you see but after it copies the row of data over and deletes the original row, it seems to be randomnly revealing one of the hidden rows.

E.G if you move a record from wip to inbound... the record that was in wip will be copied over and deleted, after this ROW 100 of the WIP sheet will suddenly become visible.

Is there a piece of code I can add to this, to ensure this doesn't happen?

Either that or a piece of code that will hide any rows with no values in every cell in that row.

Thank you again for your help, this will be the last request within this code!!

Also if you click "NO" it still performs the function

I have fixed the "No" error with a "If yes" response.

Everytime the function is performed, it takes me to the spreadsheet it pastes the data to. Is there a way to get the code to return me back to the original sheet.

I would know how to do this when the code is in worksheet module.

The problem is being caused because you're deleting the row instead of clearing it. I thought of hiding every row without data but I don't think you want that, because then people couldn't add tasks. Also, it's a little resource intensive. Instead I'm just hiding the rows below that might get unhidden.

If you didn't mind blank space, you could clear the row instead of deleting it and that would fix your problem. Maybe if you looped through the rows in the UsedRange that were below the target row and moved the values up in each. Either way, the solution I have isn't elegant, but it works for me.

Please Login or Register  to view this content.