VBA for Copy cells from marked rows on sheet 1 to sheet 2 based on criteria and number them on sheet 2

[artesz]

Hi Everybody,

I am a beginner with VBA programming but trying to find a code for my problem (first post on the forum).
I found quite a lot of solutions, but I am not able to have them do what exactly I need (copy over rows is okay).

I have 2 sheets: Sheet 1 for the source data (mainly text type cells) and Sheet 2 to collect the marked items.
So the Macro should do the following:
Filter out all the rows in Sheet 1 that are marked with an "X" in column E. Then copy over the data from Column A & B only for each row to Sheet 2 Column A & B starting from row 2 (as row 1 is a header).

At the end, I would assign this macro to a push button, so when Sheet 1 is complete, user will push the button to transfer the marked items over to Sheet 2. If the transfer button is pushed again, then the macro should first delete all data in Sheet 2 (except the header) and then copy.

Thank you so much for help

artesz

 

[artesz]

1) (in the final document, I have have many additional rows) yes in both sheets
2) They are renamed as well as in the macro
3) yes, many
4) no
5) normal range

I tend to think that it is more complex than I have anticipated it...
I will try to post the actual excel file.

 

[artesz]

I tend to think that it is more complex than I have anticipated it...
I will try to post the actual excel file.

I made another explanation drawing with the real sheets, showing also what kind of formatting I have

 

[Fluff]

Unfortunately, with a data layout like that you are going to be in a world of pain.
Merged cells are an abomination & should be avoided like the plague.
Because of that I do not work with merged cells, so can offer no further help.

 

[artesz]

Unfortunately, with a data layout like that you are going to be in a world of pain.
Merged cells are an abomination & should be avoided like the plague.
Because of that I do not work with merged cells, so can offer no further help.

Thanks Fluff, I was afraid of that when I saw the comments. I do have a problem where I though of getting it done in Excel but excel is not meant for that.