ExcelVBA コピー範囲を変数にしたい

質問

いつもお世話になっております。
またVBAでお知恵をください。
SHEET1のある範囲（フラグがたっている）をSHEET2にコピーしたいのですが
フラグが存在しない場合もあるために悩んでいます。

途中まで自分でやってみたのですがエラーになりました。
Dim AA As Range
For Each AA In Range("F4:AL4")　←ここの範囲にフラグがあります
AA.Select
With ActiveCell
If .Value = "1A" Then Selection.Offset(7, 0).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.copy
Sheets("SHEET2").Select
Range("F15").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("sheet1").Select
End With
Next AA
フラグ列を探し、1Aがあったらその7つ下のセルからラストのセルまでの
値をSHEET2のF15にコピーしたいのです。
フラグは1A、1B、2A、2Bと変化し、
貼り付け場所はそれに応じて1A→F15、1B→G15、2A→I15
となります。（貼り付け先は固定）
ただし、フラグが存在しない場合もあり、その場合はSKIPです。
（1BがなければG15には何も貼り付けない）
フラグは10Bまで合計20個あるため、できれば変数にしたいのですが
どのように記述すればいいのかわかりません。

貼り付け先はF、G、I、J、L、M、O、P、と変化します。
（AとBでセットで間に１列あきます。が、Aだけの場合もある）
これも変数にすればよいのでしょうが、15行目、というのを
記述する方法と、存在しない場合に詰めてしまわないようにする
方法がわかりません。。。。
お知恵を貸してください。
よろしくお願いいたします。

回答No.1

mt2008 · ベストアンサー

せっかくExcelを使うんですから、フラグの値と貼り付けセル位置の関係をリストにして別シートに持ちましょう。
そのリストからVlookupでフラグの値に一致するセル位置を取得します。
例えばSheet3のA列にフラグの値、B列に選択セル位置を入れます。

1A　F15
1B　G15
2A　I15
2B　J15
：　：

この状態で、元のマクロを次のように変えます（エラー処理等は手抜きです）。

Dim AA As Range
Dim sString As String '<--- セル位置用
For Each AA In Range("F4:AL4")
AA.Select
With ActiveCell

On Error Resume Next '<--- リスト不一致対策
sString = ""　'<--- 編集を初期化
sString = WorksheetFunction.VLookup(.Value, Sheets("Sheet3").Range("A:B"), 2, True) '<--- リストから貼り付け位置取得
If sString <> "" Then '<--- 貼り付け位置が取得できた場合
　Selection.Offset(7, 0).Select
　Range(Selection, Selection.End(xlDown)).Select
　Selection.Copy
　Sheets("SHEET2").Select
　Range(sString).Select '<--- 取得した貼り付け位置をSelect
　Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End If
Sheets("sheet1").Select
End With
Next AA

別シートにリスト化する事でメンテナンスも容易になります。

回答No.2

mt2008

ちょっとコメント部を訂正

誤：sString = ""　'<--- 編集を初期化
正：sString = ""　'<--- 変数を初期化

お礼

適切なアドバイスありがとうございます！
シート上に定義してLookupで読む方法があるのは
初めて知りました。
（毎回初めて知ることばかりなのですが・・・汗）
この方法で完全に解決しました。
本当に助かりました・・・。（涙）
ありがとうございました。。。