シートをまたいでオートフィル

シート１　セルA1の「ZZ0010」の値をセルA2からA5まで、
更にシート２　セルB1からB3までオートフィルをして連続したデータ（ZZ0010からZZ0018まで）を作成することはできますでしょうか？

同じシート内であれば以下のVBAでオートフィル可能でした、シート間の指定の仕方がうまく行かず悩んでいます。
もしくはもっと簡単な方法があれば教えてください。
よろしくお願いいたします。

Sub テスト

Range("A1").AutoFill Destination:=Range("A1:A5"), _
Type:=xlFillSeries
End Sub

No.5 ベストアンサー 回答者： GooUserラック 回答日時： 2019/10/11 16:54

オートフィル的な動作をするものも作りました。

Sub 連続数セット(エリア As Range, Optional 初位置 As Variant)

Dim 対象 As Range
Static フォーマット As String
Static 数 As Long
Dim 位置 As Long
Dim 数字 As String

　If Not IsMissing(初位置) Then
　　フォーマット = ""
　　For 位置 = Len(初位置.Value) To 1 Step -1
　　　If IsNumeric(Mid(初位置.Value, 位置, 1)) Then
　　　　フォーマット = "0" & フォーマット
　　　　数字 = Mid(初位置.Value, 位置, 1) & 数字
　　　Else
　　　　フォーマット = Mid(初位置.Value, 位置, 1) & フォーマット
　　　End If
　　Next
　　数 = 数字
　End If
　For Each 対象 In エリア
　　対象.Value = Format(数, フォーマット)
　　数 = 数 + 1
　Next

End Sub

使い方は以下のようになります。（Sheet1 の A1 セルに初期値が入っているとして）

Sub テスト()

　Call 連続数セット(Sheets("Sheet1").Range("A1:A5"), Sheets("Sheet1").Range("A1"))
　Call 連続数セット(Sheets("Sheet2").Range("B1:B3"))

End Sub

※ オートフィルと違い初位置には原点を含まない指定も出来ます。
※ A1 に「ZZ12A8」を入れてテストを実行すると判りますが、数字の間に数字以外の文字が入っても動作します。

この回答へのお礼

すばらしい！私のやりたいことはまさにこれです。
お察しの通り原点の値が流動的に変化すること、オートフィル対象シートが複数あり面倒でしたのでご相談させていただきました。
ありがとうございます。

お礼日時：2019/10/12 05:59

No.4 回答者： GooUserラック 回答日時： 2019/10/11 16:09

No.3 の注意点

① エラーなどすると書式や初数はクリアされて初期値になります、なるべくまとめて処理して下さい。
② 書式や初数の初期値は、それぞれ「""」（空白）と「0」になります。

No.3 回答者： GooUserラック 回答日時： 2019/10/11 15:48

No.2 を少し改良しました。

改良点　エリア以外は省略可能にし戻り値が不要になりましたので「Function」ではなく「Sub」にしました。省略した場合は、書式は前回の物、初数は前回の次の数がそれぞれセットされます。

Sub 連続数セット(エリア As Range, Optional 書式 As Variant, Optional 初数 As Variant)

Dim 対象 As Range
Static フォーマット As String
Static 数 As Long

　If Not IsMissing(書式) Then フォーマット = 書式
　If Not IsMissing(初数) Then 数 = 初数
　For Each 対象 In エリア
　　対象.Value = Format(数, フォーマット)
　　数 = 数 + 1
　Next

End Sub

使い方は以下のようになります。

Sub テスト()

　Call 連続数セット(Sheets("Sheet1").Range("A1:A5"), "ZZ0000", 10)
　Call 連続数セット(Sheets("Sheet2").Range("B1:B3"))

End Sub

No.2 回答者： GooUserラック 回答日時： 2019/10/11 14:31

オートフィルは原点を含む連続したエリアしか出来ないと思います。

（そういう機能）

しかも、なぜVBAで使う必要が有るか？判りません
原点の書式が毎回変化するので面倒とかでしょうか？

一応ユーザー定義関数を作ってみました

Function 連続数セット(フォーマット As String, 数 As Long, エリア As Range) As Long

Dim 対象 As Range

　For Each 対象 In エリア
　　対象.Value = Format(数, フォーマット)
　　数 = 数 + 1
　Next
　連続数セット = 数

End Function

使い方は以下のようになります。
残念ながらシートを跨いでの処理は出来ませんので２行に分ける必要があります。

Sub テスト()

Dim スタート数 As Long

　スタート数 = 10
　スタート数 = 連続数セット("ZZ0000", スタート数, Sheets("Sheet1").Range("A1:A5"))
　スタート数 = 連続数セット("ZZ0000", スタート数, Sheets("Sheet2").Range("B1:B3"))

End Sub

No.1 回答者： tatsu99 回答日時： 2019/10/11 13:41

一度には無理かと。

シート毎にわけて２回行うしかないかと思いますが。