初めてマクロを勉強しているものですが、
あるシートから別のシートへの複数コピーの仕方が分からないため
教えていただけますでしょうか。
###前提・実現したいこと
1、現在の状況としては「データ一覧」というデータの一覧(国名)を表にしたシートがあり、
以下のように「国名」、「略称」、「備考」のデータがあります。
2、そしてこの表の略称と一致するシートがあり、
それぞれ以下のようなフォーマットが用意されています。
(※実際に作りたいデータでは、一覧データと同じフォーマットではなく、
もう少し詳細内容が追加されています)
3、ここからが問題なのですが、やりたいこととしては一覧テーブルに「国名」と「備考」が入っているものは
それに対応するシートのフォーマットにも同じように「国名」と「備考」が表示されるようにしたいということです。
(例)略称が[USJ]の場合
⇒[USJ]シートの「国名」に「アメリカ」、「備考」に「留学した」
(例)略称が[CAN]の場合
⇒[CAN]シートの「国名」に「カナダ」、「備考」は空
このようにマクロを実行したときに一覧テーブルと同様のデータが略称の一致するシートにも
表示されるようにしたいです。
「データ一覧」の「略称」に対して「国名」と「備考」が入力されているときに、
「データ一覧」シートの「略称」と一致するシートを選択し、その「国名」と「備考」をコピーする
にはどのような処理が必要になってくるのでしょうか?
###試したこと
今現在、ここを参考にさせていただき勉強しているのですが、
何をどのように組み合わせていけばよいのかが分かりません。
なんだ!カンタン!Excel塾 やさしいエクセルマクロ講座 ExcelVBA
一番やりたいことを実現できそうなのはこのあたりかなとは思ったのですが、
ここからうまく発展できません。
生徒の成績記録簿を原本コピーで作る
イメージとしては[For Each]で回しながら[If]で「もし略称とシート名が一致するならそのシートを選択」、
さらに[If]を書いて「もしデータ一覧に国名、または略称、あるいは両方が入力されているなら、略称の一致するシートのB5に国名を入力し、B6に備考を入力」みたいな感じかな?と思っています。
ためしに備考だけでも出せないかと思い、上であげた生徒の成績記録簿を原本コピーで作るを参考に以下のようなものを書いてみたのですが(本来作成したいマクロではテンプレートのコピー等は行いません)、この場合は[略称]が[JPN]の「備考」の
「母国」が他の国の略称シートの備考に入力されてしましました。
lang
1Sub マクロテスト() 2 Dim 略称 As Range 3 Dim 備考 As Range 4 For Each 略称 In Worksheets("データ一覧").Range("B4:B8") 5 Worksheets("テンプレート").Copy After:=Worksheets(Worksheets.Count) 6 ActiveSheet.Name = 略称.Value 7 ActiveSheet.Range("C5") = 略称.Value 8 For Each 備考 In Worksheets("データ一覧").Range("C4:C8") 9 ActiveSheet.Range("D5") = 備考.Value 10 Next 備考 11 Next 略称 12End Sub
エクセルもろくに使いこなせない中、マクロを使わなければならなくなり勉強しているのですが、
何をどのように進めていけばよいのか分からず悩んでいます。
分からないことだらけで非常に申し訳ないのですが、よろしくお願いいたします。
###追記-その1
皆様ご回答いただきありがとうございます。
昨日に引き続きマクロを勉強しております。
皆様が教えてくださったおかげで完成までのイメージは掴むことができたのですが、
やはり1つ1つできることからやっていってみようと思い、
基本的なところから勉強をやり直しています。
そして今、1つのシートのセルの値をコピーして別のシートに貼り付ける
ということを試しているのですが、調べたとおりにやってみてもエラーが出てしまい
うまくいきません。
何がいけないのかお力添えいただけますでしょうか?
lang
1Sub セルコピー貼り付け_その1() 2' ワークシートの1つのセルの値をコピーして 3' 同じシートの別のセルに貼り付ける場合 4' 5 Worksheets("データ一覧").Range("B4").Copy 6 ActiveSheet.Range("B11").Paste 7End Sub 8
lang
1Sub セルコピー貼り付け_その2() 2' ワークシートの1つのセルの値をコピーして 3' 別のシートのセルに貼り付ける場合 4' 5 Worksheets("データ一覧").Range("B4").Copy 6 Worksheets("USA").Range("B11").Paste 7End Sub 8
ちなみに'Paste'メソッドをつけなくても貼り付けできるという記事をこちらで見たのですが、これも同じくエラーが出ています。
エラー内容は「実行時エラー'438' オブジェクトは、このプロパティまたはメソッドをサポートしていません」、「セルコピー貼り付け_その2()」となっています。
エクセルは2013を利用しています。
よろしくお願いいたします。
(追記1-1)
たびたび申し訳ございません。
こちらのマクロで試してみたところ、うまくコピペすることができました。
このように一個一個分けていかないとだめなのでしょうか?
lang
1Sub セルコピペ_その2() 2' 3' セルをコピーして別のシートに貼り付け 4' 5 Worksheets("データ一覧").Select 6 Range("A4").Select 7 Selection.Copy 8 Worksheets("USA").Select 9 Range("B5").Select 10 ActiveSheet.Paste 11End Sub
###追記-その2
あともう少しで目指しているマクロができそうなのですが、
またもやわからない部分があるため、追記させてください。
最初に提示させていただいた目標と多少変わったのですが、
「データ一覧」とその他のシートはこのように変更となっております。
「データ一覧」において、略称に対して国名が定義されているものは国名でシートが作成されており、
国名が定義されていないものは略称のままでシートが作成されています。
さらに、略称に対してはハイパーリンクを設定し、
略称と連動しているシートに飛べるようになっています。
最初は、処理の中でIf文を使って、「略称に対して国名がある場合は国名の付いたシートに飛んで値を出力。そうでないものは略称のシートに飛んで値を出力」ということを行っていました。
lang
1Sub for文_別のシートに値を出力_その1() 2' 「データ一覧」シートの「略称」の値を上から順番に取得して、 3' それぞれの略称名と一致するシートに国名と備考を出力 4 Dim 略称 As Range 5 Dim 略称取得用 As String 6 Dim 国名 As String 7 For Each 略称 In Worksheets("データ一覧").Range("B4:B8") 8 ' 国名が記述されていない場合 9 If 略称.Offset(0, -1).Value = 0 Then 10 ' String型の変数「略称取得用」にRange型の変数「略称」を代入 11 ' これにより「略称」と一致する名前を持つシートの選択が可能となる 12 略称取得用 = 略称 13 With Worksheets(略称取得用) 14 .Range("B5") = 略称.Offset(0, -1).Value 15 .Range("D5") = 略称.Offset(0, 1).Value 16 End With 17 ' 国名が記述されている場合 18 Else 19 国名 = 略称.Offset(0, -1) 20 With Worksheets(国名) 21 .Range("B5") = 略称.Offset(0, -1).Value 22 .Range("D5") = 略称.Offset(0, 1).Value 23 End With 24 End If 25 Next 略称 26End Sub
ですが、ハイパーリンクが設定されていることからもっと簡単に「リンク設定先のシートに値を出力する」
というようにできないか検証した結果、以下のようなマクロになりました。
lang
1Sub for文_別のシートに値を出力_その2() 2' ハイパーリンク先を取得してそのシートに「データ一覧」の「国名」と「備考」を出力 3' "Withステートメント"を使って簡略化 4 Dim 略称 As Range 5 For Each 略称 In Worksheets("データ一覧").Range("B4:B8") 6 略称.Hyperlinks(1).Follow 7 With ActiveSheet 8 .Range("B5") = 略称.Offset(0, -1).Value 9 .Range("D5") = 略称.Offset(0, 1).Value 10 End With 11 Next 略称 12End Sub
これによりやりたいことはほぼ実現できたのですが、
現在の状況としてはセルの範囲を固定しています。
これを「値のある範囲まで」"For Each文"で検索するにはどのようにしたらよろしいでしょうか?
結果としては【B4~B8】までの略称が入っているところまでということで同じになるとは思うのですが、
今後データが追加された際、そのたびにマクロの適用される範囲を修正する必要があるため、
ここを動的に処理してくれるようにしたいです。
これらを参考にマクロを以下のように書き換えてみたのですが、
こうすると[略称.Hyperlinks(1).Follow]の位置で
「インデックスが有効範囲にありません」とエラーが起きてしまいます。
lang
1Sub for文を使った値の取得() 2' ハイパーリンク先を取得してそのシートに「データ一覧」の「国名」と「備考」を出力 3' "Withステートメント"を使って簡略化 4 Dim 略称 As Range 5 ' セルの範囲を値のある範囲までとしたい 6 For Each 略称 In Worksheets("データ一覧").Range("B4", Cells(Rows.Count, 1).End(xlUp)) 7 略称.Hyperlinks(1).Follow 8 With ActiveSheet 9 .Range("B5") = 略称.Offset(0, -1).Value 10 .Range("D5") = 略称.Offset(0, 1).Value 11 End With 12 Next 略称 13End Sub
デバッグを行うと[Cells(Rows.Count, 1)]のところが[Rows.Count=1048576]で、
[End(xlUp)]が[xlUp=-4162]となっているようで、明らかに値があるところ以外を見てしまっています。
そのため上にあげたエラーが出てしまっているのかと思いますが、
どのようにして解決したらよろしいでしょうか?
よろしくお願いいたします。
回答6件
あなたの回答
tips
プレビュー
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2016/08/01 07:38 編集