學(xué)習(xí)啦 > 學(xué)習(xí)電腦 > 工具軟件 > 辦公軟件學(xué)習(xí) > Excel教程 > Excel2007教程 > excel2007拆分工作表的教程

excel2007拆分工作表的教程

時間: 業(yè)華773 分享

excel2007拆分工作表的教程

  Excel中工作表具體該如何拆分呢?接下來是學(xué)習(xí)啦小編為大家?guī)淼膃xcel2007拆分工作表的教程,供大家參考。

  excel2007拆分工作表的教程:

  拆分工作表步驟1:首先來看一下原始數(shù)據(jù),A列是日期,B列是姓名,后面是名稱和數(shù)量。需要根據(jù)B列的姓名將每個人的數(shù)據(jù)記錄存儲為一個單獨(dú)的工作簿文件。

  拆分工作表步驟2:按Alt+F11進(jìn)入VBE界面。

  拆分工作表步驟3:選擇【插入】-【模塊】插入一個模塊。

  拆分工作表步驟4:雙擊模塊名稱,在右側(cè)的空白處粘貼下列代碼:

  Sub CFGZB()

  Dim myRange As Variant

  Dim myArray

  Dim titleRange As Range

  Dim title As String

  Dim columnNum As Integer

  myRange = Application.InputBox(prompt:="請選擇標(biāo)題行:", Type:=8)

  myArray = WorksheetFunction.Transpose(myRange)

  Set titleRange = Application.InputBox(prompt:="請選擇拆分的表頭,必須是第一行,且為一個單元格,如:“姓名”", Type:=8)

  title = titleRange.Value

  columnNum = titleRange.Column

  Application.ScreenUpdating = False

  Application.DisplayAlerts = False

  Dim i&, Myr&, Arr, num&

  Dim d, k

  For i = Sheets.Count To 1 Step -1

  If Sheets(i).Name <> "數(shù)據(jù)源" Then

  Sheets(i).Delete

  End If

  Next i

  Set d = CreateObject("Scripting.Dictionary")

  Myr = Worksheets("數(shù)據(jù)源").UsedRange.Rows.Count

  Arr = Worksheets("數(shù)據(jù)源").Range(Cells(2, columnNum), Cells(Myr, columnNum))

  For i = 1 To UBound(Arr)

  d(Arr(i, 1)) = ""

  Next

  k = d.keys

  For i = 0 To UBound(k) - 1

  Set conn = CreateObject("adodb.connection")

  conn.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.FullName

  Sql = "select * from [數(shù)據(jù)源$] where " & title & " = '" & k(i) & "'"

  Dim Nowbook As Workbook

  Set Nowbook = Workbooks.Add

  With Nowbook

  With .Sheets(1)

  .Name = k(i)

  For num = 1 To UBound(myArray)

  .Cells(1, num) = myArray(num, 1)

  Next num

  .Range("A2").CopyFromRecordset conn.Execute(Sql)

  End With

  End With

  ThisWorkbook.Activate

  Sheets(1).Cells.Select

  Selection.Copy

  Workbooks(Nowbook.Name).Activate

  ActiveSheet.Cells.Select

  Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _

  SkipBlanks:=False, Transpose:=False

  Application.CutCopyMode = False

  Nowbook.SaveAs ThisWorkbook.Path & "\" & k(i)

  Nowbook.Close True

  Set Nowbook = Nothing

  Next i

  conn.Close

  Set conn = Nothing

  Application.DisplayAlerts = True

  Application.ScreenUpdating = True

  End Sub

  拆分工作表步驟5:返回Excel工作表界面,選擇【開發(fā)工具】-【插入】-【表單控件】-【按鈕】。

  拆分工作表步驟6:選擇完插入按鈕后會彈出指定宏的界面,選定剛才插入的“CFGZB”宏,并確定。

  拆分工作表步驟7:點(diǎn)擊插入的按鈕,根據(jù)提示選擇標(biāo)題行。

  拆分工作表步驟8:根據(jù)提示,選擇要拆分的列標(biāo)題,本例是“姓名”。

  拆分工作表步驟9:再次點(diǎn)擊確定后,宏會自動運(yùn)行。運(yùn)行完畢后工作簿所在的同路徑下會出現(xiàn)以每個人名命名的獨(dú)立工作簿。打開每個單個工作簿就可以看到每個人自己的數(shù)據(jù)記錄。

761013