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

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub artesz()
   Sheets("Sheet2").UsedRange.Offset(1).Clear
   With Sheets("Sheet1")
      .Range("A1").AutoFilter 5, "<>X"
      .AutoFilter.Range.Offset(1).Columns("A:B").Copy Sheets("Sheet2").Range("A2")
      .AutoFilterMode = False
   End With
End Sub

 

[vcoolio]

Hello Fluff,

"....user will push the button to transfer the marked items over to Sheet 2"

I'm pretty sure that the OP wants the data marked with "X" in Column E to be transferred over, not "<>X" although one could consider this contradictory to the OP's other comment:-

Filter out all the rows in Sheet 1 that are marked with an "X" in column E.

I suppose its a matter of how one interprets the opening post.

Cheerio,
vcoolio.

 

[Fluff]

As the OP hasn't shown expected results, I have gone with the comment to "Filter out", but we will have to wait & see.

 

[artesz]

Thank you Guys for prompt help. I believe I was not clear enough
See updated screenshot on the expected result.
So the macro should copy over to Sheet 2 from Sheet 1 the text of column A & B if the row is marked with an "X" in column E.
The results in sheet 2 should start in row 2 and come one after the other.

 

[artesz]

Thank you Guys for prompt help. I believe I was not clear enough
See updated screenshot on the expected result.
So the macro should copy over to Sheet 2 from Sheet 1 the text of column A & B if the row is marked with an "X" in column E.
The results in sheet 2 should start in row 2 and come one after the other.

Maybe this helps to explain:

 

[Fluff]

In that case just remove the <> from this line .Range("A1").AutoFilter 5, "<>X"

 

[artesz]

In that case just remove the <> from this line .Range("A1").AutoFilter 5, "<>X"

Dear Fluff,

Can you please help? I changed as above, but getting the following error:
.Range("A1").AutoFilter 5, "X" --> "Autofilter method of Range class failed

 

[Fluff]

A few questions
1) Do you have a header row in row 1 with data starting in A1?
2) Are your sheets called Sheet1 & Sheet2?
3) Do you have any merged cells?
4) is the sheet protected?
5) Is your data in a normal range (as shown in your images), or is it a structured table?

 

[artesz]

A few questions
1) Do you have a header row in row 1 with data starting in A1?
2) Are your sheets called Sheet1 & Sheet2?
3) Do you have any merged cells?
4) is the sheet protected?
5) Is your data in a normal range (as shown in your images), or is it a structured table?

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