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

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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
 
Upvote 0
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.
 
Upvote 0
As the OP hasn't shown expected results, I have gone with the comment to "Filter out", but we will have to wait & see.
 
Upvote 0
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: 5
Upvote 0
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
 
Upvote 0
In that case just remove the <> from this line .Range("A1").AutoFilter 5, "<>X"
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

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
Back
Top