Excel VBA to filter column, cut the content and paste it to the other sheets

11392

New Member
Joined
Oct 20, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
This is the Sheet1 and has been renamed it to s1

enter image description here

The VBA script below creates 2 new sheets, and name it as Task XYZ and Group JKL.

This script also filter the 2nd field (column B), and look for string specific string, let say x.

Finally it copies the first row on s1 and paste it to another new created sheets.

VBA Code:
Sub test()

' 1. Create 2 new sheets, name it as `Task XYZ` and `Group JKL`
Sheets.Add After:=ActiveSheet
ActiveSheet.Name = "Task XYZ"

Sheets.Add After:=ActiveSheet
ActiveSheet.Name = "Group JKL"

' 2. Filter 2nd field (column B), look for string "x"
Sheets("s1").Range("A1").AutoFilter Field:=2, Criteria1:="x"

' 3. Copy and paste s1 to other sheets
Sheets("s1").Rows(1).Copy
Sheets("Task XYZ").Paste
Sheets("Group JKL").Paste

End Sub

Current output for s1

enter image description here

Current output for both Task XYZ and Group JKL

enter image description here

But what I wanted to do now is not only copy and paste, but to cut the filtered content as well.

Desired output for s1

enter image description here

Desired output for Task XYZ

enter image description here
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Watch MrExcel Video

Forum statistics

Threads
1,119,241
Messages
5,576,910
Members
412,753
Latest member
Coach_Olson
Top