[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『結合されたセル 別シート数式をコピーしオートフィルで最終行まで貼り付けたい』（ぬた）
Excel VBAにて"Sheet2"の結合セル（A21:A40）内にある数式をコピーし、"Sheet1"の結合セル（A21:A40）にペースト、最終行までオートフィルを実行させたいのですが、うまく実行できません。
"Sheet2"の貼り付け対象範囲セルは元データ数によって変化します。
元データ数：20→20行分,元データ数：50→50行分
また対象セルはすべて結合されており（結合されて出てくるようになっています）、(A21:A40),(A41:A60),(A61:A80)...と1セルあたり20個のセルが結合されています。
Dim i As Long
Dim j As Long
i = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
j = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Sheet1").Range("A21:A40" & i).Copy
Worksheets("Sheet2").Range("A" & j).Offset(2, 0).PasteSpecial xlPasteAll
ご教授お願い致します。
＜ 使用 Excel：Excel2010、使用 OS：Windows10 ＞
（ぬた） 2019/05/10(金) 21:34
>2行目以降 というのを把握（理解）できませんが
>"Sheet2"の結合セル（A21:A40）内にある数式をコピー
と
>Worksheets("Sheet1").Range("A21:A40" & i).Copy
は明らかに一致しませんね。
特に "A21:A40" & i とは？
"A21:A" & i ですか？
（チオチモリン） 2019/05/10(金) 22:01
誤記訂正させていただきます。
"Sheet2"の結合セル（A21:A40）内にある数式をコピー → >"Sheet1"の結合セル（A21:A40）内にある数式をコピー
×「Excel VBAにて"Sheet2"の結合セル（A21:A40）内にある数式をコピーし、"Sheet1"の結合セル（A21:A40）にペースト、最終行までオートフィルを実行させたいのですが、うまく実行できません。」
〇「Excel VBAにて"Sheet1"の結合セル（A21:A40）内にある数式をコピーし、"Sheet2"の結合セル（A21:A40）にペースト、最終行までオートフィルを実行させたいのですが、うまく実行できません。」
"A21:A" & i で実行したところコピー対象範囲が行の最終行（1048576行）までになってしまい、うまく実行できませんでした。
作成コードですが、他サイトにて掲載されていたコードをコピーさせていただいたものですので、自身で把握できていないのが正直なところになります。
申し訳ありません。
（ぬた） 2019/05/10(金) 22:42
下記コードが全てになります。
また、参考にさせていただいたサイトのリンクになります。
http://www.excelvba.club/entry/2016/11/02/211136
こちらのサイトに掲載されているコードが自分が行いたい処理に近いのではと思い、単純に当てはめた次第です。
Dim i As Long
Dim j As Long
i = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
j = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Sheet1").Range("A21:A40" & i).Copy
Worksheets("Sheet2").Range("A" & j).Offset(2, 0).PasteSpecial xlPasteAll
大変申し訳ありませんが、ご教授お願い致します。
（ぬた） 2019/05/10(金) 23:46
Worksheets("Sheet1").Range("A21:A40" & i).Copy
↓
Worksheets("Sheet1").Range("A21:A" & i).Copy
ではありませんか？
（もこな２） 2019/05/11(土) 09:46
Sub test()
Worksheets("Sheet2").Range("A21:A40").Cells(1.1).Formula = _
Worksheets("Sheet1").Range("A21:A40").Cells(1.1).Formula
End Sub
さらに、オートフィルは【マクロの記録】でどのような命令を使えばよいかの情報が得られるはずですので試してみてください。
ただ、
＞また対象セルはすべて結合されており（結合されて出てくるようになっています）、(A21:A40),(A41:A60),(A61:A80)...と1セルあたり20個のセルが結合されています。
が、私にはよく理解できないです。
もともと結合されているのか、結合されたセルを貼付した結果、結合されているのか・・・
場合によっては、オートフィルは関係なくて、数式と書式をコピペするだけでよいかもしれません。
（もこな２） 2019/05/11(土) 10:05
アドバイスをいただき心より感謝申し上げます。
下記コードによる結合セルの操作を教えていただきありがとうございます。
Sub test()
Worksheets("Sheet2").Range("A21:A40").Cells(1.1).Formula = _
Worksheets("Sheet1").Range("A21:A40").Cells(1.1).Formula
End Sub
こちらのコードをもとにFor〜Nextで最終行取得ができるのではと思い自分の僅かな知識をもとに作成及び実行しようとしましたが、できませんでした。
Dim i As Integer
Dim j As Integer
Dim c As Long
Dim d As Long
i = 21
j = 40
For c = i To Cells(Rows.Count, 1).End(xlUp).Row
For d = j To Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Sheet2").Range(Cells("A" & i), Cells("A" & j)).Cells(1.1).Formula = _
Worksheets("Sheet1").Range("A20:A41").Cells(1.1).Formula
Next
Next
VBA初心者がある知識で入力しましたが、全くの見当違いだと思います。。。
また、マクロの記録にて、試しに”A80”までオートフィルをしてみました。
Range("A21:A40").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("A21:A40").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("A21:A80"), Type:=xlFillDefault
Range("A21:A80").Select
＞Range("A21:A80")部分を最終行までできるように工夫を凝らせば良いのでしょうか。。
＞また対象セルはすべて結合されており（結合されて出てくるようになっています）、(A21:A40),(A41:A60),(A61:A80)...と1セルあたり20個のセルが結合されています。
が、私にはよく理解できないです。
もともと結合されているのか、結合されたセルを貼付した結果、結合されているのか・・・
＞もともと結合されています。
かんばん？を大量に発行する作業があるのですが、手順としてはまずシートは3つあります。(※例a,b,c)
まず、aシートに任意の数値を入力します。仮に20とします。
入力後、マクロのボタンがあるのでクリックすると、bシートに20枚分のかんばんが行方向に出てきます。
※最初のかんばん一枚目の範囲（A1：J20）以降同範囲が数値分
最後に、ｃのシートに数式貼り付け用の模擬かんばんがあるので、模擬かんばん（A21：J40）範囲内の（A21:A40)結合セル範囲の数式をコピーし、bシートかんばん2枚目の（A21:A40）に貼り付け、データがあるところまで下方向にオートフィルを行います。
かんばんのイメージとしては※例一枚目（A1:A20）が連番を表示する範囲、（B1:J20）がかんばん情報が表示されるようになっています。いずれの範囲もすべて結合セルになります。
?@かんばん範囲/列番号範囲 一枚目（A1:J20）/（A1:A20）、二枚目（A21:J40）/（A21:A40）...となります。
2枚目から数式を張り付けるのは最初の一枚目に関しては連番”1”がふられているのですが、2枚目からは連番部分が空白になっているためです。
空白の理由としては、かんばん情報の内容が途中で切り替わり、切り替わり後はまた1からにする必要があるためです。その判定を行う数式がcシートのものになります。
※既にあるマクロに手を加えることはできません。
※自分は新たに連番の数式を張り付けれるマクロを作成しようとしています。
かなり説明が長くなってしましましたが、ご確認いただきご教示ください。
お願い致します。
（ぬた） 2019/05/11(土) 11:49
説明を読む限り ↓ で良さそうですけど
Worksheets("Sheet2").Range("$A$21:$A$400").FormulaR1C1 = "=R[-20]C+1"
（チオチモリン） 2019/05/11(土) 13:23
教えていただいたコードをもとに数式を代入、実行したところ任意のセルまで貼り付け、処理ができました。
Worksheets("Sheet2").Range("$A$21:$A$400").FormulaR1C1 = "=IF(RC[1]=R[-20]C[1],R[-20]C+1,1)"
上記のコードになるのですが、
＞Range("$A$21:$A$400").FormulaR1C1
この部分を可変する最終行まで表示させたいのですが、どのようなコードがありますでしょうか。
最終行は対象データによって変わるので、Cells(Rows.Count,1)End(xlUp).Rowなどを使用するのでしょうか。
ご教示お願いいたします。
（ぬた） 2019/05/11(土) 14:19
>Cells(Rows.Count,1)End(xlUp).Row この書き方は好きな人も多いですが私はほとんど使いません。 状況依存のコードだからです。 本件の場合も >可変する最終行 を判定するのに１列（Ａ列）が適していないのはわかりますが じゃあどの列で判定出来るかというと、これまでの説明の中には記述されていません。 故に ↓ を使うなら、判定できる列を指定する事。がアドバイスになります。 >Cells(Rows.Count,1)End(xlUp).Row
又 SpecialCells(xlCellTypeLastCell)から求める方法もありますが、これも状況によりますので好みません。 (本件では使えそうですが)
で、これまでの説明の中で
>aシートに任意の数値を入力します。仮に20とします。
>入力後、マクロのボタンがあるのでクリックすると、bシートに20枚分のかんばんが行方向に出てきます。
ということなので、
Range("$A$21:$A$400")を
↓
Range("$A$21:$A$" & Cstr(Range("$A$21:$A$" & Worksheets("Sheet1").Range(aシートに任意の数値を入力するセルのアドレス).Value * 20))に変更
を試してみて下さい。
（チオチモリン） 2019/05/11(土) 16:53
チオチモリンさんへ
＞ >Cells(Rows.Count,1)End(xlUp).Row ＞ この書き方は好きな人も多いですが私はほとんど使いません。 ＞ 状況依存のコードだからです。
ちょっと驚きの見解なんですが、 チオチモリンさんが常用する「最終行確認方法」がどんなものなのか、教えていただけませんか？
※ 横道にそれるとマズいので、このトピックが解決した後で結構です。
（半平太） 2019/05/12(日) 16:18
チオチモリンさんへ
いまここで論じると、脇道にそれ過ぎるので、 そっくり、新トピックに移動させていただきます。ご了承ください。m(__)m ↓ 最終行確認の作法 [[20190513094442]]
（半平太） 2019/05/13(月) 09:58
コメントいただきありがとうございます。
また返信を遅れてしまい大変申し訳ありません。
実は［aシートに任意の数値を入力する］ですが、かなり細かなマクロが組まれています。
自分が少し簡略的に説明しすぎました。大変申し訳ありません。
ですが、チオチモリンさんのコードをヒントに自分の導きたい答えに繋がることができ、無事完成しましたことを報告させていただきます。
本当に感謝しています。
皆さま、今回は貴重なご意見、アドバイスを下さりありがとうございました。
自分自身、ステップアップできるよう日々精進します。
（ぬた） 2019/05/14(火) 02:38
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.