teratail
回答率
85.48%
質問するログイン新規登録
トップVBAに関する質問共有サーバー内にある一覧表へ複数のファイルから転記する

編集履歴

回答編集履歴

2

質問①の回答を追記

2018/09/29 12:05

投稿

退会済みユーザー
test CHANGED
@@ -1,4 +1,20 @@
1
+ > ①Bの集約管理ファイルを閉じていると開くのですが、開いていたらメッセージボックスは表示されず「ファイルが見つかりません。ファイル名保存場所が正しいか・・」と出ます。
2
+
3
+
4
+
5
+ とりあえず既に開いている場合に閉じる処理が無いのが問題ですね。
6
+
7
+
8
+
9
+ > ②ActiveSheet.PasteSpecialから進めなくなりました。
10
+
11
+
12
+
1
- 今回のエラーの原因は本来Range.PasteSpecialにしか存在しないパラメータ「Paste:=」をWorksheet.PasteSpecialに対して使用しているためですね。
13
+ このエラーの原因は本来Range.PasteSpecialにしか存在しないパラメータ「Paste:=」をWorksheet.PasteSpecialに対して使用しているためですね。
14
+
15
+ どちらも同じ名称のメソッドですから、編集の過程で変えてしまったのでしょう。
16
+
17
+ 本件はコピー＆ペーストが必要ない事例ですが、そこが質問のキモなのでそのまま残しています。
2
18
 
3
19
 
4
20
 
@@ -8,53 +24,57 @@
8
24
 
9
25
  1.DisplayAlertsの位置が良くない。
10
26
 
11
- 　この手のスイッチは必要最小限な範囲に使うように心がけたほうが良いです。
27
+ この手のスイッチは必要最小限な範囲に使うように心がけたほうが良いです。
12
-
28
+
13
- 例えば今回の場合はOpen時の様々な警告を無視するためだと思いますのでOpenだけに適用しています。
29
+ 例えば今回の場合はOpen時の様々な警告を無視するためだと思いますのでOpenだけに適用しています。
14
30
 
15
31
 
16
32
 
17
33
  2.Withの使い方がおかしい。
18
34
 
19
- WithステートメントはWorkbook、Worksheet、Rangeなどの変数の代替するためのものです。
35
+ WithステートメントはWorkbook、Worksheet、Rangeなどの変数の代替するためのものです。
20
-
36
+
21
- 利用するにはドットから初めなければなりませんが、提示されたコードではドットが付いていません。
37
+ 利用するにはドットから初めなければなりませんが、提示されたコードではドットが付いていません。
22
38
 
23
39
 
24
40
 
25
41
  3.極力ActiveSheetや省略Cellsは使わないように。
26
42
 
27
- Withが上手く使えていないのが原因で、「Cells(x, 2).Value = "*"」などはActiveSheetが省略されたものとして動作します。※プログラムを標準モジュールに書いた場合
43
+ Withが上手く使えていないのが原因で、「Cells(x, 2).Value = "*"」などはActiveSheetが省略されたものとして動作します。
44
+
28
-
45
+ ※プログラムを標準モジュールに書いた場合
46
+
29
- 従って、前に記載されている「Sheets("list").Activate」の影響でlistに対して処理することになり、意図したように動作しないはずです。
47
+ 従って、前に記載されている「Sheets("list").Activate」の影響でlistに対して処理することになり、意図したように動作しないはずです。
30
-
48
+
31
- 基本的にシートオブジェクトは指定するようにしてください。
49
+ 基本的にシートオブジェクトは指定するようにしてください。
32
-
50
+
51
+
52
+
33
- 　たとえば、せっかく「Set wS = wB.Worksheets("list")」としているのに、すぐ下でActiveSheet.PasteSpecialとしていますよね。
53
+ たとえば、せっかく「Set wS = wB.Worksheets("list")」としているのに、すぐ下でActiveSheet.PasteSpecialとしていますよね。
34
-
54
+
35
- 　同じオブジェクトを指すのですからwSを使いましょう。
55
+ 同じオブジェクトを指すのですからwSを使いましょう。
36
-
56
+
37
- 　今回の場合、Open直後に「Sheets("list").Activate」を実行しているので、この時点で変数に代入しておけばOKです。
57
+ 今回の場合、Open直後に「Sheets("list").Activate」を実行しているので、この時点で変数に代入しておけばOKです。
38
-
39
-
40
-
58
+
59
+
60
+
41
- 4.インデントは手を抜かないこと。
61
+ 4.インデントに手を抜かないこと。
42
-
62
+
43
- コードが読みづらくなるということは、ミスに発見しずらくなります。
63
+ コードが読みづらくなるということは、ミスを発見しずらくなります。
44
-
64
+
45
- 面倒でもインデント（TAB）はきれいに整えましょう。
65
+ 面倒でもインデント（TAB）はきれいに整えましょう。
46
-
47
-
48
-
66
+
67
+
68
+
49
- 5.処理中のRange.Selectは使わないこと。
69
+ 5.処理中にRange.Selectは使わないこと。
50
-
70
+
51
- 　下記のコードを見て分かるように、正しく書けばSelectは必要ありません。
71
+ 下記のコードを見て分かるように、正しく書けばSelectは必要ありません。
52
-
72
+
53
- 　もしアクティブブック・シートが切り替わった状態でSelectするとエラーの原因となりますので、どうしても使うときはブックやシートもActiveにしましょう。
73
+ もしアクティブブック・シートが切り替わった状態でSelectするとエラーの原因となりますので、どうしても使うときはブックやシートもActiveにしましょう。
54
-
74
+
55
- 　本来はマクロの最後に、マクロ実行後に選択していてほしい箇所をSelectするような目的で使うのが正しい使い方です。
75
+ マクロの最後に、マクロ実行後に選択していてほしい箇所をSelectするような目的で使うのが正しい使い方です。
56
-
76
+
57
- 　とはいえ、デバッグのときは処理工程でSelectしてくれたほうが、わかりやすくて便利なので一応コメントで残しています。
77
+ とはいえ、デバッグのときは処理工程でSelectしてくれたほうが、わかりやすくて便利なので一応コメントで残しています。
58
78
 
59
79
 
60
80
 
@@ -78,8 +98,6 @@
78
98
 
79
99
  '集約管理（転記先）のブックを開く
80
100
 
81
-
82
-
83
101
  myPath = "共有サーバー\管理状況\"
84
102
 
85
103
  fN = "【共有】集約管理" & Range("D1").Value & "月" & ".xlsx"
@@ -92,6 +110,22 @@
92
110
 
93
111
 
94
112
 
113
+ '既にfNを開いていたら閉じる
114
+
115
+ On Error Resume Next
116
+
117
+ Set wB = Workbooks(fN)
118
+
119
+ On Error GoTo 0
120
+
121
+ If Not wB Is Nothing Then
122
+
123
+ wB.Close False
124
+
125
+ End If
126
+
127
+
128
+
95
129
  Application.DisplayAlerts = False
96
130
 
97
131
  Set wB = Workbooks.Open(Filename:=myPath & fN, Notify:=False)

1

文章の改定

2018/09/29 12:04

投稿

退会済みユーザー
test CHANGED
@@ -1,3 +1,11 @@
1
+ 今回のエラーの原因は本来Range.PasteSpecialにしか存在しないパラメータ「Paste:=」をWorksheet.PasteSpecialに対して使用しているためですね。
2
+
3
+
4
+
5
+ しかし、色々とバグの原因が混ざっているので順番に指摘していきます。
6
+
7
+
8
+
1
9
  1.DisplayAlertsの位置が良くない。
2
10
 
3
11
  　この手のスイッチは必要最小限な範囲に使うように心がけたほうが良いです。
@@ -22,6 +30,12 @@
22
30
 
23
31
  基本的にシートオブジェクトは指定するようにしてください。
24
32
 
33
+ 　たとえば、せっかく「Set wS = wB.Worksheets("list")」としているのに、すぐ下でActiveSheet.PasteSpecialとしていますよね。
34
+
35
+ 　同じオブジェクトを指すのですからwSを使いましょう。
36
+
37
+ 　今回の場合、Open直後に「Sheets("list").Activate」を実行しているので、この時点で変数に代入しておけばOKです。
38
+
25
39
 
26
40
 
27
41
  4.インデントは手を抜かないこと。
@@ -32,6 +46,18 @@
32
46
 
33
47
 
34
48
 
49
+ 5.処理中のRange.Selectは使わないこと。
50
+
51
+ 　下記のコードを見て分かるように、正しく書けばSelectは必要ありません。
52
+
53
+ 　もしアクティブブック・シートが切り替わった状態でSelectするとエラーの原因となりますので、どうしても使うときはブックやシートもActiveにしましょう。
54
+
55
+ 　本来はマクロの最後に、マクロ実行後に選択していてほしい箇所をSelectするような目的で使うのが正しい使い方です。
56
+
57
+ 　とはいえ、デバッグのときは処理工程でSelectしてくれたほうが、わかりやすくて便利なので一応コメントで残しています。
58
+
59
+
60
+
35
61
  できるだけ構造を変えないように修正したものがこちらです。
36
62
 
37
63
  一応ローカル環境でデバッグしたので動くと思いますが、ご自分でも良くデバッグしてください。
@@ -40,7 +66,7 @@
40
66
 
41
67
  Sub 転記()
42
68
 
43
- Dim wB1 As Workbook, wS1 As Worksheet
69
+ Dim wB As Workbook, wS As Worksheet
44
70
 
45
71
  Dim myPath As String, fN As String
46
72
 
@@ -68,29 +94,29 @@
68
94
 
69
95
  Application.DisplayAlerts = False
70
96
 
71
- Set wB1 = Workbooks.Open(Filename:=myPath & fN, Notify:=False)
97
+ Set wB = Workbooks.Open(Filename:=myPath & fN, Notify:=False)
72
98
 
73
99
  Application.DisplayAlerts = True
74
100
 
75
101
 
76
102
 
77
- If wB1.ReadOnly Then
103
+ If wB.ReadOnly Then
78
104
 
79
105
  MsgBox "他の人が作業中です。しばらく経ってから転記し直してください。"
80
106
 
81
- wB1.Close False
107
+ wB.Close False
82
108
 
83
109
  Exit Sub
84
110
 
85
111
  Else
86
112
 
87
- Set wS1 = wB1.Sheets("list")
113
+ Set wS = wB.Sheets("list")
88
-
114
+
89
- wB1.Activate '←普通は必要ないが、一応。
115
+ wB.Activate '←普通は必要ないが、一応。
90
-
116
+
91
- wS1.Activate
117
+ wS.Activate
92
-
118
+
93
- wS1.Range("A1") = Now
119
+ wS.Range("A1") = Now
94
120
 
95
121
  End If
96
122
 
@@ -116,9 +142,13 @@
116
142
 
117
143
  '----B列の最終ひとつ下を選択してから値として貼り付け
118
144
 
119
- With wS1.Cells(wS1.Rows.Count, 2).End(xlUp).Offset(1, 0)
145
+ With wS.Cells(wS.Rows.Count, 2).End(xlUp).Offset(1, 0)
146
+
120
-
147
+ 'wB.Activate '←Selectを使いたいときはコレも書いたほうが安全
148
+
149
+ 'wS.Select '←Selectをｒｙ
150
+
121
- .Select
151
+ '.Select '←省略可
122
152
 
123
153
  .PasteSpecial Paste:=xlPasteValues, _
124
154
 
@@ -138,13 +168,23 @@
138
168
 
139
169
 
140
170
 
171
+ 'データの末尾の次の行を選択
172
+
173
+ wB.Activate
174
+
175
+ wS.Select
176
+
177
+ wS.Cells(wS.Rows.Count, 2).End(xlUp).Offset(1, 0).Select
178
+
179
+
180
+
141
181
  Application.DisplayAlerts = False
142
182
 
143
- wB1.Save
183
+ wB.Save
144
184
 
145
185
  Application.DisplayAlerts = True
146
186
 
147
- wB1.Close False '----元のファイルを保存し閉じる
187
+ wB.Close False '----元のファイルを保存し閉じる
148
188
 
149
189
 
150
190
 
@@ -154,6 +194,8 @@
154
194
 
155
195
  Workbooks.Open Filename:=myPath & fN, ReadOnly:=True '----読み取り専用で開く
156
196
 
197
+
198
+
157
199
  End Sub
158
200
 
159
201
  ```