エクセル マクロ VBA Range Value 繰り返し転記について

「プロシージャが大きすぎます」と表示されてしまいました。
　【やりたいこと】シート1から45まで、各々のスキルマップシートがあります。
　　　　　　　　　各々のスキルマップの各項目ごとの小計評点を集計シートに転記させたいのです。

下記に、作成した標準モジュールを記載致します。
Option Explicit
Sub data_collect()
Worksheets("②集計").Range("C24").Value = Worksheets("1").Range("AJ1").Value
Worksheets("②集計").Range("C25").Value = Worksheets("2").Range("AJ1").Value
Worksheets("②集計").Range("C26").Value = Worksheets("3").Range("AJ1").Value
Worksheets("②集計").Range("C27").Value = Worksheets("4").Range("AJ1").Value
Worksheets("②集計").Range("C28").Value = Worksheets("5").Range("AJ1").Value
Worksheets("②集計").Range("C29").Value = Worksheets("6").Range("AJ1").Value
Worksheets("②集計").Range("C30").Value = Worksheets("7").Range("AJ1").Value
Worksheets("②集計").Range("C31").Value = Worksheets("8").Range("AJ1").Value
Worksheets("②集計").Range("C32").Value = Worksheets("9").Range("AJ1").Value
Worksheets("②集計").Range("C33").Value = Worksheets("10").Range("AJ1").Value
Worksheets("②集計").Range("C34").Value = Worksheets("11").Range("AJ1").Value
Worksheets("②集計").Range("C35").Value = Worksheets("12").Range("AJ1").Value
Worksheets("②集計").Range("C36").Value = Worksheets("13").Range("AJ1").Value
Worksheets("②集計").Range("C37").Value = Worksheets("14").Range("AJ1").Value
Worksheets("②集計").Range("C38").Value = Worksheets("15").Range("AJ1").Value
Worksheets("②集計").Range("C39").Value = Worksheets("16").Range("AJ1").Value
Worksheets("②集計").Range("C40").Value = Worksheets("17").Range("AJ1").Value
Worksheets("②集計").Range("C41").Value = Worksheets("18").Range("AJ1").Value
Worksheets("②集計").Range("C42").Value = Worksheets("19").Range("AJ1").Value
Worksheets("②集計").Range("C43").Value = Worksheets("20").Range("AJ1").Value
Worksheets("②集計").Range("C44").Value = Worksheets("21").Range("AJ1").Value
Worksheets("②集計").Range("C45").Value = Worksheets("22").Range("AJ1").Value
Worksheets("②集計").Range("C46").Value = Worksheets("23").Range("AJ1").Value
Worksheets("②集計").Range("C47").Value = Worksheets("24").Range("AJ1").Value
Worksheets("②集計").Range("C48").Value = Worksheets("25").Range("AJ1").Value


Worksheets("②集計").Range("D24").Value = Worksheets("1").Range("AI24").Value
Worksheets("②集計").Range("D25").Value = Worksheets("2").Range("AI24").Value
Worksheets("②集計").Range("D26").Value = Worksheets("3").Range("AI24").Value
Worksheets("②集計").Range("D27").Value = Worksheets("4").Range("AI24").Value
Worksheets("②集計").Range("D28").Value = Worksheets("5").Range("AI24").Value
Worksheets("②集計").Range("D29").Value = Worksheets("6").Range("AI24").Value
Worksheets("②集計").Range("D30").Value = Worksheets("7").Range("AI24").Value
Worksheets("②集計").Range("D31").Value = Worksheets("8").Range("AI24").Value
Worksheets("②集計").Range("D32").Value = Worksheets("9").Range("AI24").Value
Worksheets("②集計").Range("D33").Value = Worksheets("10").Range("AI24").Value
Worksheets("②集計").Range("D34").Value = Worksheets("11").Range("AI24").Value
.....　　
　※各シートのセルADまで延々とつづくマクロを作成しました。
　
　VBAの勉強不足は重々承知しております。もっと簡潔にできるのでしょうか。
　ご教示いただけますと幸いです。

質問者からの補足コメント

• この「①集計」シートにマクロで「1～45」まである数値を転記したいのです。

  
    補足日時：2020/09/10 12:53

• 文章力が無く、言葉足らずですみません。
  行方向だけのループです！
  また、丁寧に親切に分かりやすく説明してくださり、とても助かりました！ また、理解できました！ もっともっとVBAコードを勉強したいと思います☺️

  No.3の回答に寄せられた補足コメントです。 補足日時：2020/09/10 19:07

No.2 ベストアンサー 回答者： tom04 回答日時： 2020/09/10 16:14

こんにちは！

シート名が「1」から「45」までのシートは必ず存在する！という前提で・・・

Sub Sample1()
　Dim i As Long
　Dim sN As String
　　For i = 1 To 45
　　　sN = i
　　　　With Worksheets("②集計").Cells(i + 23, "C")
　　　　　.Value = Worksheets(sN).Range("AJ1")
　　　　　.Offset(, 1) = Worksheets(sN).Range("AI24")
　　　　End With
　　Next i
End Sub

こんな感じではどうでしょうか？m(_ _)m

この回答へのお礼

早々に教えて下さりありがとうございます！　頂いたVBAで、実行できました！知識不足で大変申し訳ないのですが、「With Worksheets("②集計").Cells(i + 23, "C")」　←この部分は、どういう意味になるのでしょうか。。　また、②集計シートの　E24セル～E68セル　に「1～45」シートの　AJ24　の数値を反映させたい場合、下記のように付け足していけば良いのでしょうか。　※？？の部分の意味が分かりません。
Sub Sample1()
　Dim i As Long
　Dim sN As String
　　For i = 1 To 45
　　　sN = i
　　　　With Worksheets("②集計").Cells(i + 23, "C")
　　　　　.Value = Worksheets(sN).Range("AJ1")
　　　　　.Offset(, 1) = Worksheets(sN).Range("AI24")
　　　　With Worksheets("②集計").Cells(i + 23, "？？")
　　　　　.Value = Worksheets(sN).Range("AJ1")
　　　　　.Offset(, 1) = Worksheets(sN).Range("AJ24")

　　　　End With
　　Next i
End Sub

お礼日時：2020/09/10 16:48

No.5 回答者： めぐみん_ 回答日時： 2020/09/10 18:19

各シートのＡＩ２４から左に４つの値を取得したら、次の目的行（ＡＩｎ）に飛んで４つの値を取得ってのを繰り返すのでしょ？（画像の赤印を見る限り）

その際に目的の行ナンバーが固定であれば書き出した方が楽な気もしますけど？

とは言え２つ目の画像を老眼で見ると７つの値で１組になっているみたいですが、４つの値を列に対して順に入れていく訳でもないのでしょうか？
それとも元々が４つの値ではなく７つの値を取得するはずなのか？

画像の説明を箇条書きにせよ具体的に書かれた方が宜しいと思いました。
けどOfficeをぶっ壊した初級者なので疑問点だけ言って去っていきますけど。

この回答へのお礼

ありがとうございます。

お礼日時：2020/09/10 19:08

No.4 回答者： tom04 回答日時： 2020/09/10 18:01

No.2・3です。

投稿偽気づきました。
＞※？？の部分の意味が分かりません。

の件ですが、そのまま
＞With Worksheets("②集計").Cells(i + 23, "C")
とC列のままです。

＞.Offset(, 1)＝***
としていますので、C列が基準で1列右側となり　→　D列

＞.Offset(, 2)　とすれば2列右側になるので、E列になります。

※　当方のコードでOffsetの「行」の部分を省略していますが、丁寧に記載すれば
＞.Offset(0, 1)
のようになります。

「Withステートメント」についてはネットで検索してみてください。m(_ _)m

この回答へのお礼

ご丁寧にご親切に教えて下さりありがとうございます。 なんとなく理解出来ました！もっと理解を深められるよう勉強します。

お礼日時：2020/09/10 19:09

No.3 回答者： tom04 回答日時： 2020/09/10 17:52

No.2です。

＞「With Worksheets("②集計").Cells(i + 23, "C")」　←この部分は、どういう意味になるのでしょうか

For　～　Next　で　１～45（回）ループさせるコードなので
＞Worksheets("②集計").Cells(i + 23, "C")
の「i+23」　→　24　～　68　になりますね。

＞Cells(行番号, 列番号)　という表記になります。（"C"はそのままC列　→　"C"　の代わりに　3　でも構いません）
すなわちC列24行目以降に順次、シート名「1」～「45」までの「AJ1」セルの値を代入！というコトになります。

※　シート名は文字列にしなければならないので、
＞sN = i
とし、
＞ Worksheets(sN)
のようにしています。

尚、 Worksheets(i)　としてしまうと
仮に　 Worksheets(1)の場合はシート見出しの一番左側のシートになってしまいます。

次に
＞また、②集計シートの　E24セル～E68セル　に「1～45」シートの　AJ24　の数値を・・・

お礼欄にある空白行に
＞.Offset(, 2) = Worksheets(sN).Range("AJ24")

を追加すればいけると思います。

質問文内で一つ不明な点があります。
＞※各シートのセルADまで延々とつづくマクロを作成しました。
とは列方向に関してもループが必要なのでしょうか？

質問文では行方向だけのループで済むはずなのですが。m(_ _)m

No.1 回答者： fujillin 回答日時： 2020/09/10 12:20

こんにちは

ほぼ同じ処理の繰り返しと思いますので、ループで処理すれば宜しそうです。
（For ~~ Next など）

例えば、最初のAJ1セルの値をコピーする部分であれば
Dim t As Range, i As Long
Set t = Worksheets("②集計").Range("C24")

For i = 1 To 25
t.Offset(, i - 1).Value = Worksheets(i & "").Range("AJ1").Value
Next i

みたいな感じでもできると思います。
参照先に規則性があるなら、二重ループにすればほとんど上記の記述で終わると思います。
規則性が無い場合でも、参照先のセル位置を配列などに定義しておくことで、同様の処理を行うことも可能です。（すこしだけ複雑になりますが）


あるいは、シート名の一覧を別に作成しておいて、それを利用して値を参照する関数式を作成することもできそうに思われます。
関数式は一度設定しておけば自動的に値が更新されるので、マクロを実行する手間が不要になります。

この回答へのお礼

早々に教えてくださりありがとうございます。まだ、私の知識不足でループやらなにやら理解に至らず申し訳ないです。理解できるように、勉強します！！

お礼日時：2020/09/10 16:43