エクセルVBA オートフィルの最終行の番号を特定セルの入力値で指定したい

エクセルVBA初心者です。

Sheets("Sheet2").Select
Range("A2:Z2").Select
Selection.AutoFill Destination:=Range("A2:Z500"), Type:=xlFillDefault

上記スクリプトは、Sheet2のA2:Z2の範囲を指定して「500行目まで」
オートフィルを行う内容になっているかと思いますが、
この「何行目まで」という部分を、特定の別シートの特定のセルに
入力されている数字（毎回異なる）で指定したいと思っています。

具体的には、別のシート（仮にSheet1)のセル（仮にA1)に関数が
入力されていて、その時々の条件でA1の数字は異なります。

このA1のセルに例えば800と表示されていたら、Sheet2の
A2:Z2を800行目までオートフィルする、というマクロを作りたいです。
（ちなみにA2:Z2にはもろもろ複雑なエクセル関数が入力されており、
この関数を必要な行番号までオートフィルしたい、というものです。）

具体的にどのようにスクリプトを直せばこのような動きが得られるか、
どなたかご教示頂けないでしょうか？

当方、スクリプトを一から書く力が殆どなく、普段はマクロの
自動記録で得られたスクリプトをベースに手直ししながら
欲しいマクロを作る、という程度しかできません。

何卒よろしくお願いいたします。

No.4 ベストアンサー 回答者： soixante 回答日時： 2016/01/30 18:10

たぶんシート１のA1セルには、貼り付けた行の行数をカウントする関数が入っているのでしょうか。

想像で書きましたが、以下のコードでやりたいことをカバーしていれば、と思います。

実行後は元に戻せませんので必ずバックアップを取ってからやってください。

’----------------------------------------------------------------------
Sub ZZZ()

'変数の宣言
Dim Ws1 As Worksheet, Ws2 As Worksheet
Dim Rng As Range, LstRow As Long, TgtRow As Long

'Sheet1、2を変数Ws1,Ws2に格納
Set Ws1 = Worksheets("Sheet1")
Set Ws2 = Worksheets("Sheet2")
Ws2.Select

'Sheet2のA2:Z2を変数Rngに格納
Set Rng = Range(Cells(2, 1), Cells(2, 26))

'Sheet2の前回分を消す
LstRow = Cells(3, 1).End(xlDown).Row
Range(Cells(3, 1), Cells(LstRow, 26)).ClearContents

'Sheet2でずらす必要のある行数（Sheet1に貼ったデータ行数）を変数TgtRowに入れる
TgtRow = Ws1.Cells(Rows.Count, 1).End(xlUp).Row - 4

'範囲Rngを、TgtRow までオートフィルする
Rng.AutoFill Destination:=Range(Cells(2, 1), Cells(TgtRow, 26)), Type:=xlFillDefault

'変数開放
Set Rng = Nothing
Set Ws1 = Nothing
Set Ws2 = Nothing

MsgBox "End."

End Sub

’----------------------------------------------------------------------

いちおう逐一コメントを入れてあるので読んでください。
なお、繰り返し使用することを想定し、オートフィル前にSheet2の前回分を消すフローを
入れてあります。消しに行く範囲は、３行目から、EndのxlDownとしてあります。
不具合あれば適宜いじってください。

また、コードを見ていただくと分かりますが、オートフィルの最終行は、
シート１のA1セルから取得していませんので、もしやりたいことと違ったらすみません。
（＝シート１のA1セルが無くても回ります）

この回答へのお礼

ありがとうございます！！！
ドンピシャにやりたいことが得られました！
大変ありがとうございました、助かりました！

お礼日時：2016/02/02 23:53

No.3 回答者： hallo-2007 回答日時： 2016/01/30 17:58

NO1ですが、今回の質問とだいぶかけ離れると思いますので

改めて質問をされた方が良いかと思います。
①Sheet１に5行目から始まる表があり、この表にその時々で
異なる行数のデータを貼り付ける
５行目以下の何列から何列目まで？
Sheet２のどこに？
②は、今回の回答で解決しませんでしたか？

別案ですが、Sheet1に関数を入れておいて
必要な情報を表示させておく。
Sheet1の必要な列を値を入れるという事でSheet2に入れる
方法では駄目なのでしょうか。

No.2 回答者： moto_koukousei 回答日時： 2016/01/30 16:16

Sheets("Sheet2").Select

Range("A2:Z2").Select
Selection.AutoFill Destination:=Range("A2:Z" & Sheets("Sheet1").Range("A1")), Type:=xlFillDefault

この回答へのお礼

こちらご回答ありがとうございます。
参考にさせて頂きます！

お礼日時：2016/01/30 18:34

No.1 回答者： hallo-2007 回答日時： 2016/01/30 16:00

エクセルVBAでデータが入力されいる最終行を求める方法

ネットで検索するとすぐに見つかりますが。
Sheets("Sheet2").Select
GYOU=range("A" & Rows.Count).end(xlup).Row
Range("A2:Z2").Select
Selection.AutoFill Destination:=Range("A2:Z" & GYOU), Type:=xlFillDefault
といった形になります。
A列の一番下の行から上へ移動して
空白でなくなるセルの行番号が　GYOUと云う変数に入ります。

この回答へのお礼

さっそくありがとうございます！！！大変助かります。

ただ、やりたいことは実は以下のようなことでして、あと一歩悩んでおります。
Sheet1とSheet２で表の開始位置が違う分、どのように調整したらよいか
お知恵を拝借できないでしょうか。

①Sheet１に5行目から始まる表があり、この表にその時々で
異なる行数のデータを貼り付ける

②その時に貼り付けたデータの行数分だけ、Sheet2の関数を
下方向にドラッグしてオートフィルさせる、ただしSheet2の表は
Sheet１とは異なり1行目から始まるレイアウトになっている

お礼日時：2016/01/30 16:39