基幹システムから出力されたCSVファイルをつかって集計をしたり照合をしたり、顧客の店舗コードをつけたりして請求書をつくったりする事務処理でExcelVBAをつかっています。ExcelVBAの書籍を読んでも私のようなやり方をしているのは見たことないので、上級者がこのやり方をみて、どう思うのか感想をおきかせください。
<基幹システムDL:CSVファイルを「■貼付」シートに貼り付けた:データはサンプルです>
このシートを参照する「※抽出」シートをつくります。このシートには1行目、3行目に関数をいれてあり「■貼付」シートや他のシートからの参照や計算が可能になっています。A1には、「=MATCH(A19,■貼付!$A$19:$E$19,0)」、A3には「=@INDEX(■貼付!$A:$E,ROW($A3),※抽出!A$1)」が入力してあります。
そしてVBAは下記のとおりです。
Sub ※抽出作成()
Dim 最終列 As Long '変換後のシートの最終列
Dim 最終行 As Long '変換元・変換先の最終行
Dim シート最終行 As Long '変換元・変換先の最終行
シート最終行 = 1048576
Dim シート最終列 As Long '変換元・変換先の最終行
シート最終列 = 16384
Dim 変換元シート As String '変換元シート名
Dim 変換先シート As String '変換後シート名
Dim データ始行 As Long 'データが始まっている行
Dim タイトル行 As Long 'データ見出しの行
Dim 計算式行 As Long '計算式が入っている行
Dim 最下列 As Long '元シートのデータがいちばん下まで入っている列
Dim 最左列 As Long '変換先シートのデータの最も左列
'定数セット---------------
変換元シート = "■貼付"
最下列 = 1 '元シートのデータがいちばん下まで入っている列
変換先シート = "※抽出"
計算式行 = 3
タイトル行 = 19
データ始行 = 20
最左列 = 1 '変換先シートの計算する始めの列/これより右は計算しない
'-------------------
'①既に張り付いている値を消去
Sheets(変換先シート).Select 'シート名を指定
Range(Cells(データ始行, 最左列), Cells(シート最終行, シート最終列)).Select ' 計算式はりつけ
Selection.ClearContents
'②元シートの最終行取得のためにデータが最後まで入っている列を選んで
'変数に入れる
Sheets(変換元シート).Select 'このシート名のデータを変換する
Cells(Rows.Count, 最下列).End(xlUp).Select
最終行 = ActiveCell.Row
'②-2最終列取得のためタイトル行の最後を選んで変数に入れる
Sheets(変換先シート).Select '変換先のシート名
Cells(タイトル行, Columns.Count).End(xlToLeft).Select '
最終列 = ActiveCell.Column
'③計算式をコピー
Sheets(変換先シート).Select 'シート名を指定
Range(Cells(計算式行, 最左列), Cells(計算式行, 最終列)).Select ' 計算式をコピー
Selection.Copy
'④計算式を計算式貼付 最終行まで
Range(Cells(データ始行, 最左列), Cells(最終行, 最終列)).Select ' 計算式はりつけ
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
'⑤値貼付
Range(Cells(データ始行, 最左列), Cells(最終行, 最終列)).Select ' 計算式をコピー値貼付
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets(変換先シート).Range("A1").Select 'シート名を指定
Application.Goto Reference:=Range("A1"), scroll:=True
'⑥書式貼付 計算式の行の書式をペースト データ行の書式を統一する
Sheets(変換先シート).Select 'シート名を指定
Range(Cells(計算式行, 最左列), Cells(計算式行, 最終列)).Select ' 計算式をコピー 3行目に計算式
Selection.Copy
Range(Cells(データ始行, 最左列), Cells(最終行, 最終列)).Select ' 計算式をコピー書式貼付
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets(変換先シート).Range("A1").Select 'シート名を指定
End With
こういったシート変換の途中でピボットテーブルを挟んで重複を削除したり、集計したりして、最終的な請求書の形に変換していきます。中間的な形態はすべてシートに残しておき、視覚的にわかりやすくしています。
ピボットテーブルを「元シート」にして、上記VBAをつかって変換して別シートにしたりすることもやります。途中にピボットテーブルを更新するステートメントをいれておけば整合性は簡単にとれます。
最終アウトプット用のシートを別ファイルとして保存するVBAや、CSVデータを「■貼付」シートに貼り付けるVBAを組み合わせれば、ほとんどの事務処理は、このやり方でできてしまいます。
ループ処理はつかっていませんが、計算式をコピーして、貼り付けることで実質的にループ処理をしています。
繰り返しになりますが、ExcelVBAの書籍を読んでも私のようなやり方をしているのは見たことないので、どんなものか、感想をお聞かせください。
回答1件
あなたの回答
tips
プレビュー