SORT関数×FILTER関数を仕込んだシートを、VBAでシートコピーしてシートごとにファイルに分けたハナシ
Power Automate for desktopが使えない環境で、繰り返し作業が多いのですが もくもく手作業だなんて やってらんない。。。
でもあんまりVBAも書きたくない🥺
という訳で、できるだけ関数を活用しつつ 久々にVBA書いて(いえ、検索してコピーして)みたので、メモとして残しておきます🙋♀️
使用する元データは、「システムからダウンロードして来たデータを、Power Queryで こねこねしたもの」です。
いつぞやのnoteのときに使用したPower Queryでデータ整形したファイルがありましたので、こちらを使用します。
「年月」のシートを作成して、そのシートには「年月」のデータを「社員コード」の昇順にしたデータを格納し、シートの名前をファイル名にして保存する
というものを作りたいと思います!
■Excel関数
① 原本シートの準備
まず、シートコピーする原本ファイルを作成します。
ちゃんとデータが正しく表示されるか確認するために、シート名は「202101」としておきます。
数式に入っているシート名を、関数で持って来ます。
こちらの記事を参考にさせていただきました🙏✨
"202101" の部分を、記事からコピペ🎵して、シート名を「★原本」に変更します。
(「★原本」というデータはないので、エラーになっててOK🙆♀️)
② シート名の一覧の準備
「★シート名一覧」シートを作成して、UNIQUE関数でA列の値一覧を作成します。
列で選択すると、最後が0になっちゃうけど気にしないん😋
■VBA
① 原本シートをコピーして、シート名をシート名一覧のデータにする
参考にさせていただいたのは、こちら。
ちょっと変更して、こんな感じになりました!
(自分メモなので参考にはしないでください…)
Sub sheet_copy()
Dim yyyymm As Range
For Each yyyymm In Worksheets("★シート名一覧").Range("A2:A7")
Worksheets("★原本").Copy After:=Worksheets(Worksheets.Count)
With ActiveSheet
.Name = yyyymm.Value
End With
Next yyyymm
End Sub
範囲の指定は変動するなら最終行を取得して…とやるのがベストですが、長くなるので今回はベタ打ちで🙈
実行すると・・・
はいっ、こんな感じ!
よしよし、大丈夫だよね~と中身を確認して、上書き保存。
② 「★」から始まらないシートを、値貼付けして別ファイルにする
「★から始まらない」を目印にしちゃうので、「一覧」シートを「★一覧」シートにしちゃいました😋
参考にさせていただきたのは、こちら。🙏✨
こちらもちょっと変更して、こんな感じになりました!
(再びですが、自分メモなので くれぐれも参考にはしないでください…)
Sub sheets_save()
Dim ws As Worksheet
For Each ws In Worksheets
If Not ws.Name Like "★*" Then
ws.Range("A:H").Copy
ws.Range("A1").PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
ws.Activate
ws.Range("A1").Select
ws.Copy
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & ws.Name
ActiveWorkbook.Close
End If
Next ws
ThisWorkbook.Close saveChanges:=False
End Sub
各シートの値は関数で表示されているので、値貼付けした後で別のブックにシート名をファイル名にして保存しています。
処理が終わったら、マクロを含んだファイルを保存せずに閉じることで、値貼付けが反映されないようにしました。
値貼付けについて、参考にさせていただいたのはこちら🙏✨
「★から始まらない」について、参考にさせていただいたのはこちら🙏✨
所感
VBAを極力使わなくていいように、関数で工夫してみました😊
いいやり方なのかはわかりませんが、全自動化は求めてないのでコードが複雑にならなくて気に入ってます🎵
きっとこれから どんどん欲が出て来るんでしょうけどね・・・😂
地味~に躓いたのが、値貼付け後に範囲指定されたままになるのが気に入らなくて、A1をSelectしようとしたときの
「RangeクラスのSelectメソッドが失敗しました」!!
同じシートなのに、Activateしないといけないんですね…
ここに一番ハマりました😂
助けていただいたサイトはこちら🙏✨
業務でVBAらしいVBA(とも言えないでしょうが💦)を、自分で書いて(ほぼコピペ🙊)使ったのは初めてですが、これは…楽しいですね…🤤
VBAは いつMicrosoftさんに消されてしまうかわかんないし、あんまり多用はしたくはないのですが、人に渡さずに自分だけで使う分にはありかな🎵
様子を見て、活用して行きたいと思います~✊🔥
事務員が少しの背伸びでできる効率化を目指す🌈✨ 自分の好きなものを、楽しく発信していきたいです! いただいたサポートは学習費にあてさせていただきます🥰