teratail
回答率
85.48%
質問するログイン新規登録
トップに関する質問マクロ VB データ一覧表を元に別のシートにそれぞれの内容をコピーまたは複写する方法

編集履歴

質問編集履歴

8

タイトルの変更

2016/08/03 08:50

投稿

riorio
riorio

スコア11

test CHANGED
@@ -1 +1 @@
1
- マクロ VB データ一覧表を元に別のシートにそれぞれの内容をコピーする方法
1
+ マクロ VB データ一覧表を元に別のシートにそれぞれの内容をコピーまたは複写する方法
test CHANGED
File without changes

7

分からない点についてさらに追記

2016/08/03 08:50

投稿

riorio
riorio

スコア11

test CHANGED
File without changes
test CHANGED
@@ -216,7 +216,7 @@
216
216
 
217
217
 
218
218
 
219
- (追記)
219
+ (追記1-1)
220
220
 
221
221
  たびたび申し訳ございません。
222
222
 
@@ -251,3 +251,203 @@
251
251
  End Sub
252
252
 
253
253
  ```
254
+
255
+
256
+
257
+ ###追記-その2
258
+
259
+ あともう少しで目指しているマクロができそうなのですが、
260
+
261
+ またもやわからない部分があるため、追記させてください。
262
+
263
+
264
+
265
+ 最初に提示させていただいた目標と多少変わったのですが、
266
+
267
+ 「データ一覧」とその他のシートはこのように変更となっております。
268
+
269
+
270
+
271
+ ![イメージ説明](fc11e98768d813d436215715f5acbc77.png)
272
+
273
+
274
+
275
+ 「データ一覧」において、略称に対して国名が定義されているものは国名でシートが作成されており、
276
+
277
+ 国名が定義されていないものは略称のままでシートが作成されています。
278
+
279
+
280
+
281
+ さらに、略称に対してはハイパーリンクを設定し、
282
+
283
+ 略称と連動しているシートに飛べるようになっています。
284
+
285
+
286
+
287
+ 最初は、処理の中でIf文を使って、「略称に対して国名がある場合は国名の付いたシートに飛んで値を出力。そうでないものは略称のシートに飛んで値を出力」ということを行っていました。
288
+
289
+ ```lang-VB
290
+
291
+ Sub for文_別のシートに値を出力_その1()
292
+
293
+ ' 「データ一覧」シートの「略称」の値を上から順番に取得して、
294
+
295
+ ' それぞれの略称名と一致するシートに国名と備考を出力
296
+
297
+ Dim 略称 As Range
298
+
299
+ Dim 略称取得用 As String
300
+
301
+ Dim 国名 As String
302
+
303
+ For Each 略称 In Worksheets("データ一覧").Range("B4:B8")
304
+
305
+ ' 国名が記述されていない場合
306
+
307
+ If 略称.Offset(0, -1).Value = 0 Then
308
+
309
+ ' String型の変数「略称取得用」にRange型の変数「略称」を代入
310
+
311
+ ' これにより「略称」と一致する名前を持つシートの選択が可能となる
312
+
313
+ 略称取得用 = 略称
314
+
315
+ With Worksheets(略称取得用)
316
+
317
+ .Range("B5") = 略称.Offset(0, -1).Value
318
+
319
+ .Range("D5") = 略称.Offset(0, 1).Value
320
+
321
+ End With
322
+
323
+ ' 国名が記述されている場合
324
+
325
+ Else
326
+
327
+ 国名 = 略称.Offset(0, -1)
328
+
329
+ With Worksheets(国名)
330
+
331
+ .Range("B5") = 略称.Offset(0, -1).Value
332
+
333
+ .Range("D5") = 略称.Offset(0, 1).Value
334
+
335
+ End With
336
+
337
+ End If
338
+
339
+ Next 略称
340
+
341
+ End Sub
342
+
343
+ ```
344
+
345
+ ですが、ハイパーリンクが設定されていることからもっと簡単に「リンク設定先のシートに値を出力する」
346
+
347
+ というようにできないか検証した結果、以下のようなマクロになりました。
348
+
349
+ ```lang-VB
350
+
351
+ Sub for文_別のシートに値を出力_その2()
352
+
353
+ ' ハイパーリンク先を取得してそのシートに「データ一覧」の「国名」と「備考」を出力
354
+
355
+ ' "Withステートメント"を使って簡略化
356
+
357
+ Dim 略称 As Range
358
+
359
+ For Each 略称 In Worksheets("データ一覧").Range("B4:B8")
360
+
361
+ 略称.Hyperlinks(1).Follow
362
+
363
+ With ActiveSheet
364
+
365
+ .Range("B5") = 略称.Offset(0, -1).Value
366
+
367
+ .Range("D5") = 略称.Offset(0, 1).Value
368
+
369
+ End With
370
+
371
+ Next 略称
372
+
373
+ End Sub
374
+
375
+ ```
376
+
377
+ これによりやりたいことはほぼ実現できたのですが、
378
+
379
+ 現在の状況としてはセルの範囲を固定しています。
380
+
381
+
382
+
383
+ これを「値のある範囲まで」"For Each文"で検索するにはどのようにしたらよろしいでしょうか？
384
+
385
+
386
+
387
+ 結果としては【B4～B8】までの略称が入っているところまでということで同じになるとは思うのですが、
388
+
389
+ 今後データが追加された際、そのたびにマクロの適用される範囲を修正する必要があるため、
390
+
391
+ ここを動的に処理してくれるようにしたいです。
392
+
393
+
394
+
395
+ [参考①](http://excel-ubara.com/excelvba4/EXCEL257.html)
396
+
397
+ [参考②](http://www.start-macro.com/55/w/s029.html)
398
+
399
+ [参考③](http://oshiete.goo.ne.jp/qa/3243933.html)
400
+
401
+ [参考④](http://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q13103630668)
402
+
403
+
404
+
405
+ これらを参考にマクロを以下のように書き換えてみたのですが、
406
+
407
+ こうすると[略称.Hyperlinks(1).Follow]の位置で
408
+
409
+ 「インデックスが有効範囲にありません」とエラーが起きてしまいます。
410
+
411
+ ```lang-VB
412
+
413
+ Sub for文を使った値の取得()
414
+
415
+ ' ハイパーリンク先を取得してそのシートに「データ一覧」の「国名」と「備考」を出力
416
+
417
+ ' "Withステートメント"を使って簡略化
418
+
419
+ Dim 略称 As Range
420
+
421
+ ' セルの範囲を値のある範囲までとしたい
422
+
423
+ For Each 略称 In Worksheets("データ一覧").Range("B4", Cells(Rows.Count, 1).End(xlUp))
424
+
425
+ 略称.Hyperlinks(1).Follow
426
+
427
+ With ActiveSheet
428
+
429
+ .Range("B5") = 略称.Offset(0, -1).Value
430
+
431
+ .Range("D5") = 略称.Offset(0, 1).Value
432
+
433
+ End With
434
+
435
+ Next 略称
436
+
437
+ End Sub
438
+
439
+ ```
440
+
441
+ デバッグを行うと[Cells(Rows.Count, 1)]のところが[Rows.Count=1048576]で、
442
+
443
+ [End(xlUp)]が[xlUp=-4162]となっているようで、明らかに値があるところ以外を見てしまっています。
444
+
445
+
446
+
447
+ そのため上にあげたエラーが出てしまっているのかと思いますが、
448
+
449
+ どのようにして解決したらよろしいでしょうか？
450
+
451
+
452
+
453
+ よろしくお願いいたします。

6

追記修正

2016/08/03 04:52

投稿

riorio
riorio

スコア11

test CHANGED
File without changes
test CHANGED
@@ -218,6 +218,36 @@
218
218
 
219
219
  (追記)
220
220
 
221
- 申し訳ございません。
221
+ たびたび申し訳ございません。
222
-
222
+
223
- 先ほどこちらに追記していたマクロ内容間違えていたため取り消しました。
223
+ こちらのマクロで試してみたところ、うまくコピペすることができました。
224
+
225
+ このように一個一個分けていかないとだめなのでしょうか？
226
+
227
+
228
+
229
+ ```lang-VB
230
+
231
+ Sub セルコピペ_その2()
232
+
233
+ '
234
+
235
+ ' セルをコピーして別のシートに貼り付け
236
+
237
+ '
238
+
239
+ Worksheets("データ一覧").Select
240
+
241
+ Range("A4").Select
242
+
243
+ Selection.Copy
244
+
245
+ Worksheets("USA").Select
246
+
247
+ Range("B5").Select
248
+
249
+ ActiveSheet.Paste
250
+
251
+ End Sub
252
+
253
+ ```

5

追記間違え

2016/08/02 04:44

投稿

riorio
riorio

スコア11

test CHANGED
File without changes
test CHANGED
@@ -218,30 +218,6 @@
218
218
 
219
219
  (追記)
220
220
 
221
- たびたび申し訳ございません。
221
+ 申し訳ございません。
222
-
222
+
223
- こちらのマクロで試したところコピペがうまくいきました。
223
+ 先ほどこちらに追記していたマクロ内容間違えていたため取り消しました。
224
-
225
- こんなにまどろっこしい形にしないとだめなのでしょうか？
226
-
227
- ```lang-VB
228
-
229
- Sub セルコピー貼り付け_その1()
230
-
231
- ' ワークシートの1つのセルの値を
232
-
233
- ' 別のシートの別のセルにコピー貼り付け
234
-
235
- '
236
-
237
- Worksheets("データ一覧").Range("A4").Select
238
-
239
- Selection.Copy
240
-
241
- Sheets("USA").Select
242
-
243
- ActiveSheet.Paste
244
-
245
- End Sub
246
-
247
- ```

4

追記

2016/08/02 04:12

投稿

riorio
riorio

スコア11

test CHANGED
File without changes
test CHANGED
@@ -213,3 +213,35 @@
213
213
  エクセルは2013を利用しています。
214
214
 
215
215
  よろしくお願いいたします。
216
+
217
+
218
+
219
+ (追記)
220
+
221
+ たびたび申し訳ございません。
222
+
223
+ こちらのマクロで試したところコピペがうまくいきました。
224
+
225
+ こんなにまどろっこしい形にしないとだめなのでしょうか？
226
+
227
+ ```lang-VB
228
+
229
+ Sub セルコピー貼り付け_その1()
230
+
231
+ ' ワークシートの1つのセルの値を
232
+
233
+ ' 別のシートの別のセルにコピー貼り付け
234
+
235
+ '
236
+
237
+ Worksheets("データ一覧").Range("A4").Select
238
+
239
+ Selection.Copy
240
+
241
+ Sheets("USA").Select
242
+
243
+ ActiveSheet.Paste
244
+
245
+ End Sub
246
+
247
+ ```

3

追記

2016/08/02 03:00

投稿

riorio
riorio

スコア11

test CHANGED
File without changes
test CHANGED
@@ -210,4 +210,6 @@
210
210
 
211
211
 
212
212
 
213
+ エクセルは2013を利用しています。
214
+
213
215
  よろしくお願いいたします。

2

追記の記述

2016/08/02 02:53

投稿

riorio
riorio

スコア11

test CHANGED
File without changes
test CHANGED
@@ -133,3 +133,81 @@
133
133
  何をどのように進めていけばよいのか分からず悩んでいます。
134
134
 
135
135
  分からないことだらけで非常に申し訳ないのですが、よろしくお願いいたします。
136
+
137
+
138
+
139
+ ###追記-その1
140
+
141
+ 皆様ご回答いただきありがとうございます。
142
+
143
+
144
+
145
+ 昨日に引き続きマクロを勉強しております。
146
+
147
+ 皆様が教えてくださったおかげで完成までのイメージは掴むことができたのですが、
148
+
149
+ やはり1つ1つできることからやっていってみようと思い、
150
+
151
+ 基本的なところから勉強をやり直しています。
152
+
153
+
154
+
155
+ そして今、1つのシートのセルの値をコピーして別のシートに貼り付ける
156
+
157
+ ということを試しているのですが、調べたとおりにやってみてもエラーが出てしまい
158
+
159
+ うまくいきません。
160
+
161
+ 何がいけないのかお力添えいただけますでしょうか？
162
+
163
+
164
+
165
+ ```lang-VB
166
+
167
+ Sub セルコピー貼り付け_その1()
168
+
169
+ ' ワークシートの1つのセルの値をコピーして
170
+
171
+ ' 同じシートの別のセルに貼り付ける場合
172
+
173
+ '
174
+
175
+ Worksheets("データ一覧").Range("B4").Copy
176
+
177
+ ActiveSheet.Range("B11").Paste
178
+
179
+ End Sub
180
+
181
+
182
+
183
+ ```
184
+
185
+
186
+
187
+ ```lang-VB
188
+
189
+ Sub セルコピー貼り付け_その2()
190
+
191
+ ' ワークシートの1つのセルの値をコピーして
192
+
193
+ ' 別のシートのセルに貼り付ける場合
194
+
195
+ '
196
+
197
+ Worksheets("データ一覧").Range("B4").Copy
198
+
199
+ Worksheets("USA").Range("B11").Paste
200
+
201
+ End Sub
202
+
203
+
204
+
205
+ ```
206
+
207
+ ちなみに'Paste'メソッドをつけなくても貼り付けできるという記事を[こちら](http://www.atmarkit.co.jp/ait/articles/1402/24/news108_2.html)で見たのですが、これも同じくエラーが出ています。
208
+
209
+ エラー内容は「実行時エラー'438' オブジェクトは、このプロパティまたはメソッドをサポートしていません」、「セルコピー貼り付け_その2()」となっています。
210
+
211
+
212
+
213
+ よろしくお願いいたします。

1

画像の誤りを修正

2016/08/02 02:49

投稿

riorio
riorio

スコア11

test CHANGED
File without changes
test CHANGED
@@ -12,7 +12,7 @@
12
12
 
13
13
  以下のように「国名」、「略称」、「備考」のデータがあります。
14
14
 
15
- ![イメージ説明](4cefdc903f0caabcc499006ee5a1645f.png)
15
+ ![イメージ説明](4f447f5adcacc880366b1e08a5363c55.png)
16
16
 
17
17
 
18
18
 
@@ -20,7 +20,7 @@
20
20
 
21
21
  それぞれ以下のようなフォーマットが用意されています。
22
22
 
23
- ![イメージ説明](7553337de754e860e597d7c2d4389649.png)
23
+ ![イメージ説明](29d6be083acbe307210a60191ea11258.png)
24
24
 
25
25
  (※実際に作りたいデータでは、一覧データと同じフォーマットではなく、
26
26
 
@@ -36,7 +36,7 @@
36
36
 
37
37
  ⇒[USJ]シートの「国名」に「アメリカ」、「備考」に「留学した」
38
38
 
39
- ![イメージ説明](4b2528fa794369f84886e168fde1408d.png)
39
+ ![イメージ説明](e166abd9e6778df6c90267585a652a45.png)
40
40
 
41
41
  (例)略称が[CAN]の場合
42
42
 
@@ -122,9 +122,7 @@
122
122
 
123
123
  ```
124
124
 
125
- ![イメージ説明](c25e51acac5ac53abc4f25776c87f363.png)
125
+ ![イメージ説明](9a0fea69d7af33a5b73128f7c804e5e1.png)
126
-
127
-
128
126
 
129
127
 
130
128