[[20230414015210]]　『同じフォルダ内にあるブックのシートを集計シート』（きり）　ページの最後に飛ぶ

『同じフォルダ内にあるブックのシートを集計シートに値で転記するには』（きり）

複数のシートを串刺し計算したいのですが、転記元のブック内の数値が文字列で計算出来ません。
また、転記元のブックは1ヶ月×2種類で62、支店の数が24店あるので困っています。

現状↓

フォルダ内に複数のExcel(1.xlsx～31.xlsx)と、(商品別1.xlsx～商品別31.xlsx)と、月別集計.xlmsがあります。それぞれのExcelは数値が文字列となっています。

月別集計.xlmsには、“月別集計”、“1～31”と”商品別1～31”のシートがあり、同じ名前のシートにそれぞれ値で貼りたいです。

シートを新たに増やして貼り付けのやり方はみつけたのですが、“月別集計”シートに関数を入れているので、ブック名と同じ名前のシートに貼り付けたいのですが、見つかりませんでした。　
文字列の範囲を選んで数値に変換するのも時間がかかるので、どなたかVBAでの方法を
ご教授お願いします。

下記のものは他で見つけたのですが、貼り付けは出来たのですが、文字列のままでした。

Option Explicit
'
Sub Macro1()

    Dim FileName As String
'
    FileName = Dir(ThisWorkbook.Path & "\*.xlsx")
    Application.ScreenUpdating = False
'
    Do While FileName > ""
        Workbooks.Open ThisWorkbook.Path & "\" & FileName, False, True
        FileName = Replace(FileName, ".xlsx", "")
        Cells.Copy ThisWorkbook.Sheets(FileName).[A1]
        ActiveWorkbook.Close False
        FileName = Dir
    Loop
End Sub

あちこち調べてみて試したのですが、エラーばかり出てしまい困っています。
どのように組み込めば良いのか、どなたか教えてください。よろしくお願いいたします。

＜使用 Excel：Excel2021、使用 OS：Windows10 ＞

おはよ～ございますです。＾＾
１．複数ブック、複数シートを扱うには明示的に
　　ブック名、シート名を指定すると、間違いが少なくなるようですよ。
２．数字の文字列を数値に変えるのは、VAL、Clngとか。。。他諸々有るようですよ。
どんな数値を表す文字列なのかにもよりますけどね。(￣▽￣)
物によっては更にもう一工夫いる場合も有るかもしれませんね。
情報さえ一つに纏めることが出来るなら、後は、便利機能でも
ＶＢＡなら、クルクルっと！回して分岐すれば何とかなる物ですよ
(#^^#)でわ
m(__)m
（隠居Z） 2023/04/14(金) 07:13:41

おはようございます。＾＾
http://www.excel.studio-kazu.jp/kw/20110209184943.html
をご活用賜り
1.xlsx～31.xlsx)と、(商品別1.xlsx～商品別31.xlsx
と
ご希望の結果図、月別集計
の
詳細なシート構成
シート名、セル番地、が解るエクセル表形式で、更にご説明賜れば
何か、お手伝いくらいは、出来るかもしれません。←多分ですが。。。＠＠；
私が解らずとも、多数、アドバイスが有るかもしれませんね。(*^^*)ｖ
m(__)m
（隠居Z） 2023/04/14(金) 07:37:12

■１
＞下記のものは他で見つけたのですが
して、そのコードは理解できてますか？

■２
＞それぞれのExcelは数値が文字列となっています。
＞貼り付けは出来たのですが、文字列のままでした。
元データが【文字列】ですから、マクロは正しく動作してますよね。
元データの【文字列】がどういうものを指しているかわかりませんが、たとえば、valueプロパティを直接参照して転機してみては如何でしょうか。

■３
部分的に指摘があるところですが、対象のシートは明示的にされた方がよいとおもいます。

 Cells.Copy
    ↓のように解釈されている   
 ActiveSheet.Cells.Copy

さらに、「ActiveSheet.Cells」だと全セルが対象になりますから、UsedRangeに限定したほうがよいようにおもいます。
（データ次第かもですが）

■４
＞あちこち調べてみて試したのですが、エラーばかり出てしまい困っています。
具体的にコードを示して、エラー内容（発生箇所、エラー番号、エラーメッセージ）を提示頂ければ、アドバイスできることがあるかもしれません。

（もこな２） 2023/04/14(金) 08:56:15

すみません。誤字がありました。
以下の読み替えをしてください。

 転機 → 転記

（もこな２） 2023/04/14(金) 09:03:54

トピ主の反応がありませんが、「■２」「■３」は↓のようなことです。

    Sub Macro1_改()
        Dim FileName As String
        srcRNG As Range

        FileName = Dir(ThisWorkbook.Path & "\*.xlsx")
        Do While FileName > ""
            With Workbooks.Open(ThisWorkbook.Path & "\" & FileName, False, True)
                Set srcRNG = .Worksheets(1).UsedRange
                ThisWorkbook.Worksheets(Replace(.Name, ".xlsx", "")).Range(srcRNG.Address).Value = srcRNG.Value
                .Close False
                FileName = Dir()
            End With
        Loop
    End Sub

（もこな２） 2023/04/15(土) 09:16:53

隠居Zさま、もこな2さま

コメントありがとうございます。
連日睡眠不足と昨日は営業と会議があり、夜はあっさり落ちておりました。
本日も所用で見れていませんでした。
申し訳ございません。

コードの内容は、なんとなくですが分かっているつもりです。

試しに1～5.xlsxを.csvで保存し直して(コード内の.
xlsxも.csvに変更)貼り付けしたら集計はされました。

集計したい範囲はそれぞれcellのB4:R90です。
範囲を明確にした方が良いのですね。

ありがとうございます。

（きり） 2023/04/15(土) 21:18:39

もこな2さま

コードありがとうございました。
コンパイルエラーとなったので2行目の頭にDimをつけました。

月別集計シートのUsedRangeを取得して、取得範囲を明確化できるんですね。
cellを配列や番地で指定するしかできないと思ってました。

現在1～12までの xlsxしかなく、実行時エラー9となってしまいました。
月別集計.xlsmにシートが1ヶ月分用意されているからでしょうか。

（きり） 2023/04/15(土) 23:38:15

■５
＞コンパイルエラーとなったので2行目の頭にDimをつけました。
失礼しました。その修正でＯＫです。

■６
＞cellを配列や番地で指定するしかできないと思ってました。
ちょっと仰ってる意味がわかりません。
少なくとも提示されたコードは【（アクティブシートの）全セル】を指定してますよ。

＞集計したい範囲はそれぞれcellのB4:R90です
＞範囲を明確にした方が良いのですね。
どのように理解したのか不明ですが、論点は集計したい範囲ではなく【集計シートに転記したい範囲】です。
なので、各ブックの１番目のシートの「B4:R90」を転記したいのであれば、【（アクティブシートの）全セル】なんて指定をする必要ないですよね。

■７
＞現在1～12までの xlsxしかなく、実行時エラー9となってしまいました。
＞月別集計.xlsmにシートが1ヶ月分用意されているからでしょうか。
こちらの疑問点もよくわかりません。↓の意味は理解できてますか？

 ThisWorkbook.Worksheets(Replace(.Name, ".xlsx", ""))
                         ~~~~~~~~~~~~~~~~~~~~~~~~~~

なお、数字だけのブック名があるようなので、念のため↓に修正してください。

 ThisWorkbook.Worksheets(Replace(.Name, ".xlsx", ""))～～
                    ↓
 ThisWorkbook.Worksheets(Replace(.Name, ".xlsx", "") & "")～～

■８
＞試しに1～5.xlsxを.csvで保存し直して(コード内の.xlsxも.csvに変更)貼り付けしたら集計はされました。
いきなり別アプローチをぶっこまれましたが、そちらで行くなら、それはそれで効率的なアプローチがあるように思います。
現状のコードを示したうえで、疑問点など（詰まっているところ）仕切り直しをされてはどうでしょうか？

■９
上記に関連しますが、元データの【文字列】がどういうものを指しているかわかりませんので、元のアプローチで行くなら、セルの値がどうなっているのか説明してください。

 （別アプローチのほうで進めるなら無駄な議論になりそうなのでとりあえず不要です）

（もこな２） 2023/04/18(火) 07:22:06

コメント返信：

[ 一覧(最新更新順) ]

[[20230414015210]] 『同じフォルダ内にあるブックのシートを集計シート』（きり） ページの最後に飛ぶ

[[20230414015210]]　『同じフォルダ内にあるブックのシートを集計シート』（きり）　ページの最後に飛ぶ