VBAについて「違うブックからデータを取得し行と列を入れ替える」

Question

エクセルで、異なるブックからデータを取得し、行と列を入れ替えて並べ替えるVBAを書いております。
まずはfor文を構築する前に1列だけ実行しようと考え、下記のコードを書きましたがエラー402が16行目で発生しました。

セルの値を取得するためにrange("H5:AI5")とすると上手くいくのですが、for文で書くことを考え
Range(Cells(5, 8), Cells(5, 22))とcellsを使用すると上手くいきませんでした。
（後に"5"の場所をiにすればfor文ができると考えていました。）

Sub 穴径測定結果抽出応用15()

Dim Wb1, Wb2
    Set Wb1 = ThisWorkbook 'このブック
    Set Wb2 = Workbooks("測定結果.xlsx") '元のエクセル名を記入
Dim LastRow As Long '最後の行
Dim LastClm As Long '最後の列
Dim i As Long
Dim j As Long
    
 '最終行、最終列を取得
    LastRow = Wb2.Worksheets("測定結果元データ").Cells(Rows.Count, 8).End(xlUp).Row 'シート名を記入
    LastClm = Wb2.Worksheets("測定結果元データ").Range("A1").End(xlToRight).Column 'シート名を記入
    
  'セルの値を取得する
  Wb2.Worksheets("測定結果元データ").Range(Cells(5, 8), Cells(5, 22)).Copy  '**エラーがでる行**
  Wb1.Worksheets("Sheet1").Cells(22, 2).PasteSpecial Paste:=xlPasteValues, Transpose:=True  'シート名を記入
    

End Sub

Answer 1

何も指定しなければ、CellsやRangeはアクティブシートから取得してこようとします。

Rangeは測定結果元データであると指定しているのに、Cellsはアクティブシート（このソースではコピー先シート）を使ったのでは、一体どこを指定しようとしているのか分かりません。

つまり、Cellsも測定結果元データで指定してやる必要があります。

※複数のシートを操作する必要がある場合、CellsやRowsは必ず操作対象シートを指定することを強くお勧めします。

毎回Worksheetsから取得してくるのも何ですので、ワークシートは一度変数に受けおくなり、Withを使うなりしてやると良いでしょう。

変数に受ける場合

Dim wsSrc As Worksheet
Set wsSrc = Wb2.Worksheets("測定結果元データ")

wsSrc.Range(wsSrc.Cells(5, 8), wsSrc.Cells(5, 22)).Copy

Withを使う場合

With Wb2.Worksheets("測定結果元データ")
    .Range(.Cells(5, 8), .Cells(5, 22)).Copy
End With

Comments

[yoshih]

CellsはRangeの( )内にあるため、元データで指定できていると考えておりました。ご指摘いただいたところを理解し、ワークシートを変数にすることで解決することが出来ました。

また、変数を使用したパターンとwithを用いたパターンの2つを提示していただき大変勉強になりました。
ありがとうございます。
自分の勉強不足を痛感しました。。。。

Answer 2

以下のようにすると改善すると思います。

Dim moto As Worksheet
Set moto = Wb2.Worksheets("測定結果元データ")

 '最終行、最終列を取得
    LastRow = moto.Cells(moto.Rows.Count, 8).End(xlUp).Row 'シート名を記入
    LastClm = moto.Range("A1").End(xlToRight).Column 'シート名を記入

  'セルの値を取得する
  moto.Range(moto.Cells(5, 8), moto.Cells(5, 22)).Copy  'RangeとCellsのSheetを一致させる

---

なお、セル範囲を指定するときに Range(rng1, rng2) という形を使うと、このエラーが起きがちです。
なので、個人的にはその書き方は避け、以下のようにすることが多いです。

moto.Cells(5, 8).Resize(, 15).Copy

Comments

[yoshih]

reiseを使用したことがなく、初めて聞きました。
セルの範囲サイズが指定できるので非常に便利だと思いました。

使ってみるとシンプルでしたので、今後私も使用していきたいと思います。
ありがとうございました。
また便利な技を教えて頂きますと幸いです。