[[20210911081315]]　『シートの連動について（VBA)』（カフェオレ）　ページの最後に飛ぶ

『シートの連動について（VBA)』（カフェオレ）

初めまして。マクロの勉強を始めたばかりで、同じような事例を探して作り変えてみましたが、一応正しく動いたのですが、範囲の指定方法等ご教授頂きたく、お願いいたします。

「通常」シート、「8月」シート、「まとめ」シートの3シートを連動させて、行挿入を実施したいです。

＜通常シート＞

    A        B          C
 1  No.	     商品	個数
 2  Aまとめ		80
 3  A-1	     りんご     10
 4  A-2	     みかん	30
 5  A-3	     キウイ	40
 6  Bまとめ		90
 7  B-1	     りんご	30
 8  B-2	     みかん	30
 9  B-3	     キウイ	30

＜8月シート＞

    A        B          C
 1  No.	     商品	個数
 2  Aまとめ		60
 3  A-1	     りんご     10
 4  A-2	     みかん	20
 5  A-3	     キウイ	30
 6  Bまとめ		60
 7  B-1	     りんご	10
 8  B-2	     みかん	20
 9  B-3	     キウイ	30

＜まとめシート＞

    A        B          C
 1  No.	     商品	個数
 2  Aまとめ		20
 3  A-1	     りんご      0
 4  A-2	     みかん	10
 5  A-3	     キウイ	10
 6  Bまとめ		30
 7  B-1	     りんご	20
 8  B-2	     みかん	10
 9  B-3	     キウイ	 0

Ａ列(No.)、B列(商品)は3シートすべて同じ表示としたい為、「通常」シートに内容を書き込んだものを「8月」「まとめ」シートに下記の式で表示させています。
=IF(INDIRECT("通常!R"&ROW()&"C"&COLUMN(),0)="","",(INDIRECT("通常!R"&ROW()&"C"&COLUMN(),0)))

Ｃ列の個数は、下記のように入力方法がシートによって異なります。
★「通常」シート　→ 手入力で個数を入れている。但し、2行目、6行目は3-5行目、7-9行目の足し算。
★「8月」シート　→ 別シート(「8月の個数」シート）のA列に果物、B列に個数が書いてあり、そこからVlookupで個数を拾っている。但し、2行目、6行目は3-5行目、7-9行目の足し算。
8月シートのC3セルの数式例=IFERROR(VLOOKUP(B3,'8月の個数'!A:B,2,FALSE),"")
★「まとめ」シート　→ 「通常」シートと「8月」シートの引き算を入れている。但し、2行目、6行目は3-5行目、7-9行目の足し算。
まとめシートのC3セルの数式例 =IFERROR(通常!C3-'8月'!C3,通常!C3)

上記の条件のもと、「通常」シートに行が挿入された際も、他の2シートにも連動して挿入して、かつ、数式もずれずにコピーしたいです。
ちなみに、行挿入時は、4行セットで実施したいです。（〇まとめ、〇-1、〇-2、〇-3）。
但し、通常シートの文字や個数は手入力とする。
また、各シートに条件付き書式をC列にいれて閾値以上は色を付けるようにしている。

Sub 連動1行ずつ()

    Dim r As Integer

    r = ActiveCell.Row
    Rows(r & ":" & r + 3).Insert

    Worksheets(Array("8月", "まとめ")).Select
    Rows(r).Select
    Selection.Offset(-4, 0).Range("A1:AI4").Copy
    Selection.Insert

    Worksheets("まとめ").Select
    Application.CutCopyMode = False

End Sub

上記コードで、正しく動きますが、例えば「通常シート」は基本手作業で入力しますが、可能ならC列の〇まとめ行のTotal式は、行を挿入しても自動で上の式からコピーして入れておきたいのですが、うまく組み込めなかったのでご教授頂けますと幸いです。

また、

 ＞ Rows(r & ":" & r + 3).Insert
 ＞ Selection.Offset(-4, 0).Range("A1:AI4").Copy

等の4行セットで挿入や式をコピーの指定の方法は、もっといいやり方があれば、教えて下さい。
実際は、D列以降にも数式を色々入れたいので、出来るだけ重くならずにスマートに動かしたいです。
関数部分も含めて、何か修正した方がよい箇所等ありましたら、今後の勉強の参考に教えて下さい<m(__)m>

＜使用 Excel：Excel2016、使用 OS：Windows10 ＞

 おはようございます　＾＾
ｗ、複雑そうですね。。。正常に動いているならそれが一番＾＾；ｖ
わたし、数式は新米レベルで回答では御座いませんが。テーブルにして
おけば数式はずれない！というような、お話は聞いた記憶が御座います。
一度、ダメもとで、お調べになっては？(￣▽￣)
他にも【8月の個数】というシートが存在するのでせうかね。
私でしたら、いっそ、読込みから、作表、個数算出計算、書き出し
まで、オールマクロで[数式は利用した方が便利な場合はちょい利用で＾＾；]
済ましてしまうと、思います。外していましたらお許しを。
また、余計なお世話でしたら、無視して下さいませ。m(_ _)m
（隠居Ｚ） 2021/09/11(土) 11:01

隠居Z様
ありがとうございます。
テーブルですか。調べてみます。
マクロは勉強し始めたばかりで、応用がきかない状態です、、
これから頑張りたいと思います。

（カフェオレ） 2021/09/11(土) 13:57

回答ではありません。提案です。

にこんな、入力シートを用意し
入力シートと、他のシート（通常、8月、まとめ）を連動させてはどうですか。

    A           B        C       D       E
 1  No         商品     通常    8月    まとめ
 2  Aまとめ              80     60       20
 3  A-1      りんご      10     10       0
 4  A-2      みかん      30      20      10
 5  A-3	     キウイ      40      30      10
 6  Bまとめ              90      60      30
 7  B-1	     りんご      30      10      20
 8  B-2	     みかん      30      20      10
 9  B-3	     キウイ      30      30      0

（マナ） 2021/09/11(土) 14:12

 こんにちは　＾＾
済みません。済みません。テーブルにしてみましたが
だめでした。←私の考えが浅はかでした。みたいです＾＾；
m(_ _)m
何やら、在庫の管理をしておられるようにお見受けするのですが
挿入するのは、何のためなのでしょう。
ちなみに、こちらではエラーで処理できませんでした。
動いているのですよね。↑のコードで。←私の操作ミスかもm(__)m
8月のシートをコピーしてまとめしーとに貼り付けるのでしょうか
それにしては、貼り付けるコードが見当たらないような気がするのですが
気のせいでしょうか。
どうなれば、正解なのかを教えていただけると、お手伝い出来るかも
しれません。←　多分。。。　＾＾；。。。m(__)mm(__)mm(__)m
（隠居Ｚ） 2021/09/11(土) 14:28

マナ様
ありがとうございます。
そのような発想の転換もありますね。
色々検討してみます！

隠居Z様
試して頂き、ありがとうございます。
目的は在庫が増えるたびに、リストにどんどん追加していきたい為です。
ちなみに行の挿入は、一番下に追加していくイメージです。(途中に挿入しても動きますが)

あれ？動かないですか？
挿入したい行(例えば例題ですと一番下に4行挿入したいとして)通常シートの10行目を
行選択した状態でマクロを動かすと他の2シートにも行が4行増えました。
(マクロ稼働後に通常シートには手入力でNo.、商品、個数を入力する事で、8月シート、まとめシートにも結果がかえります)
式のコピー貼り付けは自動で8月、まとめシート両選択して、いっぺんにやっています。
selection.insert する時に、その前にコピーしてきたものを4行まとめて貼りつけていると思います←初心者なもので、その理解で合っているか定かではありませんが、、
（カフェオレ） 2021/09/11(土) 15:08

 こんにちは　＾＾
はい、大変失礼致しました。動きました。
で、素朴な疑問が、追加した情報分の8月の個数は
どうしておられるのでしょう。
また、実際は、ＡＩ列まで情報が有るのでしょうか。←これも数式？？
でせうか＾＾；ｗｗｗ
m(_ _)m
（隠居Ｚ） 2021/09/11(土) 15:54

隠居Z様
動いて良かったです。
8月シートの追加した個数は、通常シートのB列にりんご、みかん、キウイと入力すると、8月シートの個数に数字が入ってきます。
(別シート(8月の個数シート)にVlookupで引っ張る用のデータを入力してあって、そこから紐づけてます)

例題なので色々簡素化してしまいましたが、実際のイメージはC列以降は各シート計算式を入れているので、それぞれのシートの4行セットで上から式をコピーしたいのです。
AI列も適当に入れてしまいましたが、実際は数十列あるので、なるべく素速く動くマクロで書きたいと思っています。
ですが、マクロ初心者のため、ネットで同じようなものを見つけては組み合わせて…という感じなので、おかしい書き方やもっとこうした考え方をするといいと言ったご意見頂きたいな…と思い、投稿させて頂きました。
（カフェオレ） 2021/09/11(土) 16:14

 こんにちわ　＾＾
私も、こちらでは、生徒さんなので、あまり詳しくありませんので、
アドバイスは他の方の回答も引き続きお待ちくださいませ。そのうえで
気が付いた点だけ。
１．私が誤作動させた要因かもしれませんが、うっかりアクティブシートが
　　変わっていますと、とんでもない結果に。。。今回の様に複数シートに
　　跨る、処理をする場合は明示的にシートを指定すればリスクを回避でき
　　ると思います。また、[二行目のセルを指定すればエラーに＾＾；]
　　のエラー回避処理等が必要かと。
２．元の情報に小計[Aまとめ]とかは入れずに処理し、1行、1情報[複数列可]
　　のようなデーターベースライクにして、それを基に、見やすい帳票はそれ
　　を基準に後から作成すると思います。←エクセルの様々な便利機能が使えて
　　仕事が楽になります。＾＾ｖ
えらそぉな事を申し上げましたが。ちょい参考程度にお止めをm(_ _)m
でわ、頑張ってくださいね。。。m(_ _)m
（隠居Ｚ） 2021/09/11(土) 17:01

隠居Z様
ありがとうございます。
そうですね、このままですとうっかり意図しない行でマクロを実行した際に、
上書きしてしまうと大変ですね。私も感じておりました。。
エラー回避処理ですね。検討してみます。

小計行…そうですよね。。今後の使い方含めて、効率の良い方法を検討してみます。
お忙しい中、ありがとうございました^ ^
（カフェオレ） 2021/09/11(土) 17:48

 こんばんわ。。。＾＾
もう、ご覧になっていないかもですが、言い出しべ！なので
そのぉ～私なら、こんな感じでという物ですが。かなり
いい加減な冗長なコードですが、ご考察時の何かの足しにでも
なれば幸甚です。m(_ _)m
シート名　　通常　　数式なし

    |[A]    |[B]   |[C] 
 [1]|No.    |商品  |個数
 [2]|Aまとめ|      |  80
 [3]|A-1    |りんご|  10
 [4]|A-2    |みかん|  30
 [5]|A-3    |キウイ|  40
 [6]|Bまとめ|      |  90
 [7]|B-1    |りんご|  30
 [8]|B-2    |みかん|  30
 [9]|B-3    |キウイ|  30
シート名　　8月の個数　　数式なし
    |[A]      |[B] 
 [1]|商品     |個数
 [2]|A-1りんご|  10
 [3]|A-2みかん|  20
 [4]|A-3キウイ|  30
 [5]|B-1りんご|  10
 [6]|B-2みかん|  20
 [7]|B-3キウイ|  30
シート名　8月　と　まとめ　は　空のシートにして
新規ブックに↑の各シートを用意して、お試しを＾＾；
新規ぶっくですよ(*^^*)、間違ってもご使用中の物はいけませんですよ。
Option Explicit
Sub OneInstanceMain()
    Dim i             As Long
    Dim lR            As Long
    Dim eflg          As Boolean
    Dim r             As Range
    Dim ad8           As String
    Dim uAry()        As Variant
    Dim k8Ary()       As Variant
    Dim ws1           As Worksheet
    Dim wS2           As Worksheet
    Dim wS3           As Worksheet
    Dim wS4           As Worksheet
    Dim t             As Double
    t = Timer
    Set ws1 = Worksheets("通常")
    Set wS2 = Worksheets("8月")
    Set wS3 = Worksheets("まとめ")
    Set wS4 = Worksheets("8月の個数")
    ReDim uAry(1 To 4, 1 To 3)
    ReDim k8Ary(1 To 3, 1 To 2)
    uAry(1, 1) = "Xまとめ": uAry(1, 2) = "": uAry(1, 3) = 100
    uAry(2, 1) = "X-1": uAry(2, 2) = "かき": uAry(2, 3) = 5
    uAry(3, 1) = "X-2": uAry(3, 2) = "もも": uAry(3, 3) = 75
    uAry(4, 1) = "X-3": uAry(4, 2) = "くり": uAry(4, 3) = 20
    k8Ary(1, 1) = "X-1かき": k8Ary(1, 2) = 3
    k8Ary(2, 1) = "X-2もも": k8Ary(2, 2) = 50
    k8Ary(3, 1) = "X-3くり": k8Ary(3, 2) = 10
    With ws1
        lR = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
        For i = 1 To lR
            If uAry(1, 1) = .Cells(i, 1) Then
                eflg = True
                Exit For
            End If
        Next
        If Not eflg Then
            .Cells(lR, 1).Resize(UBound(uAry, 1), UBound(uAry, 2)) = uAry
        End If
        eflg = False
    End With
    With wS4
        lR = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
        For i = 1 To lR
            If k8Ary(1, 1) = .Cells(i, 1) Then
                eflg = True
                Exit For
            End If
        Next
        If Not eflg Then
            .Cells(lR, 1).Resize(UBound(k8Ary, 1), UBound(k8Ary, 2)) = k8Ary
        End If
        ad8 = .Cells(1).CurrentRegion.Address
    End With
    With wS2 '8
        .UsedRange.Clear
        ws1.Cells(1).CurrentRegion.Copy .Cells(1)
        Set r = .Cells(1).CurrentRegion
        r.Offset(1, 2).Resize(r.Rows.Count - 1, 1).Formula = "=VLOOKUP(A2&B2,'8月の個数'!" & ad8 & ",2,FALSE)"
        For i = 2 To r.Rows.Count Step 4
            .Cells(i, 3).Formula = "=Sum(r[1]c:r[3]c)"
        Next
    End With
    With wS3 'まとめ
        .UsedRange.Clear
        ws1.Cells(1).CurrentRegion.Copy .Cells(1)
        Set r = .Cells(1).CurrentRegion
        r.Offset(1, 2).Resize(r.Rows.Count - 1, 1).Formula = "=IFERROR(通常!C2-'8月'!C2,通常!C2)"
        For i = 2 To r.Rows.Count Step 4
            .Cells(i, 3).Formula = "=Sum(r[1]c:r[3]c)"
        Next
    End With
    Erase k8Ary, uAry
    MsgBox "終了　" & Format(Int(Timer - t) / 24 / 60 / 60, "hh : mm : ss") & _
                      Format((Timer - t) - Int(Timer - t), ".000") & " 秒"
End Sub
（隠居Ｚ） 2021/09/11(土) 19:52

隠居Z様
貴重なご投稿、本当にありがとうございます。
やはり、配列を使用するといいのですね…。まだそこまで勉強出来ていませんが、
表示して頂いたコードを参考にして、ステップインで理解できるようにしたいと思います。
決まったフォームに囚われることなく、プログラムを組む時には発想の転換が大切なのだなぁ…と学びました。。
ほんと、ありがとうございます。
今後色々と引き出しを増やしていけるように、頑張りたいと思います！

掛かった時間を表示しているマクロをたまに見かけますが、このようにやるのですね(^^)

（カフェオレ） 2021/09/11(土) 20:31

 恐縮で御座います。m(__)m
配列部分は、別途、入力用シート、若しくは、ユーザーフォーム等
を使用して、新規増加分とそれに対応する処理月度の支出部分を入力
した情報の受け皿を想定して、配列に手入力部分を格納してみました。
確かに処理速度を上げる、一方法としても有効な時も御座いますので、
習得しておいて、損はないかと思います。
とても、楽しく勉強させて戴きました。ありがとうございました。
でわ。。。m(_ _)m
（隠居Ｚ） 2021/09/11(土) 21:39

コメント返信：

[ 一覧(最新更新順) ]

[[20210911081315]] 『シートの連動について（VBA)』（カフェオレ） ページの最後に飛ぶ

[[20210911081315]]　『シートの連動について（VBA)』（カフェオレ）　ページの最後に飛ぶ