Find and copy macro

[Malbogio]

I'm trying to come up with a macro which will go through column A once searching for a value (ex. "apple") and upon finding a cell with that value, take cells 79-100 from that row, copy them to cells 82-103 of the next row, and delete the row which contains the value.

I believe the functions I need to use are:

Please Login or Register  to view this content.

I would greatly appreciate any guidance or examples.

[royUK]

You don't need to select the cell, so you wouldn't use ActiveCell. use the VBA .Find method to find the string. Try this

Please Login or Register  to view this content.

[Malbogio]

You don't need to select the cell, so you wouldn't use ActiveCell. use the VBA .Find method to find the string. Try this

Please Login or Register  to view this content.

It really does, thank you. Unfortunately, being as much of a newbie at macros & VBA as I am, I can't figure out how to finish this up by having it react to multiple instances of the value ("apple" exists in 5 different rows on my current worksheet), nor how to add in a command to delete each of the rows in which apple is found after that data is copied off of them.

[Malbogio]

Sorry, I'm still a bit stuck (I only started trying to work with VBA yesterday). Could someone please show me how to get that macro to repeat itself, and possibly to delete the row containing the value 'apple' after having copied the selected cells from it?

[Reafidy]

Try:

Please Login or Register  to view this content.

[Reafidy]

if you want to copy cells (not just values) change this line:

Please Login or Register  to view this content.

to this line

Please Login or Register  to view this content.

[Malbogio]

Try:

Please Login or Register  to view this content.

This code works perfectly until it hits multiple lines holding the value.

Ex:
A B
Apples 12
Bananas 34

Does generate Bananas 1234. However:

A B
Apples 12
Apples 34
Bananas 56

Generates Bananas 1234 as well, rather than Bananas 123456.

[Reafidy]

Youve lost me with your little example, perhaps an example workbook with original data and end result would help.

[Malbogio]

Youve lost me with your little example, perhaps an example workbook with original data and end result would help.

I want to take all cells in a specific area on each flagged row, then move them down to the next row and 3 steps over. I'd like to go through them in descending order like so:

Rows 3, 4, 5, and 6 are all flagged.
Information from select cells in row 3 is grabbed and moved to row 4.
Row 3 is deleted.
Information from select cells in row 4 (now also including the cells from row 3) is moved down to row 5.
Row 4 is deleted.
Information from select cells in row 5 (now also including the cells moved down from rows 3 & 4) is moved down to row 6.
Row 5 is deleted.
Information from select cells in row 6 (now also including the cells moved down from rows 3, 4, & 5) is moved down to row 7.
Row 6 is deleted.
Row 7 contains the accumulated select cells from 3, 4, 5, 6, & 7 in the set area.

I'm trying hard to be clear on this, but I doubt I'm doing a sufficient job. I'm quite sorry for any confusion I might be causing as I attempt to express the task I'm having difficulty with.

[shg]

Zip your spreadhseet, and then attach the zip file.

[Malbogio]

Zip your spreadhseet, and then attach the zip file.

Thanks! Here's a before and after of the original files I'm working on, stripped of unrelated or personal information

EDIT: I'd mention that the actual order of the moved files is not important to me.

[Reafidy]

Well the example doesnt seem to be anything like your question so I have got you example to work

Try this, it looks for the word kill in your eaxmple and moves the range as requested in your example:

Please Login or Register  to view this content.

[Malbogio]

Well the example doesnt seem to be anything like your question so I have got you example to work

Try this, it looks for the word kill in your eaxmple and moves the range as requested in your example:

Please Login or Register  to view this content.

Yes, my apologies for failing to mention. I was using different values in my actual files ('kill' to delete the line, 'save' to keep it) and there were another ~70 lines of unrelated and private data in the middle which I excluded from the samples.

However the code you provided works perfectly. Now that it's plugged into a macro it completely automates a long and tedious set of operations I had to perform daily.

Thank you very much for the help and the effective code!

[Reafidy]

Your Welcome!