VBA เพื่อคัดลอกข้อมูลไปยังชีตอื่นด้วยตัวกรองขั้นสูงใน Excel

เมื่อเราจัดการกับแผ่นงาน Excel หลายแผ่น บางครั้งเราต้องคัดลอกข้อมูลจากสเปรดชีตหนึ่งไปยังอีกแผ่นหนึ่งโดยมีเงื่อนไขบางประการเพื่อให้อ่านง่ายขึ้น การใช้งาน VBA เป็นวิธีที่มีประสิทธิภาพ รวดเร็วที่สุด และปลอดภัยที่สุดในการรันการดำเนินการใดๆ ใน Excel ในบทความนี้ เราจะแสดงวิธีคัดลอกข้อมูลไปยังแผ่นงานอื่นด้วยตัวกรองขั้นสูงใน Excel ด้วยมาโคร VBA

ดาวน์โหลดสมุดงาน

คุณสามารถดาวน์โหลดเวิร์กบุ๊ก Excel แบบฝึกหัดฟรีได้จากที่นี่

3 วิธีด้วย VBA เพื่อคัดลอกข้อมูลไปยังชีตอื่นด้วย Advanced Filer ใน Excel

ดูชุดข้อมูลต่อไปนี้ มีแผ่นงาน Excel ชื่อ ต้นฉบับ . ชีตนี้ประกอบด้วยข้อมูลบางส่วนจากช่วง B4 ถึง E12 . ช่วงนั้นยังประกอบด้วยค่าที่ซ้ำกัน ช่วง G4 ถึง H5 ถือเกณฑ์ . สิ่งที่เราอยากทำคือเราจะคัดลอกข้อมูลจากช่วง B5:E12 เมื่อ จอห์น จาก ชื่อ คอลัมน์ มี เครื่องหมาย น้อยกว่า 80 (เกณฑ์ใน เซลล์ G4:H5 ) และวางข้อมูลเฉพาะเหล่านั้นลงในชีตอื่นด้วย ตัวกรองขั้นสูง ใน Excel ด้วยสามวิธีที่แตกต่างกัน

เราจะดูวิธีการแทรกมาโครฮาร์ดโค้ดเพื่อคัดลอกข้อมูล วิธีกรองล่วงหน้าตามการเลือก และวิธีถ่ายโอนข้อมูลจากแผ่นงานหนึ่งไปยังอีกแผ่นหนึ่งโดยการบันทึกมาโคร . และชุดข้อมูลข้างต้นจะเป็นตัวอย่างการดำเนินการตามวิธีการทั้งหมดที่กล่าวถึง

1. ฝังโค้ด VBA เพื่อคัดลอกข้อมูลไปยังชีตอื่นด้วยตัวกรองขั้นสูงใน Excel

ที่นี่ คุณจะได้เรียนรู้ VBA รหัส เพื่อ คัดลอกเฉพาะข้อมูลจาก ต้นฉบับ ชีต เมื่อ John's Marks น้อยกว่า 80 ไปยัง ชีตชื่อ . อื่น เป้าหมาย ด้วยตัวกรองขั้นสูง

ขั้นตอน:

• ในตอนแรก ให้กด Alt + F11 บนแป้นพิมพ์ของคุณหรือไปที่แท็บ นักพัฒนา -> Visual Basic เพื่อเปิด Visual Basic Editor .

• ถัดไป ในหน้าต่างรหัสป๊อปอัป จากแถบเมนู ให้คลิก แทรก -> โมดูล .

• จากนั้น คัดลอกโค้ดต่อไปนี้แล้ววางลงในหน้าต่างโค้ด

Sub AdvancedFilterCode()
Dim iRange As Range
Dim iCriteria As Range
'set the range to filter and the criteria range
Set iRange = Sheets("Original").Range("B4:E12")
Set iCriteria = Sheets("Original").Range("G4:H5")
'copy the filtered data to the destination
iRange.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=iCriteria, CopyToRange:=Sheets("Target").Range("B4:E4"), Unique:=True
End Sub

ตอนนี้รหัสของคุณพร้อมใช้งานแล้ว

• ตอนนี้ กด F5 บนแป้นพิมพ์หรือจากแถบเมนู ให้เลือก เรียกใช้ -> เรียกใช้ Sub/UserForm . คุณยังสามารถคลิกที่ไอคอนการวิ่งขนาดเล็ก ในแถบเมนูย่อยเพื่อเรียกใช้มาโคร

หลังจากรันโค้ดแล้ว ดูภาพด้านล่างเพื่อดูผลลัพธ์

เป็นผลให้เฉพาะข้อมูลที่ John's Marks มีค่าน้อยกว่า 80 คัดลอก ใน เป้าหมาย แผ่นงาน จาก ต้นฉบับ แผ่น ด้วยตัวกรองขั้นสูงของ VBA .

อ่านเพิ่มเติม: วิธีใช้ตัวกรองขั้นสูงเพื่อคัดลอกข้อมูลไปยังชีตอื่นใน Excel

2. ใช้มาโคร VBA เพื่อกรองข้อมูลตามการเลือกที่ผู้ใช้กำหนด

ตอนนี้ คุณจะได้เรียนรู้วิธี กรองข้อมูลเฉพาะจาก ต้นฉบับ แผ่น เมื่อ John's Marks น้อยกว่า 80 ไปยัง ชีตอื่นที่ชื่อว่า Destination จาก ช่วงที่ผู้ใช้เลือก ด้วยตัวกรองขั้นสูงของ VBA ใน Excel

ขั้นตอน:

• เหมือนเมื่อก่อน เปิด Visual Basic Editor จาก นักพัฒนา แท็บและ แทรก โมดูล ในหน้าต่างโค้ด
• จากนั้น ในหน้าต่างโค้ด ให้คัดลอกโค้ดต่อไปนี้แล้ววาง

Sub AdvancedFilterBySelection()
Dim iTrgt As String
Dim iRange As Range
Dim iCriteria As Range
Dim iDestination As Range
On Error Resume Next
iTrgt = ActiveWindow.RangeSelection.Address
Set iRange = Application.InputBox("Select Range to Filter", "Excel", iTrgt, , , , , 8)
If iRange Is Nothing Then Exit Sub
Set iCriteria = Application.InputBox("Select Criteria Range", "Excel", "", , , , , 8)
If iCriteria Is Nothing Then Exit Sub
Set iDestination = Application.InputBox("Select Destination Range", "Excel", "", , , , , 8)
If iDestination Is Nothing Then Exit Sub
iRange.AdvancedFilter xlFilterCopy, iCriteria, iDestination, False
iDestination.Worksheet.Activate
iDestination.Worksheet.Columns.AutoFit
End Sub

ตอนนี้รหัสของคุณพร้อมใช้งานแล้ว

• ตอนนี้ วิ่ง มาโคร
• จากนั้น กล่องป๊อปอัปจะปรากฏขึ้น เลือกช่วง ที่คุณต้องการกรอง (ในกรณีของเราคือตั้งแต่ช่วง B4 ถึง E12 )
• ต่อมา กด ตกลง .

• ถัดไป กล่องป๊อปอัปอื่นจะปรากฏขึ้น คราวนี้คุณต้อง เลือกช่วงเกณฑ์ ที่คุณจัดเก็บไว้ในชุดข้อมูลของคุณ (เกณฑ์มีตั้งแต่ เซลล์ G4 ถึง H5 สำหรับชุดข้อมูลของเรา)
• อีกครั้ง กด ตกลง .

• เป็นครั้งสุดท้าย กล่องป๊อปอัปอื่นจะปรากฏขึ้น และตอนนี้ คุณต้อง เลือกช่วงปลายทาง ที่คุณต้องการจัดเก็บข้อมูลที่คัดลอก . ในกรณีของเราคือ เซลล์ B2 ใน ปลายทาง แผ่น .
• จากนั้น กด ตกลง .

หากต้องการเห็นผลดูที่ภาพด้านล่าง

สุดท้าย เฉพาะข้อมูลที่ John's Marks มีค่าน้อยกว่า 80 คัดลอก ใน ปลายทาง แผ่น จาก ต้นฉบับ แผ่น ด้วยตัวกรองขั้นสูงของ VBA .

เนื้อหาที่เกี่ยวข้อง: ตัวกรองขั้นสูงของ Excel ไม่ทำงาน (2 เหตุผลและวิธีแก้ไข)

การอ่านที่คล้ายกัน

• วิธีใช้ตัวกรองขั้นสูงหากช่วงเกณฑ์มีข้อความใน Excel
• Dynamic Advanced Filter Excel (VBA &Macro)
• ตัวกรองขั้นสูงที่มีช่วงเกณฑ์ใน Excel (18 แอปพลิเคชัน)
• ตัวกรองขั้นสูงพร้อมหลายเกณฑ์ใน Excel (ตัวอย่างที่เหมาะสม 15 ตัวอย่าง)
• ตัวอย่าง Excel VBA:ใช้ตัวกรองขั้นสูงที่มีเกณฑ์ (6 เกณฑ์)

3. ใช้ VBA Macro เพื่อคัดลอกข้อมูลไปยังชีตอื่นโดย Macro Recording ใน Excel

ในส่วนนี้ คุณจะได้ทราบวิธีแก้ปัญหาเดียวกันคือ แยกเฉพาะข้อมูลจาก ต้นฉบับ ชีต เมื่อ John's Marks น้อยกว่า 80 ไปยัง ชีตอื่นที่ชื่อว่า กรองแล้ว โดย การบันทึกมาโคร ของ VBA ใน Excel

ขั้นตอน:

• ก่อนอื่น เปิดแผ่นงานใหม่ (ในกรณีของเราคือชีต กรองแล้ว )
• ในชีตนั้น เก็บเฉพาะแถวส่วนหัว ของชุดข้อมูลเดิม

• ถัดไป ไปที่ ต้นฉบับ แผ่น . คุณจะเห็นเครื่องหมายมาโครขนาดเล็ก ที่ด้านล่างซ้ายของแผ่นงาน คลิกที่ป้าย เพื่อเริ่มบันทึกมาโคร

• จากนั้น บันทึกมาโคร หน้าต่างป๊อปอัปจะปรากฏขึ้น ระบุชื่อมาโคร ที่คุณต้องการ. เรากำหนด AdvancedFilter เป็นชื่อมาโครของเรา .
• ถัดไป เลือกตำแหน่งที่คุณต้องการจัดเก็บมาโคร . เราต้องการเก็บมาโครในสมุดงานที่มีอยู่ เราจึงเลือก สมุดงานนี้ .
• ภายหลัง คลิก ตกลง .

• ตอนนี้ กลับไปที่ ต้นฉบับ แผ่น และคุณจะสังเกตเห็นว่ามาโครที่คุณเพิ่งเริ่มบันทึก

• จากนั้น คุณต้องไปที่ ชีต ที่จะ เก็บข้อมูลที่คัดลอกไว้ (เช่น กรองแล้ว แผ่น)
• มีเซลล์ที่ใช้งานอยู่ ในชีตนั้นและไปที่ ข้อมูล -> ขั้นสูง .

• ถัดไป ตัวกรองขั้นสูง กล่องป๊อปอัปจะปรากฏขึ้น
• ขั้นแรก ตรวจสอบ คัดลอกไปยังตำแหน่งอื่น ตัวเลือกจาก การกระทำ
• ตอนนี้ ในกล่องข้อความข้าง ช่วงรายการ , ไปที่ ต้นฉบับ แผ่น และ เลือกช่วง เพื่อกรอง (สำหรับชุดข้อมูลของเรา ช่วงคือ B4:E12 )

• จากนั้น ในกล่องข้อความข้าง ช่วงเกณฑ์ ให้เลือก ช่วงเกณฑ์ (John's Marks น้อยกว่า 80 ) เก็บไว้ใน Original แผ่น (สำหรับชุดข้อมูลของเรา ช่วงคือ G4:H5 )

• ตอนนี้ ในกล่องข้อความข้าง คัดลอกไปยัง , ไปที่ กรอง แผ่น แผ่นงานที่คุณต้องการ จัดเก็บข้อมูลที่คัดลอก และ เลือกช่วงส่วนหัว (สำหรับชุดข้อมูลของเรา ช่วงคือ B4:E4 )
• สุดท้าย คลิก ตกลง .

หากต้องการดูผลลัพธ์ที่ได้จากกระบวนการทั้งหมดนี้ ให้ดูที่ภาพด้านล่าง เฉพาะข้อมูล โดยที่ John's Marks มีค่าน้อยกว่า 80 คัดลอก ใน กรอง แผ่น จาก ต้นฉบับ แผ่น ด้วย การบันทึกมาโคร .

• ตอนนี้ คลิกที่เครื่องหมายมาโคร ที่ด้านล่างซ้ายของชีต เพื่อหยุดการบันทึกมาโคร . ตอนนี้คุณมีมาโครที่บันทึกไว้ซึ่งจะดำเนินการตามขั้นตอนข้างต้นที่อธิบายไว้ทุกครั้งที่เรียกใช้

แต่มี ข้อเสียเปรียบ ของวิธีนี้ หากคุณเพิ่มข้อมูลใหม่ลงใน ต้นฉบับ แผ่น , กรอง แผ่น จะไม่ได้รับการอัปเดตแม้ว่าข้อมูลจะเป็นไปตามเกณฑ์ก็ตาม

เราต้องการ กรอง . ใหม่ของเรา แผ่น ที่จะอัปเดตโดยอัตโนมัติโดยการรันโค้ดเมื่อเราเพิ่มข้อมูลใหม่ลงใน Original แผ่น . ในการทำเช่นนั้น เราต้องแก้ไขโค้ดเล็กน้อย

ขั้นตอนทั้งหมดเพื่อให้ได้สิ่งที่เราต้องการแสดงอยู่ด้านล่าง

ขั้นตอน:

• ในตอนแรก จากแท็บ ให้เลือก ดู -> มาโคร -> ดูมาโคร .

• ต่อมา มาโคร หน้าต่างป๊อปอัปจะปรากฏขึ้น เลือก ชื่อมาโคร ที่คุณเพิ่ง สร้างขึ้นโดยการบันทึก (ตัวกรองขั้นสูง สำหรับกรณีของเรา)
• จากนั้น คลิก แก้ไข .

• โค้ดด้านหลังมาโครที่บันทึกไว้จะปรากฏในหน้าต่างโค้ด (ดูภาพด้านล่าง)

• ตอนนี้ ลบส่วนที่ทำเครื่องหมายสีน้ำเงิน (แสดงในภาพด้านล่าง) จากโค้ด

• จากนั้น แก้ไขโค้ด ดังแสดงในภาพต่อไปนี้

• ตอนนี้ รหัสที่อัปเดต จะเป็น:

Sub AdvancedFilter()
Sheets("Original").Range("B4").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheets("Original").Range("G4:H5"), CopyToRange:=Sheets("Filtered").Range("B4:E4"), Unique:=False
End Sub

• บันทึก รหัสนี้
• ตอนนี้ กลับไปที่ ต้นฉบับ แผ่น และ เพิ่มข้อมูลใหม่ ซึ่งจะเข้าเกณฑ์ ตัวอย่างเช่น เราได้เพิ่ม John . อีกแถวหนึ่ง ข้อมูลโดยที่ คะแนนที่ได้รับคือ 76 ซึ่งเข้าเกณฑ์ คะแนนน้อยกว่า 80 .

• จากนั้น วิ่ง รหัสและดูที่ภาพต่อไปนี้สำหรับผลลัพธ์

• มี แถวที่คัดลอกใหม่ ใน กรอง แผ่น ของ John ข้อมูลของ เครื่องหมาย 76 ที่เข้าเกณฑ์ (เครื่องหมาย<80 )

อ่านเพิ่มเติม:วิธีใช้ตัวกรองขั้นสูงสำหรับระเบียนที่ไม่ซ้ำเฉพาะใน Excel

บทสรุป

สรุป บทความนี้แสดงให้คุณเห็น 3 เกณฑ์ที่แตกต่างกันเกี่ยวกับวิธีการคัดลอกข้อมูลไปยังชีตอื่นด้วยตัวกรองขั้นสูง ใน Excel ด้วย มาโคร VBA . ฉันหวังว่าบทความนี้จะเป็นประโยชน์กับคุณมาก อย่าลังเลที่จะถามคำถามใด ๆ เกี่ยวกับหัวข้อนี้

บทความที่เกี่ยวข้อง

• วิธีใช้ตัวกรองขั้นสูงเพื่อยกเว้นเซลล์ว่างใน Excel (3 เคล็ดลับง่ายๆ)
• Excel VBA:ตัวกรองขั้นสูงที่มีหลายเกณฑ์ในช่วง (5 วิธี)
• วิธีใช้ตัวกรองขั้นสูงสำหรับระเบียนที่ไม่ซ้ำเฉพาะใน Excel
• ใช้ตัวกรองขั้นสูงเพื่อคัดลอกไปยังตำแหน่งอื่นใน Excel
• ตัวกรองขั้นสูงของ Excel:ใช้ “ไม่มี” (2 วิธี)
• ใช้ตัวกรองขั้นสูงตามเกณฑ์หลายเกณฑ์ในคอลัมน์เดียวใน Excel