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

artesz

New Member
Joined
Jul 31, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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
 

Attachments

  • Sheet 1.png
    Sheet 1.png
    10.5 KB · Views: 3
  • Sheet 2.png
    Sheet 2.png
    5.8 KB · Views: 2

artesz

New Member
Joined
Jul 31, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

artesz

New Member
Joined
Jul 31, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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
MrExcel screenshot.jpg
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,043
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jul 31, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,871
Messages
5,544,787
Members
410,635
Latest member
phoenix7771
Top