Q&A
「これをボタンクリック毎に下に行を追加していきたいのです。」ボタンとは何でしょうか？
前提・実現したいこと
VBAで、一番下の行を複製してその下に貼り付けたいのですが、
先頭のセルには日付、一番後ろのセルには数式が入っています。
日付は月毎、数式はそのままコピーしたい場合、
どのように記載したらよいのでしょうか？
マクロの記録だと以下のようになりますが、
これをボタンクリック毎に下に行を追加していきたいのです。
よろしくお願いいたします。
該当のソースコード
Sub Macro9()
'
' Macro9 Macro
'
'
Range("B5:Q7").Select
Selection.AutoFill Destination:=Range("B5:Q10"), Type:=xlFillDefault
Range("B5:Q10").Select
Range("B5:B7").Select
Selection.AutoFill Destination:=Range("B5:B10"), Type:=xlFillMonths
Range("B5:B10").Select
Range("P5:P7").Select
Selection.AutoFill Destination:=Range("P5:P10"), Type:=xlFillCopy
Range("P5:P10").Select
End Sub
試したこと
ここに問題に対して試したことを記載してください。
補足情報（FW/ツールのバージョンなど）
ここにより詳細な情報を記載してください。
回答3件
0
ExcelVBA
1Sub test() 2 With Cells(Rows.Count, "B").End(xlUp).Resize(3, 15) 3 .Copy .Offset(.Rows.Count) 4 With .Columns(1) 5 .AutoFill .Resize(.Rows.Count * 2), xlFillMonths 6 End With 7 End With 8End Sub
コピーの仕方というより、
コピー元、コピー先のセル範囲の取得の仕方
が、テーマですね？
今回の件の場合、
ResizeプロパティやOffsetプロパティを使って表現できるといいかもです。
参考URL>>
Excel(エクセル) VBA入門：セル範囲の指定方法
ちなみに、B列に数字が入っており、コピーしたらそこだけ数字が
一つ増えるようにしたい場合はどうしたらよいのでしょうか？
AutoFill .Resize(.Rows.Count * 2), xlFillMonths
のところを変えるのでしょうか？
ExcelVBA
1Option Explicit 2 3Sub test2() 4 Dim rngFrom As Range 'コピー元セル範囲 5 Dim rngTo As Range '貼付け先セル範囲 6 Dim ixRow As Long '使用しているセル範囲の行数 7 Dim i As Long 'オートフィルのタイプ 8 9 'セル範囲の取得 10 With ActiveSheet.UsedRange 11 ixRow = .Rows.Count 12 Set rngFrom = Intersect(.Cells, .Offset(ixRow - 3)) 13 Set rngTo = .Cells(ixRow + 1, 1).Resize(rngFrom.Rows.Count) 14 End With 15 16 'コピー 17 rngFrom.Copy rngTo 18 '値の型によりオートフィルのタイプを変える 19 If TypeName(rngFrom.Cells(1).Value) = "Date" Then 20 i = xlFillMonths 21 Else 22 i = xlFillSeries 23 End If 24 'オートフィル 25 With rngFrom.Columns(1) 26 .AutoFill Application.Range(.Cells, rngTo), i 27 End With 28End Sub 29
条件により処理が変わるなら、
IF文を使い条件分岐します。
参考サイト
プロパティ、メソッドの探り方 マクロ記録とＦ１のHelpを使う
↑を参考にメソッドにどういう引数を与えたらいいかヘルプで確認してください。
（ヘルプの意味が解らなかったらネットで単語を検索してみること）
1行1行意味の理解に努めていただけたら嬉しく思います。
う～ん。
意味的には、
B列の最後のデータが結合されているセル範囲の15列分
が、コピー元のセル範囲なので、
コードにそう書いておいたほうが後で読んだとき（半年後や1年後）にわかりやすいですね。
ただ、結合セルにoffsetプロパティ適用すると変な値を返すので、
冗舌な表現で書くしかなくなるのでコードがきれいでなくなるのが難点かな。
投稿2019/12/31 07:14
編集2020/01/01 09:08
総合スコア2163
mattuwanさん、ありがとうございます！
こういうやり方もあるのですね。
resizeのところを変えてアレンジも試してみたら成功しました。
ちなみに、B列に数字が入っており、コピーしたらそこだけ数字が一つ増えるようにしたい場合はどうしたらよいのでしょうか？
AutoFill .Resize(.Rows.Count * 2), xlFillMonths
のところを変えるのでしょうか？
そう思うなら、やってみれば？
試す前に質問してすみません。
やってみてうまくいかなかったらまた質問させてください。
0
ベストアンサー
追加の質問について．．．
ちなみに、結合していない1行のみをコピーする場合は、
最初のコードで「MaxRow + 3」を「MaxRow + １」にすればよいのでしょうか？
コピーする表に結合セルが１つも含まれていない場合は、「MaxRow + 1」で問題なく動くと思います。
同じ処理を他のシートでも行いたいのですが、
その場合はどのようにしたらよいのでしょうか？
例えばシート１にボタンを一つ設置して、
シート４，５，６で同じ処理がしたいです。
マクロを記載しているシートと異なるシートの操作をする場合、Rangeを指定する際に、シート名も指定しないといけません。
下記のコードの通り、ワークブックとシート名を指定しました。
繰り返しの記述を避けるために、withブロック変数を使ってます。
複数のシートで同じ処理をするということだったので、行追加の処理をメソッド（RowAdd）にしました。
以下がコードになります。ご確認ください。
VBA
1'// ボタンで実行するマクロ 2Sub macro1() 3 '// シート名を指定して、メソッドを呼ぶだけ 4 Call RowAdd("sheet1") 5 Call RowAdd("sheet4") 6 Call RowAdd("sheet5") 7 Call RowAdd("sheet6") 8End Sub 9 10'// 行追加するメソッド（引数は行追加するシート名） 11Sub RowAdd(SheetName) 12 thisworkbook.Sheets(SheetName).Select '// 行追加するシートを表示する 13 14 With thisworkbook.Sheets(SheetName).Cells(Rows.Count, "B").End(xlUp) 15 MaxRow = .Row + .MergeArea.Rows.Count - 1 '// B列の最終行を取得 16 End With 17 18 With thisworkbook.Sheets(SheetName) '// 行追加するシート名を指定 19 .Range("B" + CStr(MaxRow - 2) + ":Q" + CStr(MaxRow)).Select '// 初期データの3行を選択 20 Selection.AutoFill Destination:=.Range("B" + CStr(MaxRow - 2) + ":Q" + CStr(MaxRow + 3)), Type:=xlFillDefault '// とりあえず、表全体をコピー 21 22 .Range("B5:B7").Select '// 初期データの3行を選択（日付データのB列） 23 Selection.AutoFill Destination:=.Range("B5:B" + CStr(MaxRow + 3)), Type:=xlFillMonths '// 日付は月毎の設定で上書き 24 25 .Range("P5:P7").Select '// 初期データの3行を選択（数式データのP列） 26 Selection.AutoFill Destination:=.Range("P5:P" + CStr(MaxRow + 3)), Type:=xlFillCopy '// 数式は数式の設定で上書き（なくてもいい） 27 28 .Range("B" + CStr(MaxRow + 1) + ":Q" + CStr(MaxRow + 3)).Select '// 追加した3行を選択状態で終了（特に意味はない） 29 End With 30End Sub 31
不明点ありましたら、教えてください。
以上
投稿2019/12/30 20:53
総合スコア640
0
###回答
初期データのイメージが違ってるかもしれませんが、コードから推測してご要望の動作を作ってみました。
初期データと動作のイメージはこんな感じです。（画像参照）
マクロを見た感じ、表範囲はB列～Q列、B列は日付、P列は数式と解釈しました。
初期データはB5:Q7の３行と解釈しました。
以下、コード。不要なコードはコメントアウトしました。
VBA
1 MaxRow = Range("B5").End(xlDown).Row '// 最終行番号を取得 2 3 Range("B5:Q7").Select '// 初期データの3行を選択 4 Selection.AutoFill Destination:=Range("B5:Q" + CStr(MaxRow + 3)), Type:=xlFillDefault '// とりあえず、表全体をコピー 5 '//[削除] Range("B5:Q10").Select 6 Range("B5:B7").Select '// 初期データの3行を選択（日付データのB列） 7 Selection.AutoFill Destination:=Range("B5:B" + CStr(MaxRow + 3)), Type:=xlFillMonths '// 日付は月毎の設定で上書き 8 '//[削除] Range("B5:B10").Select 9 Range("P5:P7").Select '// 初期データの3行を選択（数式データのP列） 10 Selection.AutoFill Destination:=Range("P5:P" + CStr(MaxRow + 3)), Type:=xlFillCopy '// 数式は数式の設定で上書き（なくてもいい） 11 '//[削除] Range("P5:P10").Select 12 Range("B" + CStr(MaxRow + 1) + ":Q" + CStr(MaxRow + 3)).Select '// 追加した3行を選択状態で終了（特に意味はない）
###補足
xlFillDefault でB列～Q列までデフォルト設定のオートフィルで、数式も正しくコピーされるので、xlFillCopy はなくてもいいと思います。
月データはデフォルト設定でコピーできないので、xlFillMonths で上書きしてます。
以上
###追記
シート名の確認
ちなみにテラテイルでの画像のアップボタンは下記のアイコンです。
###追記 コード修正
VBA
1 thisworkbook.Sheets("Sheet3").Select 2 3 With Cells(Rows.Count, "B").End(xlUp) 4 MaxRow = .Row + .MergeArea.Rows.Count - 1 5 End With 6 7 Range("B" + CStr(MaxRow - 2) + ":Q" + CStr(MaxRow)).Select '// 初期データの3行を選択 8 Selection.AutoFill Destination:=Range("B" + CStr(MaxRow - 2) + ":Q" + CStr(MaxRow + 3)), Type:=xlFillDefault '// とりあえず、表全体をコピー 9 10 Range("B5:B7").Select '// 初期データの3行を選択（日付データのB列） 11 Selection.AutoFill Destination:=Range("B5:B" + CStr(MaxRow + 3)), Type:=xlFillMonths '// 日付は月毎の設定で上書き 12 13 Range("P5:P7").Select '// 初期データの3行を選択（数式データのP列） 14 Selection.AutoFill Destination:=Range("P5:P" + CStr(MaxRow + 3)), Type:=xlFillCopy '// 数式は数式の設定で上書き（なくてもいい） 15 16 17 Range("B" + CStr(MaxRow + 1) + ":Q" + CStr(MaxRow + 3)).Select '// 追加した3行を選択状態で終了（特に意味はない） 18
投稿2019/12/29 08:19
編集2019/12/29 13:39総合スコア640
KazuSakaさん、ありがとうございます！
画像はほとんど私の理想の通りです！
早速いただいたコードを貼り付けてみたのですが、
「実行時エラー 1004」
が出てしまいました。
貼り付け方がおかしかったのでしょうか？
どの行でエラーになってるか知りたいです。
マクロを１行ずつ実行する方法はご存じですか？
エディタでF8を押すたびに1行ずつ実行されます。（マウスカーソルはマクロの先頭にしといてください）
F8を推したら、3行目でエラーになりました
３行目は「Range("B5:Q7").Select」だから、マクロ記録で出力されたコードなので、動くはずですが。
マクロ記録したコードに新しいコードをコピペした感じですよね？
マクロが記載されているシート名は何ですか？
よろしければ、開発エディタを画像で見せてくれませんか？（質問文に追記していただければ見れますので）
画面の写真は撮ったのですが、アップの仕方が・・・
少々お待ちください
すみません、うまくアップできませんでした。
以下にコピペしてみましたが、こちらでどうでしょうか？
Sub macro10()
maxrow = Range("B5").End(xlDown).Row '// 最終行番号を取得
Range("B5:Q7").Select '// 初期データの3行を選択
Selection.AutoFill Destination:=Range("B5:Q" + CStr(maxrow + 3)), Type:=xlFillDefault '// とりあえず、表全体をコピー
'//[削除] Range("B5:Q10").Select
Range("B5:B7").Select '// 初期データの3行を選択（日付データのB列）
Selection.AutoFill Destination:=Range("B5:B" + CStr(maxrow + 3)), Type:=xlFillMonths '// 日付は月毎の設定で上書き
'//[削除] Range("B5:B10").Select
Range("P5:P7").Select '// 初期データの3行を選択（数式データのP列）
Selection.AutoFill Destination:=Range("P5:P" + CStr(maxrow + 3)), Type:=xlFillCopy '// 数式は数式の設定で上書き（なくてもいい）
'//[削除] Range("P5:P10").Select
Range("B" + CStr(maxrow + 1) + ":Q" + CStr(maxrow + 3)).Select '// 追加した3行を選択状態で終了（特に意味はない）
End Sub
私事ですみませんが、お返事まで2時間ほどかかりそうです。
よろしくお願いいたします。
大丈夫です。ごゆっくり
3行目はRange("B5:Q7").Select の行という認識で合ってますよね？
マクロの先頭に以下のコードを追加して、試してみてください。
※sheet1の所は表が書かれているシート名に変更してください。
thisworkbook.Sheets("sheet1").Select
解決しない場合は以下を教えてください。
シート名の確認方法は回答に画像でアップしましたので、ご確認ください。
①表が記載されているシート名
②ボタンが記載されているシート名
③マクロが記載されているシート名
以上
お返事ありがとうございます。
まだうまくいっていないのですが、お伝えするための画像がアップできません。
シート名は大丈夫だと思うのですが、最初にコピーする表が違うのかもしれません。
mi_kuさんがマクロ記録で作ったマクロは動きますか？
教えたいただいたものをコピペして実行したらB列だけ追加になりました。
エラーは解消されたということでしょうか？
B列〜Q列までコピーされるはずですが…
表の初期データの形が見たいですが…
今のところコピーされるのはB列だけのようです。
表も先ほどアップしたはずなのですが、反映されていないようです。
画像のアップ、繰り返してみます。
いめぴくというところにアップしてみました。
http://imepic.jp/Lv5VaTYd
画像ありがとうございます！
B列が結合セルの場合、私が作ったコードではmaxrowが間違った値を取得してしまうことは確認できましたが、mi_kuさんの方ではエラーは出ませんか？
B列のみコピーされる意味がわかりませんが…B列のみコピーされた画像もよろしければ見せてください！
結合セルを考慮したプログラムに変更しました。
試してみてください。回答追記しました。
KazuSakaさん、ありがとうございます！
お返事が遅くなり、申し訳ありません。
いただいたもので動きました！
前回のB列のみコピーされる、というのは私のミスでした。
途中まで自分で作成していたマクロと、
KazuSakaさんにいただいたマクロが同じ名前になってしまっており、
フォームコントロールで追加したボタンをクリックしたときに
自分が作成したほうが動いていたようです。
全て削除したら問題なく動いております。
いただいたコードをよく見て勉強させていただきたいと思います。
ちなみに、結合していない1行のみをコピーする場合は、
最初のコードで「MaxRow + 3」を「MaxRow + １」にすればよいのでしょうか？
すみません、もう一つお伺いしてもよろしいでしょうか？
同じ処理を他のシートでも行いたいのですが、
その場合はどのようにしたらよいのでしょうか？
例えばシート１にボタンを一つ設置して、
シート４，５，６で同じ処理がしたいです。
シート２，３は表の形式が違うので別なコードにする予定です。
シート名を変更してsub内にコピペしてもダメでした。
シート毎にコードを作成してシートの数分のボタンが必要でしょうか？
回答欄をご覧ください。
KazuSakaさん、ありがとうございます！
おかげさまでやりたい操作ができました。
いろいろ教えていただきまして、ありがとうございました！
あなたの回答
tips
プレビュー
