複数ブックから一つのシートにデータを転記する際に順序をつける方法

都道府県ごとのブックにバラけたトマトの作付面積のデータを、一つのシートにまとめようと試みています。
転記元のブックは「1北海道」、「2青森」・・・「47沖縄」という順番で単一フォルダ内に並んでおります。
このフォルダ内の順番の通りにシートに転記したいのですが、
以下のマクロ（あるサイト様から拝借しました）を用いて転記すると順番がめちゃくちゃになってしまいます。
何か改善策はないものでしょうか？お詳しい方、どうぞよろしくお願い申し上げます。



転記に用いたマクロ

記

Sub ブックの転記()
Dim myDir As String, myName As String, myBook As Workbook
Dim motodata As Range, sakidata As Range

Dim 転記先 As Worksheet, 転記元 As Worksheet

Set 転記先 = ThisWorkbook.ActiveSheet

Application.ScreenUpdating = False

'集計用のブックがあるフォルダ名を指定
myDir = "C:\Users\Desktop\野菜データ"
myName = Dir(myDir & "\" & "*.xls")

Do While myName <> ""
'↓転記先の最新レコード位置を取得する
Set sakidata = 転記先.Range("XFD2").End(xlToLeft).Offset(0, 1)
'↓(1)指定した名前のブックを開いて変数に格納する
Set myBook = Workbooks.Open(Filename:=myDir & "\" & myName)
'↓(2)転記元を取得する。

Set 転記元 = myBook.ActiveSheet

Set motodata = 転記元.Range("A8:A47")
'↓(3)転記先に貼り付ける
motodata.Copy sakidata
'↓(4)開いたブックを閉じる
myBook.Close

myName = Dir()

Loop
End Sub


以上

No.2 ベストアンサー 回答者： tatsu99 回答日時： 2016/07/22 13:05

とりあえず、今のソースをそのまま使用したい場合は、ファイル名を以下のように変えて下さい。

1北海道.xls -> 01北海道.xls
2青森.xls -> 02青森.xls
・・・途中省略
9XX.xls -> 09XX.xls

上記のように、1～9を01～09に変えてください。
その後、今のマクロを実行してください。

もし、上記の名前の変更を手作業で毎回するのがいやな場合は、以下のようなバッチファイル
を作ってください。（ファイル名をchange.batとします）
------------------------------------
rename 1北海道.xls 01北海道.xls
rename 2青森.xls 02青森.xls
・・・途中省略
rename 9XX.xls 09XX.xls
------------------------------------
このファイルをexcelファイルがあるフォルダにおきます。
コマンドプロンプトを呼び出し、このフォルダに移動します。
そこで、change.batと入力するとファイル名が変更されます。

もし、ファイル名を変更せずに行ないたい場合は、以下のようにします。
新しいシートを作成し、A列に都道府県毎のファイル名を記入します。
A1->1北海道
A2->2青森
・・途中省略
A47->47沖縄

このA1～A47までのセルの内容に拡張子(.xls)を付加してファイルを読み込むようにします。
このやり方を望まれる場合は、その旨補足してください。

この回答へのお礼

お返事が遅れまして、申し訳ありませんでした。

大変丁寧にご教示いただき、誠にありがとうございました。
ファイルの名前を変えてやってみます。
後々のことをちゃんと考えて名付けるべき、ということですね。

ご協力に感謝申し上げます。

お礼日時：2016/07/24 14:45

No.3 回答者： fujillin 回答日時： 2016/07/22 19:11

ANo1です。

まったくトンチンカンな回答をしてしまいました。
申し訳ありませんが、ANo1は無視してください。

お騒がせしてすみません。m(__)m

この回答へのお礼

お返事が遅れてしまい、申し訳ありません。
ご丁寧に教えていただけただけで、大変ありがたく存じます。
このたびは、お手数をおかけいたしました。

お礼日時：2016/07/24 14:41

No.1 回答者： fujillin 回答日時： 2016/07/21 17:08

こんにちは

ループの処理順序が
　＞　Dir(myDir & "\" & "*.xls")
で示されている様に、.xlsファイルを指定してはいますが、順序等はシステムに任せているので、それなりの順になってしまうものと思います。

ご提示の「1北海道」、「2青森」・・・「47沖縄」の順序でよいのなら、そのままファイル名の先頭の番号を利用して明示的に順序を制御してあげれば宜しいかと思います。
例えば以下のような感じです。

For n = 1 To 47
　myName = Dir(myDir & "\" & n & "*.xls")

　' コピーの処理などをここに入れる

Next n