RSS

Category Archives: Sheet

การเพิ่ม Sheet อัตโนมัติตามรายชื่อที่ให้ไว้ล่วงหน้าด้วย VBA

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

  1. เพิ่มชีทใหม่มา 1 ชีทโดยให้ชื่อว่า Sheet1 > Copy รายชื่อมาวางโดยเริ่มที่เซลล์ A1
  2. จากนั้นเปิดหน้าต่าง VBE โดยการกดแป้น Alt+F11 > เข้าเมนู Insert > Module แล้วนำ Code ด้านล่างไปวาง

    Sub AddWorkSheets() 
    Dim i As Long
    Dim r As Range
    On Error Resume Next
    With Worksheets("Sheet1")
    Set r = .Range("A1", .Range("A65536").End(xlUp))
    End With
    For i = 1 To r.Count
    Worksheets.Add(After:=Worksheets(Worksheets.Count)) _
    .Name = r.Cells(i, 1).Value
    Next i
    End Sub

  3. เมื่อต้องการ Run Code กดแป้น Alt+F8 > เลือก AddWorkSheets > Run
 
Leave a comment

Posted by on 31/12/2012 in Sheet

 

การ List รายชื่อ Sheet

เนื่อง จากมีคำถามเกี่ยวกับวิธีการ List รายชื่อ Sheet ค่อนข้างบ่อย และ Pantip เองก็มีการลบกระทู้ค่อนข้างบ่อย ผมก็เลยต้องตอบคำถามเดิม ๆ ค่อนข้างบ่อยเป็นลูกโซ่ เลยนำมาเขียนไว้ที่นี่เพื่อจะได้ใช้อ้างอิง ไม่ต้องเขียนใหม่ หรือ Copy มาแปะทุกครั้งที่เจอคำถาม

สำหรับ วิธีการนี้ใช้ Excel 4 Macro Function มาช่วย ซึ่งต้องกำหนด Security ในเมนู Tools > Macro > Security เป็นระดับ Medium หรือ Low ถึงจะใช้ได้

วิธีการทำดังนี้ครับ

  1. เปิดไฟล์ที่ต้องการ List รายชื่อ Sheet โดยทำการเพิ่ม 1 Sheet คือ Sheet ที่ต้องการให้แสดงรายชื่อ Sheet ทั้งหมด
  2. เข้าเมนู Insert > Name > Define >
    1. ช่อง Names in workbook: คีย์ CountSheet > ช่อง Refers to: คีย์ =Get.Workbook(4) > Add
    2. ช่อง Names in workbook: คีย์ ListSheet > ช่อง Refers to: คีย์
      =RIGHT(GET.WORKBOOK(1),LEN(GET.WORKBOOK(1))-FIND(“]”,GET.WORKBOOK(1))) > OK
  3. ใน Sheet ที่เพิ่มเข้ามาใหม
    1. เซล A1 คีย์
      =CountSheet
      Enter
    2. เซล A2 คีย์
      =IF(ROWS($A$2:A2)<=$A$1,INDEX(ListSheet,ROWS($A$2:A2)),””)
      Enter แล้ว Copy ลงด้านล่าง
 
4 Comments

Posted by on 09/11/2008 in List, Macro Function, Sheet

 
 
Follow

Get every new post delivered to your Inbox.

Join 791 other followers