エクセル2013を使ってPTAの会計処理をしています。
予算書、出納帳、決算書、特に出納帳の科目を条件にしてSUMIF関数を使って決算に自動記入出来ました。
科目別の明細はオートフィルターを使ってコピペしていたのですが、科目数も多くなり(40を超えて)行数も多くなり(200行超えてます)、自動で出来ないかと思い、過去ログ等を頼りにVBAを書いてみたのですが、うまく動作しません。
出納帳の1行目に見出しを入れて、2行目から日々の入出金、最後に収入合計、支出合計、残額の行を設けてあります。
A B C D E F G H I
1 月日 分類 小分類 No. 摘要 収入 支出 残高 科目
2 4/1 本部 運営 1 前年度 100,000 0 100,000 前年度繰越金
3 4/2 委員会 事業 2 会議 0 900 99,100 会議費
合計 ***,*** ***,*** ***、***
となっています(画面ではズレているでしょう)
このI列の科目毎に明細表を作りたいのです。
ここの過去ログで下記のようなVBAを見つけ、やってみました。
教えていた方の話では、
K列に「抽出条件の見出しと最初の科目名」
M列に「抽出条件の見出しと(抽出したい?)全科目名」
N列に「科目毎のシートの名称」
を記入して、と有った(実際の返答を列数を増やしました)ので、そのようにしました。
Sub FilterDataCopy()
Dim MyRow As Long 'G列の行を入れる変数
Application.ScreenUpdating = False
MyRow = 2
'まず前日の抽出データを全てクリア
Do Until Sheets("出納帳").Cells(MyRow, "M") = ""
Sheets(Sheets("出納帳").Cells(MyRow, "N").Text).Select
Cells.Select
Selection.ClearContents
MyRow = MyRow + 1
Loop
Sheets("出納帳").Activate
MyRow = 2
'抽出してコピー
Do Until Cells(MyRow, "M") = ""
Range("K2") = Cells(MyRow, "M")
Range("A2").CurrentRegion.AdvancedFilter xlFilterCopy, Range("K1:K2"), Sheets(Cells(MyRow, "N").Text).Range("A2")
MyRow = MyRow + 1
Loop
Application.ScreenUpdating = True
End Sub
これで動かしたところ、各タブに1行目の見出しは書き出される(タイトルを入れる都合でA2からにしています)のですが、その下の実際の入出金が記載されないのです。
どのように修正したら良いのか?お教えいただけますでしょうか?
修正ではなく、新規でも構いません。
本当は、各科目の最終行に合計を入れたいのですが、それは可能ならで構いません。(^^;)
それに印刷することを考えると、行数が少なく終わってしまうモノも有りますので、少ないシート数に出来たらうれしいですが、そのくらいはコピペ出来ると思いますので(^^;)
お知恵をいただけないでしょうか?
No.1ベストアンサー
- 回答日時:
質問に対する直接の回答でなくて失礼します。
1、各科目ごとに40枚のシートが必要ですか?
2、最終行に合計を入れると下の方で位置もバラバラですよ。
私であれば
1、データの抽出シートを1枚
K2に科目をドロップダウンリストから選択したら
希望の科目の明細が下に表示する。
2、合計金額は上部分の固定した位置に表示させる。
参考までに、先日回答したサイトを紹介しておきます。
https://oshiete.goo.ne.jp/qa/9249567.html
仮に、別シートに
A B C D E F G H
1 科目 収入計 支出計
2 会議費
3
4
5 月日 分類 小分類 No. 摘要 収入 支出 残高
とB2に希望する科目(入力規則を設定して、ドロップダウンで選択)
シートモジュールを開いて
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$2" Then
Sheets("データ").Columns("A:I").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("B1:B2"), CopyToRange:=Range("A5:H53"), Unique:=False
End If
End Sub
B2セルを変更するだけで、ご希望の科目の明細が6行目以下に表示されます。
シートを移動して希望する科目を探すより
ドロップダウンリストで探す方が楽ですよ。
アドバイスありがとうございますm(__)m
1は表現が下手ですみません。
「各科目ごとに40枚のシート」ではなく、1科目は1枚です。
科目が40くらい有るので、1科目1シートで、Book全体で40シート位になってしまう、ということです。
2の「合計を上部で固定」ですが、帳面に記帳した際の見た目を重視してしまいました。
文字サイズ等を変更して、見やすいようにすればいけますね(^_^;)
ご教授いただいたコードを新規のシートのタブで右クリックして貼り付けました。
「データ」を「出納帳」に修正しました。
しかし、6行目以下に何も表示されないのですが・・・???
科目ごとにシートを作った方が良いと思ったのは、私ではない者(後任)が作業したときに、その方が印刷する際にコピペしやすいかなと思ったからなんです(^_^;)
表示されない点等、お分かりになりますでしょうか?
出来ました!!
今日再度詳細を確認しました。
問題なく抽出できた(マクロが動いた)試しに作った表と実際に使いたい表と見比べてみました。すると、抽出条件にする「科目」が「科 目」でした。(;´・ω・)
今後引き継いでもらう方のことも考えて、ファイルを用意したいと思います。
親身にお付き合いいただき、本当にありがとうございました。
No.8
- 回答日時:
>「マクロ実行中」とメッセージボックスが出ました!!
と云う事でしたら
Sheets("出納帳").Columns("A:I").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("B1:B2"), CopyToRange:=Range("A5:I5"), Unique:=False
は実行されていますが
B2セルに入力されている 科目に該当するデータが一件もないという事はないですよね。
もうひとつ試して
B2セルを空白にしたら全てが表示されると思うのですが
>ご教示いただいたページの様にやってみたところ、1行目の「月、分類・・・」が表示され
>るだけでその下は何も表示されませんでした。
フィルターオプションの操作を行っても希望のデータが抽出されないという事ですね。
まずは、ここをクリアする必要があるかも??
No.7
- 回答日時:
こちらでも同じようなエクセルファイルを作成し実行しましたが
出来ました。
ちなみに
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$2" Then
MsgBox "マクロ実行中"
Sheets("出納帳").Columns("A:I").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("B1:B2"), CopyToRange:=Range("A5:I5"), Unique:=False
End If
End Sub
と途中にMsgbox で表示する行を追加してみましたが
B2セルの内容を変更したら、 マクロ実行中 と 表示が出ますか?
No.6
- 回答日時:
今日は暇なのでお付き合いします。
現在のシートの状況を説明してもらえませんか
>1行目の「月、分類・・・」が表示されるだけでその下は何も表示されませんでした。
先の例では
1行目、2行目に抽出の条件です。
元データに月という項目があるのでしょうか。
先に紹介したコードはシートモジュールに記載します。
シート名を右クリックして、コードの表示をクリック
VBエディターが起動したら、そのシートのシートモジュールになります。
No.5
- 回答日時:
>フィルターを手動でやったら、出来たのですが・・・(;´・ω・)
だったら
マクロの記録
フィルターを手動でやったら
記録の終了
これで作成されたコードと現在のコードの違いを確認しましょう
If Target.Address = "$B$2" Then
の部分で、B2セルの内容が変更されたら
実行するという事を指定したいますが
No.4
- 回答日時:
横から失礼します。
後任に引き継ぐのであれば、あまり凝らない方が良いかと・・・。そこで、次のような手順ではいかがでしょうか?こうしておけば、手作業のコピペも楽だと思います。
①元シートをコピーして”明細”シートを作成します。以下、”明細”シートでの作業です。
②科目で並べ替えます。
③科目をキーに、小計を設定します。「グループ毎に改ページを挿入」にすれば、印刷時に改ページもできます。
貴重なアドバイスありがとうございます。
そうですね、今後科目名が変わったり増えたり減ったりすることも有りえますので、マクロだと敷居が高いかもしれません(^^;)
私はこれで楽させてもらいます(爆)が、引き継ぐのは別にした方が良さそうです。
ありがとうございました。m(__)m
No.3
- 回答日時:
NO1です。
>しかし、6行目以下に何も表示されないのですが・・・???
すみませんでした。
CopyToRange:=Range("A5:H53"), Unique:=False
は
CopyToRange:=Range("A5:H5"), Unique:=False
ですね。
後はB2セルを変更することでVBAが実行されると思います。
フィルターオプションの機能ですが
http://www.eurus.dti.ne.jp/~yoneyama/Excel/filte …
の辺りを参考に一度、手動で抽出してみてください。
No.2
- 回答日時:
ピボットテーブル試してみましたか?
http://allabout.co.jp/gm/gc/297727/
出納長のシートにおいて
[Ctrl]+[F3]名前の定義
名前 : PT範囲_出納
参照範囲 : =$A$1:INDEX($I:$I,COUNTA($A:$A))
ピボットテーブルの範囲を
=PT範囲_出納
とすることでピボットテーブルの範囲が可変になり
ピボットテーブル更新時に計算してくれます。
とりあえず、科目を行フィールド
収入と支出をデータフィールド
「データ」となりますので列フィールドにドラッグ
とか 科目をページフィールドにドラッグする
とか 合計/支出 の金額のセルをダブルクリックしてみるなんてのも
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) Sheet2の日付をキーにオートフィルターで2023年1月のデータを抽出し、Sheet3へ書き出すた 2 2023/03/06 23:57
- Visual Basic(VBA) 【前回の続き続きです、ご教示ください】VBAの記述方法がわかりません。 2 2022/08/24 20:49
- Visual Basic(VBA) 3つのプロシージャをまとめたら実行時エラー発生で対応不能 6 2022/05/17 01:47
- Visual Basic(VBA) 別シートから年齢別の件数をカウントしたいの続き 5 2023/01/24 00:16
- Visual Basic(VBA) まとめシートから集計シートへA列のコードが一致したら1行コピーするマクロをネット上で見つけました。こ 1 2022/08/30 14:11
- Visual Basic(VBA) ファイル全てを .xlsm に変更したところ、プログラムが途中で落ちてしまっています 17 2022/12/07 12:03
- Excel(エクセル) 【困っています】VBA 追加処理の記述を教えてください。 1 2022/08/25 22:54
- Visual Basic(VBA) Sheet3から2つの条件でオートフィルターで抽出した個数をSheet2へ入力するマクロで、一つ目の 4 2023/01/12 23:40
- Visual Basic(VBA) 複数csvを横に追加していくマクロについて 2 2023/04/25 09:19
- Excel(エクセル) VBAで組み合わせ算出やCOUNTIFSの処理を高速化したいです。 4 2022/04/07 02:38
このQ&Aを見た人はこんなQ&Aも見ています
-
性格の違いは生まれた順番で決まる?長男長女・中間子・末っ子・一人っ子の性格の傾向
同じ環境で生まれ育っても、生まれ順で性格は違うものなのだろうか。家庭教育研究家の田宮由美さんに教えてもらった。
-
Excelの出納帳で、別シートに自動で振り分け
財務・会計・経理
-
(EXCEL)出納帳に科目別シートを作成したい
Excel(エクセル)
-
現金出納表の内容を別表の科目別欄に自動コピー出来ますか?
Word(ワード)
-
-
4
エクセル入力で項目別にシートに振り分ける方法を教えてください!
Excel(エクセル)
-
5
フィルタを使わずに抽出
Excel(エクセル)
-
6
エクセルで集計表から項目ごとに別シートへ表示する方法
財務・会計・経理
-
7
エクセルでシートの振り分けを自動的にする方法
Excel(エクセル)
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Excelの関数について教えてくだ...
-
エクセルファイルがファイルの...
-
一つのセルに(例えばA1)入力...
-
エクセル vbaについて教えてく...
-
Excel 複数列 A列B列C列一致 D...
-
【マクロ】名前を保存する際に...
-
Excelについて教えてください
-
エクセル マクロ チェックボックス
-
エクセルであるセルに数字を入...
-
【Excel】数字を3倍にし、なお...
-
エクセル共有したが、アクセス...
-
【マクロ】顧客番号にて一致さ...
-
Excel分析ツールでのポアソン回...
-
エクセルで年休を管理する方法...
-
エクセルのデーターが2か月前の...
-
LOOKUP関数を使えばいいのでし...
-
エクセル②
-
エクセルを使っていて2024/5/15...
-
Excelでセルの値が同じか...
-
エクセルでの作業計算方法について
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報
アドバイスありがとうございます。時間の都合と試行錯誤で遅くなりましたm(__)m
ピボットテーブル試してみました。「収入支出」が合計で表示されるのは良いのですが、他の「月日」や「分類」「摘要」等が1行で表示されません。
私の設定の仕方が悪いのかと色々やってみたのですが、やればやるほど見づらくなってしまいました(^^;)
さらなるアドバイスありがとうございます。時間の都合と試行錯誤で遅くなってしまいましたm(__)m
修正をしてやったのですが、何も表示されませんでした。
あ、教えていただいたコードの「データ」は実際に合わせて「出納帳」と修正しました。
そこで、別に簡単な表を作り、動かしてみたのですが、そちらではちゃんと表示されました。
表の違いは、行数が違うこととセルに記入された単語?そして空白セルの有り無しくらいなんですが・・・
フィルターを手動でやったら、出来たのですが・・・(;´・ω・)
再度ありがとうございます。
私が勘違いしていたようです。(^^;)
「手動でフィルターを掛けたら出来ました」というのは、単にその表にフィルターを
掛けたことで、ご教示いただいたページの様にやってみたところ、1行目の「月、分類・・・」が表示されるだけでその下は何も表示されませんでした。
行数が多いのか?(そんなことはないと思いますが)と思い、例題と同じ13行くらいを範囲指定してみましたが、1行目しか表示されません。
例題の表と見比べても、当然違いが判りません。あえて言えば、こちらには空白セルが有るくらいでしょうか。
なんか深みにハマってきました(;_;)
ありがとうございます。
「月、分類・・・」は質問に書いた元データ(出納帳というシート名)の
A B C D E F G H I
1 月日 分類 小分類 No. 摘要 収入 支出 残高 科目
この行のことです(^^;)
マクロを実行して、表示されるのは
A B C D E F G H I
1 科目
2 (リスト表示される科目)
3
4
5 月日 分類 小分類 No. 摘要 収入 支出 残高 科目
で、6行目からには何も表示されないんです。
はい、シートを右クリックの「コード表示」から書き込んでます。
わざわざやっていただきありがとうございます。
「マクロ実行中」とメッセージボックスが出ました!!
でも、表示は1行目だけです。
またありがとうございます。
試しに選択したモノはちゃんとデータのあるやつです(^^;)
そして、空白にすると、「全てのデータ」が表示されました。
ちなみに、列はそのまま、行は13行、データは不規則な表でやってみたらちゃんと表示されたのですが、データを今回の表からコピペしたらダメでした。
データ自体に問題が有るってことでしょうか?
科目の列には空白は無いです。
他の列には空白が有りますが。
出納帳シートの表の科目を一つコピーして貼り付けしても、今までと同じで1行目だけの表示でした。