エクセルの自動で複写

エクセル2019を使用しています。
A1からK17までの範囲を1ブロックと考え、L1セルに複写回数を入力し、18行目以降にブロックを回数分だけ自動で複写したいです。（終わりの行番号が17の倍数になる感じです）計算式の組み合わせでできればよいのですが、いい方法があれば教えていただけないでしょうか。
よろしくお願いいたします。

質問者からの補足コメント

• すみません、文章では分かりにくいかもしれないので、画像を添付します。
  L1セルに3と入力した場合、18行目から2ブロック追加表示されるようにしたいです。
  ブロックの中は他シートからの内容を表示させる式が入っており、
  基本的にはL1のセル以外触ることはありません。
  よろしくお願いいたします。

  
    補足日時：2021/01/20 10:05

• 分かりにくい説明で申し訳ございません、画像を追加しました。
  sheet1の3行目以降の数量分のみsheet2のフォームで表示させたいです。
  ただ、例えばsheet1の表示させたい内容がNo.200まであると
  sheet2に3400行も式を入れておかなければならず、重くなるのを避けたいため
  必要分だけの式が18行目以降に計算式が複写されればと思います。
  sheet2の1ブロック目の内容ですが
  A1、A2～A17、B2～B17はsheet1からの参照ではないです。
  空白の部分には何も入力していません。
  計算式ですが文字数オーバーなので次の投稿に記載します。
  難しそうなのでダメでしたらマクロでも構いません。
  長々となりましたが、よろしくお願いいたします。

  
    補足日時：2021/01/20 14:43

• 計算式ですが
  B1セルは
  {=IFERROR(INDEX(IOカード仕様設定!$A$3:$A$303,SMALL(IF(IOカード仕様設定!$A$3:$A$303<>"",ROW(IOカード仕様設定!$A$3:$A$303)-2),ROW(INDIRECT("B"&INT((ROW()-1)/17)+1)))),"")}
  D1セルは
  =IFERROR(IF(INDIRECT("E"&ROW())="","",VLOOKUP(INDIRECT("E"&ROW()),$M$1:$O$22,2,FALSE)),"")
  E1セルは
  =IFERROR(IF(INDIRECT("B"&ROW())="","",VLOOKUP(INDIRECT("B"&ROW()),IOカード仕様設定!$A$2:$E$303,3,FALSE)),"")

    補足日時：2021/01/20 14:46

• F1セルは
  =IFERROR(IF(INDIRECT("E"&ROW())="","",VLOOKUP(INDIRECT("E"&ROW()),$M$1:$O$22,3,FALSE)),"")
  G1セルは
  =IFERROR(IF(INDIRECT("B"&ROW())="","",VLOOKUP(INDIRECT("B"&ROW()),IOカード仕様設定!$A$2:$E$303,4,FALSE)),"")
  H1セルは
  =IFERROR(IF(INDIRECT("B"&ROW())="","",VLOOKUP(INDIRECT("B"&ROW()),IOカード仕様設定!$A$2:$E$303,2,FALSE)),"")

    補足日時：2021/01/20 14:47

• I1セルは
  =IFERROR(IF(INDIRECT("B"&ROW())="","",VLOOKUP(INDIRECT("B"&ROW()),IOカード仕様設定!$A$2:$E$303,5,FALSE)),"")
  尚、sheet2の本当の名称は「IOカード仕様設定」となっています。
  難しそうなのでダメならマクロでも構いません。
  長々となりましたが、よろしくお願いいたします。

    補足日時：2021/01/20 14:49

• 回答ありがとうございます。
  値のコピーと式のコピー両方で、式のコピーは相対参照となります。
  よろしくお願いいたします。

  No.2の回答に寄せられた補足コメントです。 補足日時：2021/01/20 14:58

• 回答ありがとうございます。
  教えていただきましたマクロで指定数のブロックを複写できました。
  あとコピーする前に、18行目以降を消すことは可能でしょうか？
  2回目以降の複写時にブロック数が前回より減ると前のブロックが残ってしまうので
  18行目以降を削除後に複写できれば幸いです。
  よろしくお願いいたします。

  No.5の回答に寄せられた補足コメントです。 補足日時：2021/01/21 09:57

• 回答ありがとうございます。
  おかげでうまくいきました。
  あと1点、教えていただきたいのですが
  今までL1セルの値が"1"以上でA~K列の18行目以降に複写していたのですが
  L1セルが"0"の時、A~K列の18行目以降を削除する事は可能でしょうか？
  よろしくお願いいたします。

    補足日時：2021/01/21 16:48

• 回答ありがとうございます。
  おかげでうまくいきました。
  あと1点、教えていただきたいのですが
  今までL1セルの値が"1"以上でA~K列の18行目以降に複写していたのですが
  L1セルが"0"の時、A~K列の18行目以降を削除する事は可能でしょうか？
  よろしくお願いいたします。

  No.6の回答に寄せられた補足コメントです。 補足日時：2021/01/24 10:34

No.6 ベストアンサー 回答者： chiune 回答日時： 2021/01/21 11:19

No.5のソースからの変更点

☆の行を入れ替えてます（速度上げるため）。
★の行を挿入。
これでOKかな

Sub rcopy()
Const rgRow As Integer = 17
Const rgCol As Integer = 11
Dim cpCnt As Integer
Dim cpCntX As Integer

cpCntX = Val(Cells(1, 12).Value)
If cpCntX < 1 Then End

Range(Cells(rgRow + 1, 1), Cells(50000, rgCol)).Delete Shift:=xlUp '★

Range(Cells(1, 1), Cells(rgRow, rgCol)).Copy '☆
For cpCnt = 1 To cpCntX　'☆
Cells(rgRow * cpCnt + 1, 1).Select
ActiveSheet.Paste
Next

End Sub

この回答へのお礼

色々教えていただき、ありがとうございました。

お礼日時：2021/01/26 08:51

No.5 回答者： chiune 回答日時： 2021/01/20 15:51

とりあえず、マクロで。

Sub rcopy()
Const rgRow As Integer = 17
Const rgCol As Integer = 11
Dim cpCnt As Integer
Dim cpCntX As Integer

cpCntX = Val(Cells(1, 12).Value)
If cpCntX < 1 Then End

For cpCnt = 1 To cpCntX
Range(Cells(1, 1), Cells(rgRow, rgCol)).Copy
Cells(rgRow * cpCnt + 1, 1).Select
ActiveSheet.Paste
Next

End Sub

No.4 回答者： fujillin 回答日時： 2021/01/20 15:49

No3です。

え～～っと・・・

１行目から１7行目までが、1行目のセルのフィルコピーで成立していると仮定できるのなら（＝コピペで成立する）、No3の最後の方法で全体的に可能と思います。
そうでない場合（＝1行目から17行目までが同じ式でない）場合でも、できないことはありませんが、以下に示すよりもさらに面倒なことになるので、マクロで対応してしまった方が簡単だと思われます。


ひとまず、仮定が成り立つものとして…
１）まず、18行目に現在の式をコピペします。
　　その式は「=○〇〇」となっているものとします。

２）L1セルの値は、「表示／非表示」の制御をしているだけなので、その式の表示／非表示の制御を追加するように編集します。
　=IF(ROW()>($L$1+1)*17,"",○〇〇)
　（○〇〇の部分は上記コピーした時の式です）

３）どうやら、各列で異なる式が入れてあるようなので、各列を順に編集する必要がありそうです。
　元が配列数式の場合は、編集してから再度配列数式として確定します。

４）もしも、式のコピーをするのではなく、値のコピーをしたい列がある場合は、その列のセルの式の編集では、元の式ではなく
　=IF((ROW()>($L$1+1)*17),"",OFFSET($A$1,MOD(ROW()-1,17),COLUMN()-1))
に置き換えます。
（どの列であっても、この式で対応できると思います）

５）18行目（A～k列）の各セルの編集ができたら、全体を下方にフィルコピーします。

以上の手順で、ご質問の内容を実現できるものと思います。


※ 元が想定以上に複雑なようなので、上記の手順も多少複雑になってしまっていますが、もしも、わからなかったり設定するのが面倒であるようなら、マクロで対応してしまうのが宜しいのかもしれません。

この回答へのお礼

回答ありがとうございます。
計算式だと、必要数量分セルの確保が必要なのが分かりました。
マクロだと行や列の追加や削除を行うと追従されないので
計算式でいければと思っていたのですが、かなり複雑そうなので
マクロにすることにしました。
ここまで親切に教えてくださったのに申し訳ございません。
ありがとうございました。

お礼日時：2021/01/21 09:50

No.3 回答者： fujillin 回答日時： 2021/01/20 11:33

No1です

せっかく画像を添付いただきましたが、あまり識別ができません。
目を凝らしてみると、C～K列に関してはご説明とは異なっているように感じられ、ご質問文からは単純に考えていましたが、どうやら違うようなので、全体の規則性がまったくわからなくなりました。

ですので、A、B列に関してのみの回答になります。
C列以降はまったくわかりませんけれど、共通部分はありそうですが、異なった式になるのではないかと想像します。

ということで、A18セルに
=IF((ROW()>($L$1+1)*17)+(COLUMN()>2),"",OFFSET($A$1,MOD(ROW()-1,17),COLUMN()-1))
の式を入れて、B18セルにコピー
ついで、A18:B18セルを下方に必要十分な範囲までフィルコピー
（L1がどのくらいの回数までの可能性があるのかによります）

以上で、A、B列に関しては可能と思います。


まったくの山勘での想像では、C18セル以降の式は
　=IF(ROW()>($L$1+1)*17,"",C1セルの数式をC18セルに換算した式)
のような形式になりそうな気はしますけれど…
（A、B列もこの形式で良いのかもしれませんが、式の実態が不明なのでわかりません）

No.2 回答者： chiune 回答日時： 2021/01/20 10:41

関数だけでは（実際上）無理だと思います。

マクロ使って良いなら比較的簡単ですけど。

あと18行目以降は値のコピーで良いのでしょうか。式のコピーでしょうか。式のコピーだとしたら絶対参照ですか／相対参照ですか。

No.1 回答者： fujillin 回答日時： 2021/01/19 20:41

こんばんは

表示するだけなら関数式で可能ですけれど、そのセルには式が入っているので、そのあとで何かを入力すると式はなくなってしまいます。

どのように使うのかがわかりませんが、そうなると複写する機能もなくなっちゃうけれど、それでも良いのでしょうか？