エクセルマクロでシート名をセルからひっぱりたい

Question

For i = 1 To 10
  If i > 10 Then
    MsgBox "Bookがそんなにありません"
    Exit For
  End If
Workbooks.Open op.Range("C2").Offset(i - 1, 0).Value
With ActiveWorkbook
Range("A1:AU3100").Copy
Workbooks("CAB-Grapf.xls").Worksheets("sheet" & i).Range("A1:AU3100").PasteSpecial Paste:=xlPasteAll

このコードでやりたい事はセルC2からC11までのパスから指定のブックを開きそれをコピーして、
CAB-Grapf.xlsの各シートに貼り付けたいのですが、
今現在のコードだと、Worksheets("sheet" & i)となっているのでシート名が"sheet1"とか"sheet2"でしか読み込めません。

シート名は変更予定なのでこのままだといちいちマクロを修正しなければなりません。

なので指定のセルに例えばSampleと記入しておき、その値をひろってシートをよみこみたいです。

つまり、

Workbooks("CAB-Grapf.xls").Worksheets("sheet" & i).Range("A1:AU3100").PasteSpecial Paste:=xlPasteAll

の"sheet"の部分をセルからひっぱった値にしたいという事です。

いろいろ試してはみたのですが、ことごとく玉砕されてしまったのでどなたか教えていただきたいです。

Answer 1

セルの値はCells.Valueで取得できます。
例えばB5セルの値であれば、Cells(5, 2).ValueとかCells(5, "B").Valueといった式で取得できます。

今回はこれを複数取りたいので、ループ処理で回します。
例えば最初から１０シートとわかっていれば、

For i = 0 To 9
    '対象シート名を取得
    strSheetName = Cells(5 + i, "B").Value    'B5～B14まで順番にセルの値を取得
    '貼り付け
    Workbooks("CAB-Grapf.xls").Worksheets(strSheetName).Range("A1:AU3100").PasteSpecial Paste:=xlPasteAll
Next

といったコードで取得になります。

対象シートがいくつあるのかわからない場合、ループする回数も判断しなければなりません。

Do～Loopで無限ループして、B列が空欄になったらループを抜ける方法や、
B列の最終データ行（B列最下行から[CTRL]+[↑]を操作したセル）を調べてその行までループする方法などがあります。

'Do～Loopするサンプル
i = 0
Do
    strSheetName = Cells(5 + i, "B").Value    'B5セルから順番にセルの値を取得
    If strSheetName = "" Then Exit Do   '取得したセル内容が空欄ならループを抜ける
    i = i + 1
Loop

'B列最終データまでループするサンプル
For i = 5 To Cells(Rows.Count, "B").End(xlUp).Row   '5行目～最終データ行までループ
    strSheetName = Cells(i, "B").Value    'セルの値を取得
Next

ブック内のすべてのシートを対象とするならもう少し楽にできそうですが、今回はセル値からシート名を取得したいということでしたので、ここでは割愛します。

参考になれば幸いです。

Comments

[macro-beginner]

できました！ありがとうございました！

Answer 2

ActiveSheetのセル "B1" にシート名が書かれているとして以下でいいのでは？

Dim sheetName As String
sheetName = ActiveSheet.Range("B1").Value

Workbooks("CAB-Grapf.xls").Worksheets(sheetname).略

---

セル B1 ～ B10 にシート名が文字列として記載されているとして以下のように
取得セルの範囲指定で"B"にループ変数の値を文字列として連結する

Dim sheetName As String
For i = 1 To 10
    sheetName = ActiveSheet.Range("B" & CStr(i)).Value
    Workbooks("CAB-Grapf.xls").Worksheets(sheetname).略
Next

Comments

[macro-beginner]

ご回答ありがとうございます。
すみません、説明が足りませんでした・・・
For文で回してシートが10枚ほどできるのでそれぞれのシート名を各セルからもってきたいのです。

例えば"B1"がsample1、"B2"がsample2、"B3"がsample3,というように記入してあったとして、
"sheet"の部分をその各セルから引張りたいのです。

つまり、"sheet1"を"sample1"、"sheet2"を"sample2"というような感じです。

[Y.H.]

for文でループして"B"にループ変数の値を文字列として連結するだけですよね？ 一応回答に追記しておきました。

[macro-beginner]

できました！ありがとうございました！