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

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,043
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
VBA Code:
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

Well-known Member
Joined
Jun 29, 2014
Messages
1,002
Hello Fluff,

Rich (BB code):
"....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:-

Rich (BB code):
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

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

New Member
Joined
Jul 31, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
 

Attachments

  • Sheet 2.png
    Sheet 2.png
    8.6 KB · Views: 1

artesz

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

Explanation.png
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,043
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

artesz

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

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

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

Watch MrExcel Video

Forum statistics

Threads
1,113,872
Messages
5,544,796
Members
410,635
Latest member
phoenix7771
Top