Excel 形式を選択して貼り付けの「値」・「行列を入れ替える」 を一気に行うには!?

質問

行いたい処理の内容は以下になります。

sheet名 = A1, A2, A3…　　　　　　　　　　　全てのsheetが対象、sheet名に規則性はない。
セル = G15～G32, G36～53, G57～74　　※全sheet同じ場所をコピーします。

で、質問なのですが表題どおり、全sheetのcopyを　→　張り付けの『形式を選択して貼り付けの「値」・「行列を入れ替える」』にチェックを入れて、新しいまとめ用のsheetに貼り付けを一気に行うってのは可能でしょうか?

移動先の「まとめ」用sheetには、
「A1」のsheet内容は1行目に
「A2」のsheet内容は2行目に
「A3」のsheet内容は3行目に
　　　　　・
　　　　　・
　　　　　・

といった感じにさせたいのですが…アドバイスよろしくお願い致します。

回答No.2

imogasi · ベストアンサー

Sub test01()
Dim ws As Worksheet
K = 1
For Each ws In ActiveWorkbook.Sheets
If ws.Name = "Sheet4" Then '集約シートは処理せず
Else
ws.Range("G15:G32").Copy
Sheets("Sheet4").Cells(K, 1).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
'-------
ws.Range("G36:G53").Copy
Sheets("Sheet4").Cells(K, 18).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
'-------
ws.Range("G57:G74").Copy
Sheets("Sheet4").Cells(K, 35).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
K = K + 1
End If
Next
End Sub
複数範囲を指定してコピーをしようとするとエラーに成ったので、上記コード内で３回コピーを連ねてます。
１７セルごとですから、繰り返し化が出来ますが
コピー元が３ブロックですので、書き連ねました。
あるいは、＃１のご解答のように
Worksheets(1).Range("G15:G32,G36:G53,G57:G74").Copy
Worksheets(4).Range("A1").PasteSpecial Transpose:=True
が出来るのでそれを使う手もあります。(下記）
集約シートはSheet４にしてます。本番では
If ws.Name = "Sheet4" Then
のShet4を変えてください。ほか３箇所のShhet4も。
集約シートは、シートタブを右端においてください。
改良版
Sub test03()
Dim ws As Worksheet
k = 1
For Each ws In ActiveWorkbook.Sheets
If ws.Name = "Sheet4" Then
Else
ws.Range("G15:G32,G36:G53,G57:G74").Copy
Worksheets("sheet4").Cells(k, "A").PasteSpecial Transpose:=True
k = k + 1
End If
Next
End Sub

回答No.3

papayuka

#1です。

> 処理的にVBAでは『値』のみを張り付けたい時は

PasteSpecial Paste:=xlAll　　　　　'「全て」
　　　↓
PasteSpecial Paste:=xlPasteValues　　　　　'「値」

> １．With ～ End With構文の使用方法・意味がいまいち理解できていません。

With を使わないと↓こうなるので、重なる Worksheets(1) をWithで処理しています。

Worksheets(1).Range("A" & Worksheets(1).Range("A65536").End(xlUp).Row + 1).PasteSpecial Paste:=xlAll, Transpose:=True

> ２．.Range("A" & .Range("A65536").End(xlUp).Row + 1). _

A列の最後のセル+1を出しているだけです。
A65536 から Ctrlキーを押しながら↑キーを押す動作をマクロ記録して見て下さい。

　　Range("A65536").Select
　　Selection.End(xlUp).Select

これを繋げて、Select の代わりに Row（行番号）+1 を得て "A" に繋げて "A100" などの文字列を出しています。

Offsetを使うともっとスマートに書けるかも。

お礼

詳しい解説ありがとうございます。
とても勉強になりました。

回答No.1

papayuka

やるとしたらマクロですね。

例は先頭に新規シートを作って、全シートの指定範囲を2行目から順番に貼り付けます。
（1行目からだと判定処理が必要なので）

試すならテスト環境で。

Sub test()
Dim i As Integer, ws As Worksheet, LRow As Long
　Set ws = Worksheets.Add(before:=Worksheets(1))
　ws.Name = "マクロで追加 " & ws.Name
　For i = 2 To Worksheets.Count
　　Worksheets(i).Range("G15:G32,G36:G53,G57:G74").Copy
　　With Worksheets(1)
　　　　.Range("A" & .Range("A65536").End(xlUp).Row + 1). _
　　　　　　　　PasteSpecial Paste:=xlAll, Transpose:=True
　　End With
　Next i
　Application.CutCopyMode = False
End Sub

補足

処理的には結果も思い通りになりました…!?　っと思ったのですが、私の内容の条件に1点漏れがありました! すみません。

＞セル = G15～G32, G36～53, G57～74　　※全sheet同じ場所をコピーします。
実はここのセルは計算式の処理が入っているセルだったのです。内容は以下になります。

=F15*3/1023　
=F16*3/1023
　・
　・
　　 *3/1023 部分は変わりません、全て同じです。

処理的にVBAでは『値』のみを張り付けたい時は何か専用の関数を付け足せば、変換されて『値』のみの表示になってくれるのでしょうか？　(自作関数を作成しないと無理?)

それとも手作業で『値』のみの行を新たに(仮)で張り付けしておいて、その(仮)部分を参照してあげないと無理でしょうか?

で、まだ私の知識不足でしてこの構文をもう少し詳しく説明していただきたいのですが、

＞With Worksheets(1)
＞　　　　.Range("A" & .Range("A65536").End(xlUp).Row + 1). _
＞　　　　　　　　PasteSpecial Paste:=xlAll, Transpose:=True
＞　　End With

１．With ～ End With構文の使用方法・意味がいまいち理解できていません。

２．.Range("A" & .Range("A65536").End(xlUp).Row + 1). _
ここの処理は一体何を意味するものなんでしょうか?
A列を選択してからどこかを参照しているように思えるのですが? (間違ってるかもしれませんけど)

長文になってしまいましたが、再度アドバイスよろしくお願い致します。