\u524d\u56de\u306b\u5f15\u304d\u7d9a\u304d\u3001Copilot\u304c\u3042\u308c\u3070VBA\u304c\u3081\u3063\u3061\u3083\u4fbf\u5229\u306b\u306a\u308b\u3088\uff01\u3063\u3066\u3044\u3046\u8a71\u3092\u3057\u3066\u3044\u304d\u305f\u3044\u3068\u601d\u3044\u307e\u3059\u3002\u5b9f\u4f8b\u3092\u7528\u3044\u3066\u8aac\u660e\u3057\u3066\u307f\u307e\u3059\u3002<\/p>\n\n\n\n
\u4f8b\u3048\u3070\u3001\u4e0b\u8a18\u306e\u3088\u3046\u306a\u30c6\u30b9\u30c8\u7528\u306e\u30d5\u30a1\u30a4\u30eb\u3068\u30d7\u30ed\u30f3\u30d7\u30c8\u3092\u4f5c\u3063\u3066\u3076\u3093\u6295\u3052\u3066\u307f\u307e\u3059\u3002<\/p>\n\n\n\n
[html]<iframe src=”https:\/\/1drv.ms\/x\/c\/c7d0da5604634246\/IQTGS2gTjfC5SKuX1mFmymF8Aalzf69IhyJTof7ulaZXCCQ” width=”402″ height=”346″ frameborder=”0″ scrolling=”no”><\/iframe>[\/html]<\/p>\n\n\n\n
\n\u3053\u306e\u30d5\u30a1\u30a4\u30eb\u3092\u3001\u5404\u90e8\u9580\u306b\u6240\u5c5e\u3059\u308b\u62c5\u5f53\u8005\u3092\u3059\u3079\u3066\u307e\u3068\u3081\u305f\u300c1_\u55b6\u696d\u90e8.xlsx\u300d\u306e\u3088\u3046\u306a\u540d\u524d\u3067\u65b0\u3057\u304f\u30d5\u30a9\u30eb\u30c0\u3092\u4f5c\u6210\u3057\u3001\u540c\u540d\u306eExcel\u306b\u3057\u3066\u4fdd\u5b58\u3059\u308b\u3088\u3046\u306aVBA\u3092\u4f5c\u6210\u3057\u3066\u304f\u3060\u3055\u3044\u3002\u540c\u3058\u540d\u524d\u306e\u30d5\u30a1\u30a4\u30eb\u304c\u3042\u308b\u5834\u5408\u306f\u4e0a\u66f8\u304d\u3057\u3066\u304f\u3060\u3055\u3044\u3002\u00a0<\/p>\n<\/blockquote>\n\n\n\n
\u3053\u308c\u306b\u5bfe\u3057\u3066\u3001Copilot\u306f\u4ee5\u4e0b\u306e\u3088\u3046\u306a\u56de\u7b54\u3092\u304f\u308c\u307e\u3057\u305f\u3002<\/p>\n\n\n\n
\n\u4ee5\u4e0b\u306eVBA\u30b3\u30fc\u30c9\u3092\u4f7f\u7528\u3057\u3066\u3001\u5404\u90e8\u9580\u306b\u6240\u5c5e\u3059\u308b\u62c5\u5f53\u8005\u3092\u3059\u3079\u3066\u307e\u3068\u3081\u305fExcel\u30d5\u30a1\u30a4\u30eb\u3092\u4f5c\u6210\u3057\u3001\u90e8\u9580\u3054\u3068\u306b\u65b0\u3057\u3044\u30d5\u30a9\u30eb\u30c0\u3092\u4f5c\u6210\u3057\u3066\u4fdd\u5b58\u3059\u308b\u3053\u3068\u304c\u3067\u304d\u307e\u3059\u3002\u540c\u3058\u540d\u524d\u306e\u30d5\u30a1\u30a4\u30eb\u304c\u3042\u308b\u5834\u5408\u306f\u4e0a\u66f8\u304d\u3055\u308c\u307e\u3059\u3002 <\/p>\n\n\n\n
Sub SplitWorkbookByDepartment()
Dim wb As Workbook
Dim ws As Worksheet
Dim lastRow As Long
Dim department As String
Dim cell As Range
Dim newWb As Workbook
Dim newWs As Worksheet
Dim savePath As String
Dim folderPath As String
Dim dict As Object
Dim i As Long
‘ \u5143\u306e\u30ef\u30fc\u30af\u30d6\u30c3\u30af\u3092\u958b\u304f
Set wb = Workbooks.Open(ThisWorkbook.Path & “\\\u30c6\u30b9\u30c8\u7528\u53f0\u5e33.xlsx”)
Set ws = wb.Sheets(1)
‘ \u6700\u7d42\u884c\u3092\u53d6\u5f97
lastRow = ws.Cells(ws.Rows.Count, “A”).End(xlUp).Row
‘ \u90e8\u9580\u3054\u3068\u306b\u30c7\u30fc\u30bf\u3092\u683c\u7d0d\u3059\u308b\u30c7\u30a3\u30af\u30b7\u30e7\u30ca\u30ea\u3092\u4f5c\u6210
Set dict = CreateObject(“Scripting.Dictionary”)
‘ \u30c7\u30fc\u30bf\u3092\u30c7\u30a3\u30af\u30b7\u30e7\u30ca\u30ea\u306b\u683c\u7d0d
For i = 2 To lastRow
department = ws.Cells(i, 1).Value
If Not dict.exists(department) Then
dict.Add department, New Collection
End If
dict(department).Add ws.Rows(i)
Next i
‘ \u90e8\u9580\u3054\u3068\u306b\u65b0\u3057\u3044\u30ef\u30fc\u30af\u30d6\u30c3\u30af\u3092\u4f5c\u6210\u3057\u3066\u4fdd\u5b58
For Each department In dict.keys
folderPath = ThisWorkbook.Path & “\\” & department
savePath = folderPath & “\\” & “1_” & department & “.xlsx”
‘ \u30d5\u30a9\u30eb\u30c0\u304c\u5b58\u5728\u3057\u306a\u3044\u5834\u5408\u306f\u4f5c\u6210
If Dir(folderPath, vbDirectory) = “” Then
MkDir folderPath
End If
‘ \u65b0\u3057\u3044\u30ef\u30fc\u30af\u30d6\u30c3\u30af\u3092\u4f5c\u6210
Set newWb = Workbooks.Add
Set newWs = newWb.Sheets(1)
‘ \u30d8\u30c3\u30c0\u30fc\u3092\u30b3\u30d4\u30fc
ws.Rows(1).Copy Destination:=newWs.Rows(1)
‘ \u90e8\u9580\u306e\u30c7\u30fc\u30bf\u3092\u30b3\u30d4\u30fc
For Each cell In dict(department)
cell.Copy Destination:=newWs.Rows(newWs.Cells(newWs.Rows.Count, “A”).End(xlUp).Row + 1)
Next cell
‘ \u65b0\u3057\u3044\u30ef\u30fc\u30af\u30d6\u30c3\u30af\u3092\u4fdd\u5b58
newWb.SaveAs Filename:=savePath, FileFormat:=xlOpenXMLWorkbook
newWb.Close SaveChanges:=False
Next department
‘ \u5143\u306e\u30ef\u30fc\u30af\u30d6\u30c3\u30af\u3092\u9589\u3058\u308b
wb.Close SaveChanges:=False
MsgBox “\u90e8\u9580\u5225\u306b\u30d5\u30a1\u30a4\u30eb\u304c\u4f5c\u6210\u3055\u308c\u307e\u3057\u305f\u3002”
End Sub
<\/p>\n\n\n\n\u3053\u306e\u30b3\u30fc\u30c9\u3092Excel\u306eVBA\u30a8\u30c7\u30a3\u30bf\u306b\u8cbc\u308a\u4ed8\u3051\u3066\u5b9f\u884c\u3059\u308b\u3053\u3068\u3067\u3001\u5404\u90e8\u9580\u306b\u6240\u5c5e\u3059\u308b\u62c5\u5f53\u8005\u3092\u3059\u3079\u3066\u307e\u3068\u3081\u305fExcel\u30d5\u30a1\u30a4\u30eb\u3092\u90e8\u9580\u3054\u3068\u306b\u65b0\u3057\u3044\u30d5\u30a9\u30eb\u30c0\u306b\u4fdd\u5b58\u3059\u308b\u3053\u3068\u304c\u3067\u304d\u307e\u3059\u3002\u4f55\u304b\u4ed6\u306b\u304a\u624b\u4f1d\u3044\u3067\u304d\u308b\u3053\u3068\u304c\u3042\u308c\u3070\u6559\u3048\u3066\u304f\u3060\u3055\u3044\uff01 <\/p>\n<\/blockquote>\n\n\n\n
\u3057\u304b\u3057\u3001\u3053\u308c\u3092\u305d\u306e\u307e\u307e\u5b9f\u884c\u3059\u308b\u3068\u30a8\u30e9\u30fc\u304c\u767a\u751f\u3059\u308b\u306e\u3067\u3001
\u4e0b\u8a18\u306e\u3088\u3046\u306b\u30a8\u30e9\u30fc\u3092\u305d\u306e\u307e\u307e\u6559\u3048\u3066\u3042\u3052\u307e\u3059\u3002<\/p>\n\n\n\n\nFor Each department In dict.keys\u306e\u7b87\u6240\u3067\u3001\u300cFor Each\u306b\u6307\u5b9a\u3059\u308b\u5909\u6570\u306f\u30d0\u30ea\u30a2\u30f3\u30c8\u578b\u307e\u305f\u306f\u30aa\u30d6\u30b8\u30a7\u30af\u30c8\u578b\u3067\u306a\u3051\u308c\u3070\u306a\u308a\u307e\u305b\u3093\u3002\u300d\u3068\u3044\u3046\u30a8\u30e9\u30fc\u304c\u51fa\u307e\u3059\u3002\u00a0<\/p>\n<\/blockquote>\n\n\n\n
\u3059\u308b\u3068\u3001\u3061\u3083\u3093\u3068\u305d\u306e\u610f\u5473\u3092\u7406\u89e3\u3057\u3066\u4fee\u6b63\u3057\u305f\u30b3\u30fc\u30c9\u3092\u518d\u5ea6\u56de\u7b54\u3057\u3066\u304f\u308c\u307e\u3059\u3002<\/p>\n\n\n\n
\n\u30a8\u30e9\u30fc\u306e\u539f\u56e0\u306f\u3001department\u5909\u6570\u304c\u9069\u5207\u306a\u578b\u3068\u3057\u3066\u5ba3\u8a00\u3055\u308c\u3066\u3044\u306a\u3044\u305f\u3081\u3067\u3059\u3002department\u5909\u6570\u3092Variant\u578b\u3068\u3057\u3066\u5ba3\u8a00\u3059\u308b\u3053\u3068\u3067\u3001\u3053\u306e\u30a8\u30e9\u30fc\u3092\u89e3\u6d88\u3067\u304d\u307e\u3059\u3002\u4ee5\u4e0b\u306e\u4fee\u6b63\u3055\u308c\u305f\u30b3\u30fc\u30c9\u3092\u8a66\u3057\u3066\u307f\u3066\u304f\u3060\u3055\u3044\u3002 <\/p>\n\n\n\n
Sub SplitWorkbookByDepartment()
Dim wb As Workbook
Dim ws As Worksheet
Dim lastRow As Long
Dim department As Variant ‘ Variant\u578b\u306b\u5909\u66f4
Dim cell As Range
Dim newWb As Workbook
Dim newWs As Worksheet
Dim savePath As String
Dim folderPath As String
Dim dict As Object
Dim i As Long
‘ \u5143\u306e\u30ef\u30fc\u30af\u30d6\u30c3\u30af\u3092\u958b\u304f
Set wb = Workbooks.Open(ThisWorkbook.Path & “\\\u30c6\u30b9\u30c8\u7528\u53f0\u5e33.xlsx”)
Set ws = wb.Sheets(1)
‘ \u6700\u7d42\u884c\u3092\u53d6\u5f97
lastRow = ws.Cells(ws.Rows.Count, “A”).End(xlUp).Row
‘ \u90e8\u9580\u3054\u3068\u306b\u30c7\u30fc\u30bf\u3092\u683c\u7d0d\u3059\u308b\u30c7\u30a3\u30af\u30b7\u30e7\u30ca\u30ea\u3092\u4f5c\u6210
Set dict = CreateObject(“Scripting.Dictionary”)
‘ \u30c7\u30fc\u30bf\u3092\u30c7\u30a3\u30af\u30b7\u30e7\u30ca\u30ea\u306b\u683c\u7d0d
For i = 2 To lastRow
department = ws.Cells(i, 1).Value
If Not dict.exists(department) Then
dict.Add department, New Collection
End If
dict(department).Add ws.Rows(i)
Next i
‘ \u90e8\u9580\u3054\u3068\u306b\u65b0\u3057\u3044\u30ef\u30fc\u30af\u30d6\u30c3\u30af\u3092\u4f5c\u6210\u3057\u3066\u4fdd\u5b58
For Each department In dict.keys
folderPath = ThisWorkbook.Path & “\\” & department
savePath = folderPath & “\\” & “1_” & department & “.xlsx”
‘ \u30d5\u30a9\u30eb\u30c0\u304c\u5b58\u5728\u3057\u306a\u3044\u5834\u5408\u306f\u4f5c\u6210
If Dir(folderPath, vbDirectory) = “” Then
MkDir folderPath
End If
‘ \u65b0\u3057\u3044\u30ef\u30fc\u30af\u30d6\u30c3\u30af\u3092\u4f5c\u6210
Set newWb = Workbooks.Add
Set newWs = newWb.Sheets(1)
‘ \u30d8\u30c3\u30c0\u30fc\u3092\u30b3\u30d4\u30fc
ws.Rows(1).Copy Destination:=newWs.Rows(1)
‘ \u90e8\u9580\u306e\u30c7\u30fc\u30bf\u3092\u30b3\u30d4\u30fc
For Each cell In dict(department)
cell.Copy Destination:=newWs.Rows(newWs.Cells(newWs.Rows.Count, “A”).End(xlUp).Row + 1)
Next cell
‘ \u65b0\u3057\u3044\u30ef\u30fc\u30af\u30d6\u30c3\u30af\u3092\u4fdd\u5b58
newWb.SaveAs Filename:=savePath, FileFormat:=xlOpenXMLWorkbook
newWb.Close SaveChanges:=False
Next department
‘ \u5143\u306e\u30ef\u30fc\u30af\u30d6\u30c3\u30af\u3092\u9589\u3058\u308b
wb.Close SaveChanges:=False
MsgBox “\u90e8\u9580\u5225\u306b\u30d5\u30a1\u30a4\u30eb\u304c\u4f5c\u6210\u3055\u308c\u307e\u3057\u305f\u3002”
End Sub
<\/p>\n\n\n\n\u3053\u306e\u4fee\u6b63\u306b\u3088\u308a\u3001department\u5909\u6570\u304cVariant\u578b\u3068\u3057\u3066\u5ba3\u8a00\u3055\u308c\u3001\u30a8\u30e9\u30fc\u304c\u89e3\u6d88\u3055\u308c\u308b\u306f\u305a\u3067\u3059\u3002\u518d\u5ea6\u5b9f\u884c\u3057\u3066\u307f\u3066\u304f\u3060\u3055\u3044\u3002\u4f55\u304b\u4ed6\u306b\u304a\u624b\u4f1d\u3044\u3067\u304d\u308b\u3053\u3068\u304c\u3042\u308c\u3070\u6559\u3048\u3066\u304f\u3060\u3055\u3044\uff01 <\/p>\n<\/blockquote>\n\n\n\n
\u3084\u3060\u3001\u7d20\u6575
\u3061\u3083\u3093\u3068\u4f1d\u308f\u3063\u3066\u308b\u3058\u3083\u306a\u3044\u3002
\u3066\u306a\u611f\u3058\u3067\u4e0a\u624b\u304f\u4fee\u6b63\u3057\u3066\u304f\u308c\u307e\u3059\u3002<\/p>\n\n\n\n\u3053\u3046\u3044\u3046\u7cfb\u306e\u66f8\u985e\u3063\u3066\u3001\u5272\u3068\u591a\u3044\u3093\u3067\u3059\u3088\u306d\u3002
\u4eca\u307e\u3067\u306f\u306a\u308b\u3079\u304f\u7c21\u5358\u306b\u5f8c\u3067\u8ab0\u304b\u3061\u3087\u3063\u3068\u8a73\u3057\u3044\u4eba\u304c\u898b\u308c\u3070\u308f\u304b\u308b\u3060\u308d\u3046\u304f\u3089\u3044\u306e\u611f\u3058\u3067\u4f5c\u6210\u3057\u3066\u3044\u307e\u3057\u305f\u304c\u3001\u3053\u3053\u307e\u3067AI\u304c\u3067\u304d\u308c\u3070\u3001\u3082\u3063\u3068\u591a\u304f\u306e\u4eba\u304c\u81ea\u5206\u305f\u3061\u3067\u696d\u52d9\u6539\u5584\u3067\u304d\u305d\u3046\u306a\u6c17\u304c\u3057\u307e\u3059\u3002\u00a0<\/p>\n\n\n\n\u3068\u3044\u3046\u304b\u3001\u4f7f\u3044\u65b9\u3092\u6559\u3048\u308b\u304b\u3089\u3001\u3061\u3087\u3063\u3068\u81ea\u7531\u306b\u3084\u3063\u3066\u307f\u3066\u00a0
\u3063\u3066\u3044\u3046\u3053\u3068\u3082\u3067\u304d\u308b\u3068\u601d\u3046\u3002
\u3057\u304b\u3082\u3001\u3061\u3083\u3093\u3068\u30b3\u30e1\u30f3\u30c8\u3092\u5165\u308c\u3066\u304f\u308c\u3066\u3044\u308b\u306e\u3067\u3001\u00a0
\u3053\u308c\u307e\u3067\u81ea\u5b66\u3067\u5b66\u3093\u3067\u3044\u305f\u4eba\u305f\u3061\u306a\u3089\u3001\u5fdc\u7528\u3055\u305b\u3066\u3044\u304f\u3053\u3068\u3082\u5bb9\u6613\u306b\u306a\u3063\u305f\u3093\u3058\u3083\u306a\u3044\u304b\u3068\u611f\u3058\u307e\u3059\u3002\u00a0<\/p>\n\n\n\n\u3053\u3046\u3044\u3046\u4ed5\u4e8b\u3092\u3084\u3063\u3066\u3044\u3066\u3001\u3044\u307e\u3055\u3089\u306a\u3093\u3067\u6065\u305a\u304b\u3057\u3044\u3053\u3068\u306a\u3093\u3067\u3059\u3051\u3069\u00a0
\u3061\u3087\u3063\u3068\u9a5a\u3044\u305f\u306e\u3067\u66f8\u304b\u305b\u3066\u3044\u305f\u3060\u304d\u307e\u3057\u305f\u3002\u00a0
\u307b\u304b\u306e\u8a00\u8a9e\u3082\u3067\u304d\u308b\u3088\u3046\u306b\u306a\u3063\u3066\u305d\u3046\u3067\u3059\u306d\u3002Python\u3068\u304b\u3002\u00a0
\u3061\u3083\u3093\u3068\u4f7f\u3048\u3070\u5de5\u6570\u6fc0\u6e1b\u3067\u304d\u305d\u3046\u3067\u3059\u306d\u3002\u4f7f\u3044\u3053\u306a\u3057\u3066\u3044\u308b\u4eba\u3068\u305d\u3046\u3067\u306a\u3044\u4eba\u306e\u5dee\u304c\u6fc0\u3057\u304f\u51fa\u308b\u00a0
\u3063\u3066\u3044\u3046\u610f\u5473\u304c\u3061\u3087\u3063\u3068\u5206\u304b\u308a\u307e\u3057\u305f\u3002\u00a0<\/p>\n","protected":false},"excerpt":{"rendered":"\u524d\u56de\u306b\u5f15\u304d\u7d9a\u304d\u3001Copilot\u304c\u3042\u308c\u3070VBA\u304c\u3081\u3063\u3061\u3083\u4fbf\u5229\u306b\u306a\u308b\u3088\uff01\u3063\u3066\u3044\u3046\u8a71\u3092\u3057\u3066\u3044\u304d\u305f\u3044\u3068\u601d\u3044\u307e\u3059\u3002\u5b9f\u4f8b\u3092\u7528\u3044\u3066\u8aac\u660e\u3057\u3066\u307f\u307e\u3059\u3002 \u4f8b\u3048\u3070\u3001\u4e0b\u8a18\u306e\u3088\u3046\u306a\u30c6\u30b9\u30c8\u7528\u306e\u30d5\u30a1\u30a4\u30eb\u3068\u30d7\u30ed\u30f3\u30d7\u30c8\u3092\u4f5c\u3063\u3066\u3076\u3093\u6295\u3052\u3066\u307f\u307e\u3059\u3002 [htm…<\/p>\n","protected":false},"author":1,"featured_media":119,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[12],"tags":[21,23],"class_list":["post-118","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-it","tag-vba","tag-23"],"jetpack_featured_media_url":"https:\/\/ittk-shop.com\/wp-content\/uploads\/2025\/02\/\u30c0\u30a6\u30f3\u30ed\u30fc\u30c9.jpeg","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/ittk-shop.com\/index.php?rest_route=\/wp\/v2\/posts\/118","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ittk-shop.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ittk-shop.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ittk-shop.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ittk-shop.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=118"}],"version-history":[{"count":1,"href":"https:\/\/ittk-shop.com\/index.php?rest_route=\/wp\/v2\/posts\/118\/revisions"}],"predecessor-version":[{"id":120,"href":"https:\/\/ittk-shop.com\/index.php?rest_route=\/wp\/v2\/posts\/118\/revisions\/120"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ittk-shop.com\/index.php?rest_route=\/wp\/v2\/media\/119"}],"wp:attachment":[{"href":"https:\/\/ittk-shop.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=118"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ittk-shop.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=118"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ittk-shop.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=118"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}