Excel VBA 複数のブックの複数シートからデータをコピーするマクロを作成したのですが、
コピー部分が上手く作動せず、訳の解らないデータが貼り付いてしまいます。
ちゃんとファイルは読み込んでいます。
、
VBA超初心者なので、うまく動作しませんでした。
どこが悪いのか教えて頂けないでしょうか。
やりたいことは
・同一フォルダ内に複数のExcelファイルがある
・各ファイル内には複数のシートがあり、シート数はファイル毎にばらばら
・各シートの構造は全て同じ
・VBAを保存してあるExcel(貼り付け用.xls)も同じフォルダに置いて作業します
・JEまとめ.ファイルの原本シートをコピーして、シート名に日付を入れたシートに
・全シート下記のセルのコピー(値、縦横変換)を日付をいれた原本コピーのシートに
B1~3セル→A~C
B4~5セル→G~H
B6セル→J
J22~24→D~F
をシートの一番最初の行は見出しなので、その後上から順に値の貼付けをしたいのです。
エクセルは2003です。
宜しくお願いいたします。
Sub 集計コピー操作()
Dim 集 As Workbook, 開 As Workbook
Dim 原 As Worksheet, コピー As Worksheet
Dim パス As String, フォルダ As String
Dim 日付 As String
Dim 紙 As Integer
Dim 終 As Integer
Dim 数 As Long '書込み行
'日付取得
日付 = Format(Date, "yyyymmdd")
'新規シート追加
'シート名チェック
Set 集 = ThisWorkbook
For Each 原 In 集.Worksheets
If 原.Name = 日付 Then
原.Activate
Exit For
End If
Next 原
'シート作成
If 原 Is Nothing Then
'シート名が存在しない場合は作成
Sheets("原本").Copy Before:=Sheets(1)
Set 原 = ActiveSheet
原.Name = 日付
Else
End If
'Application.ScreenUpdating = False '画面ちらつき防止
'ファイル名設定
Set 集 = ThisWorkbook 'このbookをまとめとする。
パス = ThisWorkbook.Path 'このbookのパスを取得
フォルダ = Dir(パス & "\*.xls") 'フォルダ内のExcelブックを検索
Do Until フォルダ = Empty '全て検索
If フォルダ <> 集.Name Then 'book名がこのbookの名前でなければ
'コピーブックの設定
Set 開 = Workbooks.Open(パス & "\" & フォルダ) '開ファイルとする。
紙 = Worksheets.Count 'シートカウント
For 終 = 1 To 紙
数 = 数 + 1
'シートループ処理
For Each コピー In 開.Worksheets
With WorksheetFunction
原.Cells(数, "A").Value = コピー.Range("B1").Value
原.Cells(数, "B").Value = コピー.Range("B2").Value
原.Cells(数, "C").Value = コピー.Range("B3").Value
原.Cells(数, "G").Value = コピー.Range("B4").Value
原.Cells(数, "H").Value = コピー.Range("B5").Value
原.Cells(数, "J").Value = コピー.Range("B6").Value
原.Cells(数, "D").Value = コピー.Range("J22").Value
原.Cells(数, "E").Value = コピー.Range("J23").Value
原.Cells(数, "F").Value = コピー.Range("J24").Value
End With
Next
Next
'ブッククローズ処理
開.Close (False) '保存せずに閉じる
End If
フォルダ = Dir 'フォルダ内の次のbookを検索
Loop
Application.ScreenUpdating = True '画面ちらつき防止を解除
MsgBox 紙 & "件のファイルをコピーしました。"
End Sub
No.2
- 回答日時:
おそらく"For 終" のループは要りません。
-------------------------------------
For 終 = 1 To 紙
数 = 数 + 1
'シートループ処理
For Each コピー In 開.Worksheets
--------------------------------------
ここはこうしたかったんじゃないかと思います。
--------------------------------------
'シートループ処理
数 = 1
For Each コピー In 開.Worksheets
数 = 数 + 1
--------------------------------------
それと、実行時に当日のシートが既にあった場合は使いまわしていますが、シート内容をクリアしなくても大丈夫でしょうか。
一度実行した後、フォルダ内のxlsファイルを減らしてから再実行すると前の結果が一部残ってしまうと思います。
この回答への補足
7981785さま
アドバイスありがとうございます。
アドバイスにしたがって色々模索した結果、何とか使えるようになりました。
ありがとうございました。
ただ、まだいくつかの問題は残っていて、その部分について追加マクロをいただけないでしょうか。
問題は3つです。
1.ご指摘のシートデータクリアなのですが、エラーになってしまうため、はずしたのですが、出来れば入れたいのです。何がダメなのでしょうか。
'存在する場合はシートデータクリア
原.Cells(2,1).Rsize(Rows.Count-1,Columns.Count).ClearContents
2.いくつか除きたいシートをはずすマクロを作成したのですが、これはスルーされてしまいます。
If コピー.Name<>"はじめに" Or コピー.Name<>"TAB" Then
また、シートどうやらもう一つ非表示(別表1)があり、全部で3つのシートを除きたいです。
どんなマクロを使ったらよいのでしょうか。
3.ファイルですが、どうやら開いて作業しているThisWorkbookを再度読み込んでしまうので(メッセージが出ます)、エラーになっているので、何とかしたいのです。
同じフォルダーに入っている除きたいファイルは2つあります。
集ファイル(ThisWorkbook)とjeという名前のファイルです。
何卒、アドバイス宜しくお願いいたします。
木村
No.3
- 回答日時:
> 1.ご指摘のシートデータクリアなのですが、エラーになってしまうため、
> 原.Cells(2,1).Rsize(Rows.Count-1,Columns.Count).ClearContents
Rsize でなく Resize です。
それから、混乱を避らすため"原."を付けるかどうか統一しておいた方がいいと思います。
Cells(2,1).Rsize(Rows.Count-1,Columns.Count).ClearContents
にするか、
原.Cells(2,1).Rsize(原.Rows.Count-1,原.Columns.Count).ClearContents
にするか。
> 2.いくつか除きたいシートをはずすマクロを作成したのですが、これはスルーされてしまいます。
>If コピー.Name<>"はじめに" Or コピー.Name<>"TAB" Then
この場合<>を使うならOrでなくAndです。
If コピー.Name<>"はじめに" And コピー.Name<>"TAB" Then
> また、シートどうやらもう一つ非表示(別表1)があり、全部で3つのシートを除きたいです。
表示しているシートでも非表示のシートでもやり方は同じです。
他の2つの条件に
And コピー.Name<>"別表1"
を付け足したらいいです。
> 3.ファイルですが、どうやら開いて作業しているThisWorkbookを再度読み込んでしまうので
もとの質問にある、
> If フォルダ <> 集.Name Then 'book名がこのbookの名前でなければ
これで集ファイルが除けるはずなのですがうまくいきませんか?
条件を増やしたらおかしくなったということでしょうか。
JEも除くのはシート名の判定と同じくJEのファイル名を条件に付け足すだけです。
If フォルダ <> 集.Name And フォルダ <> "JEまとめ.xls" Then
それからNo2でうっかりForの直前に
数 = 1
を書いてますが、もう場所を直してもらってますかね。
Do Until より上にしてないとブックごとに"数"がリセットされてうまくいってないと思います。
この回答への補足
色々とアドバイスありがとうございます。
とっても助かりました。
問題なく動きました。
また、別の質問なのですが、このマクロを利用して(条件は同じ)今度は列単位のコピーをしたいのですが、下記のように変更するだけでは動かないのです。
何度もすみませんが、また、アドバイスいただけるとうれしいです。
With WorksheetFunction
原.Cells(数, "H").Value = コピー.Range("B1:B6").Value
原.Cells(数, "H").Value = コピー.Range("B10:B20").Value
End With
No.4ベストアンサー
- 回答日時:
回答が遅くなりました。
もう自己解決されてるかもしれませんが、一応。
> 下記のように変更するだけでは動かないのです。
> 原.Cells(数, "H").Value = コピー.Range("B1:B6").Value
6個の値をどのように入れようとされているのか分かりませんが、もし元と同じ並びのまま入れるのなら、行数・列数をもとの範囲と合わせる必要があります(上記の例だと"B1:B6"なので6行1列)。
コードの書き方は色々でしょうが、上記例に近い形を取るなら
原.Cells(数, "H").Resize(6, 1).Value = コピー.Range("B1:B6").Value
など。
もし縦横を入れ替える必要があるのなら、この書き方ではできません。
複数を一度に代入するのはあきらめてもとの質問のコードのように一つずつ入れていくか、次のようにコピー&ペーストの機能を利用して縦横を入れ替えるのが楽だと思います。
コピー.Range("B1:B6").Copy
原.Cells(数, "H").Resize(1, 6).PasteSpecial Paste:=xlPasteValues, Transpose:=True
ここで、"Transpose"が縦横入れ替えのオプションです。
本当に色々とアドバイス下さり、
ありがとうございました。
とっても助かりました。
また、何かございましたら、宜しくお願いいたします。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) 【ご教示ください】VBAの記述方法がわかりません。 2 2022/08/12 21:28
- Visual Basic(VBA) 【前回の続き続きです、ご教示ください】VBAの記述方法がわかりません。 2 2022/08/24 20:49
- Visual Basic(VBA) 【前回の続きです、ご教示ください】VBAの記述方法がわかりません。 2 2022/08/16 16:44
- Visual Basic(VBA) まとめシートから集計シートへA列のコードが一致したら1行コピーするマクロをネット上で見つけました。こ 1 2022/08/30 14:11
- Visual Basic(VBA) 特定の文字を含むシートだけマクロ処理をしたい 1 2023/05/22 01:43
- Excel(エクセル) Excel VBAどこが間違ってますか? 4 2023/07/17 10:04
- Visual Basic(VBA) このVBAでExcelアプリケーションを作成は必要ですか? 3 2023/07/19 21:13
- Visual Basic(VBA) 他のシートからコピーする下記マクロで貼付け位置をWorksheets(1).Range("A3")の 8 2023/01/30 18:48
- Visual Basic(VBA) VBAのユーザーフォームのテキストボックスに入力制限をしたい 6 2022/11/15 08:28
- Visual Basic(VBA) 貼り付けた値が消えていく 以下はソースファイルの2番目のシートのB6から最終行を取得 ターゲットファ 2 2023/07/27 12:23
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Excel 関数を使ってデータと一...
-
エクセルの選択範囲以外を削除...
-
EXCELのVBAでシートコピーをし...
-
【Excel VBA】シートコピー時、...
-
EXCELで別のブックから式をコピ...
-
Excelの行をコピーして貼り付け...
-
エクセルの1シートの内容を複...
-
【エクセル】プルダウン設定の...
-
Excel シートのコピーの際、ペ...
-
ワークシートを別ファイルにコ...
-
ExcelVBAで、ユーザーフォーム...
-
ExcelのVBAで、application.inp...
-
PDFファイルをコピーしてエクセ...
-
excel表のコピー時、列・行幅も...
-
ExcelのBook内のシートを一枚だ...
-
【Excel】数式をそのまま他のシ...
-
エクセルのワークシートをUSBメ...
-
【VBA】コピー&複数個所のペー...
-
EXCELファイルをコピーすると終...
-
エクセルマクロで上書きして貼...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルの選択範囲以外を削除...
-
Excel 関数を使ってデータと一...
-
EXCELのVBAでシートコピーをし...
-
EXCELで別のブックから式をコピ...
-
【Excel】数式をそのまま他のシ...
-
【VBA】コピー&複数個所のペー...
-
Excelの行をコピーして貼り付け...
-
【エクセル】プルダウン設定の...
-
エクセルのワークシートをUSBメ...
-
Excel シートのコピーの際、ペ...
-
エクセルの1シートの内容を複...
-
エクセルVBA 1行飛ばしで転記す...
-
【Excel VBA】シートコピー時、...
-
エクセルシートを別のエクセル...
-
ExcelVBAで、ユーザーフォーム...
-
エクセルでシートを「移動また...
-
Excel シートに別のExcelシート...
-
Excel 数式の保護をしたセルを...
-
EXCEL2007でシートをコピーする...
-
シートが保護されていないのに...
おすすめ情報