SORT関数×FILTER関数を仕込んだシートを、VBAでシートコピーしてシートごとにファイルに分けたハナシ

まる🍑

2023年3月20日 21:13

Power Automate for desktopが使えない環境で、繰り返し作業が多いのですがもくもく手作業だなんてやってらんない。。。
でもあんまりVBAも書きたくない🥺

という訳で、できるだけ関数を活用しつつ久々にVBA書いて(いえ、検索してコピーして)みたので、メモとして残しておきます🙋‍♀️

使用する元データは、「システムからダウンロードして来たデータを、Power Queryでこねこねしたもの」です。

いつぞやのnoteのときに使用したPower Queryでデータ整形したファイルがありましたので、こちらを使用します。

「年月」のシートを作成して、そのシートには「年月」のデータを「社員コード」の昇順にしたデータを格納し、シートの名前をファイル名にして保存する
というものを作りたいと思います！

■Excel関数

① 原本シートの準備

まず、シートコピーする原本ファイルを作成します。

ちゃんとデータが正しく表示されるか確認するために、シート名は「202101」としておきます。

数式に入っているシート名を、関数で持って来ます。
こちらの記事を参考にさせていただきました🙏✨

"202101" の部分を、記事からコピペ🎵して、シート名を「★原本」に変更します。
（「★原本」というデータはないので、エラーになっててOK🙆‍♀️）

RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

② シート名の一覧の準備

「★シート名一覧」シートを作成して、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さんに消されてしまうかわかんないし、あんまり多用はしたくはないのですが、人に渡さずに自分だけで使う分にはありかな🎵
様子を見て、活用して行きたいと思います～✊🔥

事務員が少しの背伸びでできる効率化を目指す🌈✨ 自分の好きなものを、楽しく発信していきたいです！いただいたサポートは学習費にあてさせていただきます🥰