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: 6
  • Sheet 2.png
    Sheet 2.png
    5.8 KB · Views: 5

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
72,989
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,259
Office Version
  1. 365
Platform
  1. Windows
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
72,989
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: 2

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
72,989
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
72,989
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,500
Messages
5,832,072
Members
430,110
Latest member
Chyke_mxl

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top