[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『偶数（または）奇数シートを選択コピーし、新規ブックに貼り付けたい』（えま）
こんばんは。
いつもお世話になっています。
フォルダの中にブックが11個あり、各ブックにはデータの入ったシートが20枚あります。
各シートのうち、数列に数式が入っています。
現在、作業の一つとして
1．ブックを1個ずつ開き、各シートを選択、コピー、値の貼り付け（全て手作業）
2．1．で値の貼り付けをしたブックの偶数シートを選択、コピーを作成し、
新規ブックに貼り付け（全て手作業）
3．2．で作成した11個の新規ブックを一つにまとめる
#「統合ブックメーカー｣を利用させてもらっています。
http://www.vector.co.jp/soft/win95/business/se398736.html
4．一つのブックにまとまった220枚のシートを同じブック内の
「まとめ」シートにまとめる
# Mookさんに教えていただいたサンプルを使わせてもらっています。
[[20120428220856]]
（2．で奇数シートを選択し、4まで進むという作業もします）
Mookさんにプログラムの書き方を教えてもらい、マクロの便利さを初めて知りました。
そこで、1．の作業もマクロでできるのではないかと思い、試行錯誤した揚句、
下記のプログラムを書きました。
Sub 値の貼り付け()
Dim ws As Worksheet
Worksheets.Select
Cells.Select
For Each ws In ThisWorkbook.Worksheets
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Next
End Sub
2．の作業もマクロでできるのではないかと、いろいろと調べてみましたが、
こちらはわかりませんでした。
ご教示いただけると嬉しいです。
よろしくお願いします。
Excel 2007
WindowsXP
ご自身でいろいろと工夫して試したりしているのは良いですね。
ですが、ちょっとだけ上のコードが気になりました。
Sub 値の貼り付け()
Dim ws As Worksheet
Worksheets.Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
End Sub
だけでも、全シートが値になっていませんか？
全シートを選んで一括で全シートの処理って出来るとは思ってませんでしたので、 私もいつもシート単位で処理をしていましたが、その場合は
Sub 値の貼り付け()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Next
End Sub
や、Select をしないで
Sub 値の貼り付け()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Cells.Copy
ws.Cells.PasteSpecial Paste:=xlPasteValues
Next
End Sub
となります。ですが、一括でできるのであれば、最初のコードでよいと思います。 For 文はシート数だけコピーを繰り返しているので、今回は For を使う必要がなかった ということですね。 ws でシート分だけ繰り返してますけれど、ws を指定していないので、結局全体の コピー処理がシート数回繰り返されています。
でも、長年マクロ使ってますけれど、こういう発想はありませんでした。 （頭が固くなっているっていう事ですね）
話が質問に戻りますが、最後のファイルを作るのが目的だと思いますが、 １〜４で作成する途中の個々の処理をしたファイルは必要ですか？ また、現在はマクロを個々のファイルに書いて処理しているのでしょうか？
頻繁にある作業であれば、個人ブックに書いておいてフォルダ単位で処理をする ということもできますが、前回4シート目から処理をするといっていたので、３の処理の 後になにかシートを付けていたでしょうか？
少しこのあたりを説明してもらえると、全体的な処理をするマクロが作れると思います。 もちろん、全体ではなく質問してもらった内容にフォーカスして回答した方が良ければ その点に対して回答できますので、そのように言ってください。 (Mook)
ご自身でいろいろと工夫して試したりしているのは良いですね。
Mookさんに褒められると素直にうれしいです。
ありがとうございます*^^*
ですが、ちょっとだけ上のコードが気になりました。
Forがなくても全シートが値になりました!
Worksheets.Celectで全ワークシートを選択するので、Forは必要ないのですね。
ForとNextがなくなるとすっきりしますね^^
以下に私が今取り組んでいる作業をMookさんの問いに答える形で
出来るだけ具体的に書いてみました。
１〜４で作成する途中の個々の処理をしたファイルは必要ですか？
受け取ったフォルダは元ファイルとして別に保存しますので、個々の処理をしたファイルは必要ないです。
話が質問に戻りますが、最後のファイルを作るのが目的だと思いますが、
4.には続きの作業がありまして、奇数ファイルの最終形はサーバーへアップロードするための
@@@ファイル、偶数ファイルの最終形は解析レポートに使う表とグラフの元データになります。
#特定の業界が使う用語を@@@に充てているみたいですので、伏せています。
偶数、奇数シートはペアになっていまして、偶数シートにはお客様が入力したデータ
奇数ファイルには偶数ファイルの中からサーバーへアップする際に必要なデータが参照されています。
#サーバーへデータをアップした後、統計処理が行われます。
私が担当する作業の流れは、
a)お客様が入力した偶数シートに記入ミス、漏れがないかをチェックし、
b)奇数シートのデータを範囲指定してcsvファイルにし、ファイル名をお客様が入力した装置コードに手作業で書き換えます。
これを１１０回繰り返し、（すみません上の4．は110の間違いです）
c)出来上がったcsvファイルを@@@ファイルに変換します。
（エクセルから直接@@@ファイルにならないので、
一旦csvファイルに変換するようにと上司から指示を受けました。）
#ファイル名を一括変換できるフリーソフトを利用しました。
d)お客様に提出する解析レポートに添付する、偶数ファイルのデータをもとにした表とグラフを作成します。
今回質問させてもらっている1．〜4．の作業はa)でしています。
偶数シートにはチェックする列が11列あり、1シートずつ見ていくとかなりの時間を要します。
これが1枚のシートになればフィルタをかけるなどしてチェックしやすくなるのにな、と思い
[[20120407230100]]の2．複数のシートを1枚にまとめたい。で質問させてもらいました。
値の貼り付けをし、計算式の入っていない偶数シートを1枚にまとめてミスをチェックし、
元ファイルとして残している計算式の入った偶数シートの該当部分を訂正する。
これが一番効率がいい方法かな、と思いました。
本当は計算式が入ったまま偶数、奇数の全てのシートを1枚のシートにまとめられると、
偶数シートを訂正すると奇数シートの参照部分も訂正され、訂正後の奇数シートを範囲指定して
csvファイルに変換するまでの作業が楽なのですが、計算式が入ったままだと
途中からエラー表示が出てくるので、あきらめました。
あ、1枚のシートにまとめてからは偶数シートの計算式は必要ないので、奇数シートの個所に
参照式を入れ直せば、元ファイルに戻って訂正する、という作業は省けるかな…
また、偶数シートのうち数列はセルを結合して中央揃えになっているのですが、
d)の表を作成するにはセルの結合をほどく必要があり、結合をほどくと、
2行目が空白になってしまうため、1．1行おきに参照したい。と質問させてもらいました。
また、現在はマクロを個々のファイルに書いて処理しているのでしょうか？
私の上司がこの作業の前任者で、今回上司の補佐役として作業を行ったのですが、
今後は私が主担当者になるということに決まりました。
ですが、a) の作業に手間と時間がかかる割にはチェックミスが多く、
サーバーへアップロードをしてもエラーがたくさん出てやり直し、という状況でしたので、
手順書を作成する時間をもらい、今回のデータを基に、もっと効率のいい作業の進め方がないか
模索中、という状況です。
また、この作業は今のところ半年に1回発生しているのですが、
今年度中に月1回ペースにしたいというのが上の意向だそうです。
頻繁にある作業であれば、個人ブックに書いておいてフォルダ単位で処理をする
ということもできますが、前回4シート目から処理をするといっていたので、
３の処理の後になにかシートを付けていたでしょうか？
個人ブックという便利なものがあるのですね。知りませんでした。
http://allabout.co.jp/gm/gc/297809/
「4シート目から」と指定しましたのは、｢シートを1枚にまとめる」マクロ入りのブックを作っておき、
次回からは受け取ったデータを値の貼り付け、ブックを一つにまとめる作業をしたのち、
全てのシートを選択、「シートの移動またはコピー」をしてマクロ入りのブックに貼り付け、
Sheet1に作っておいたボタンを押すと、｢まとめ｣シートにまとまる、という仕組みにしたかったからなのです。
シートの移動またはコピー先を選択するときに、挿入先ボックスにはSheet1,Sheet2,Sheet3,
(末尾へ移動）と表示されるのですが、(末尾へ移動）が選択しやすいかな、と思い、「4シート目」
の指定になりました。
以上です。
長々と書いた割にはわかりにくいかもしれません。
ここをもっと具体的に、と指摘していただければ説明させてもらいますので、
よろしくお願いいたします。
（えま）
詳細な説明ありがとうご会いました。
運用は既にマクロと処理データを分離することを考えられているようなので、その方向で 良いと思います。
まとめたデータは、マクロのあるファイルにまとめた方が良いですか？ 新規ブックにまとめた方が、マクロのあるファイルは変更しなくて済むので良いような 気がします。 まぁ、このあたりは些細な変更なので、後々変えても良いと思いでしょうか。
いろいろと改善の余地はありそうですが、いっぺんにやると混乱しそうなので、 まずは質問にあった a) の部分を回答します。
今回の提案としては、元ファイルから一度に集計シートを作ってはどうでしょうか。
作業としては、フォルダの中にあるファイルを順番に開き 偶数シートを順番に纏めシートに値としてコピーする。 という内容にすれば、良いかと思います。
すでに、シートのコピーの部分は出来ていますから、考えるのはファイルを順番に見て いくという処理ですが、このあたりは学校を探してみるとたくさん例があると思います。 フォルダの中のファイルを順番に処理するというのは、 [[20120420224652]] なのがありますね。
これをこの前の処理とくっ付けるとこんな感じでしょうか。 これをたたき台に、修正したいところを変更していくとよいかと思います。 (Mook)
Option Explicit
'//-----------------------------------
Sub まとめシートの作成()
'//-----------------------------------
Dim ファイル管理
Set ファイル管理 = CreateObject("Scripting.FileSystemObject")
Dim まとめシート As Worksheet
Set まとめシート = ThisWorkbook.Worksheets("まとめ")
Dim フォルダパス
With Application.FileDialog(msoFileDialogFolderPicker)
If .Show <> True Then Exit Sub
フォルダパス = .SelectedItems(1)
End With
'// 指定フォルダのファイルを順番に処理
Dim 処理ファイル
'// 画面の更新を停止
Application.ScreenUpdating = False
'// 計算を停止
Application.Calculation = xlCalculationManual
For Each 処理ファイル In ファイル管理.GetFolder(フォルダパス).Files
まとめ処理 まとめシート, フォルダパス & "\" & 処理ファイル.Name
Next
'// 計算を自動
Application.Calculation = xlCalculationAutomatic
'// 画面の更新を再開
Application.ScreenUpdating = True
End Sub
'//-----------------------------------
'// 一ファイルの処理
'//-----------------------------------
Sub まとめ処理(まとめシート, ファイルパス)
Dim 処理ブック As Workbook
Set 処理ブック = Workbooks.Open(ファイルパス)
Dim 処理シート As Worksheet
Dim 貼付行 As Long
貼付行 = まとめシート.Cells(Rows.Count, "A").End(xlUp).row
If まとめシート.Cells(貼付行, "A").Value <> "" Then 貼付行 = 貼付行 + 1
Dim シート位置 As Long
Dim 最終行 As Long
For シート位置 = 2 To 処理ブック.Worksheets.Count Step 2
Set 処理シート = 処理ブック.Worksheets(シート位置)
最終行 = 処理シート.UsedRange.Rows.Count
まとめシート.Cells(貼付行, "A").Resize(最終行, 1).Value = 処理ブック.Name '// 行数分ブック名を記載
まとめシート.Cells(貼付行, "B").Resize(最終行, 1).Value = 処理シート.Name '// 行数分シート名を記載
まとめシート.Range(まとめシート.Cells(貼付行, "C"), まとめシート.Cells(貼付行 + 最終行 - 1, "U")).Value _
= 処理シート.Range("A1:S" & 最終行).Value
貼付行 = 貼付行 + 最終行
Next
処理ブック.Close False
End Sub
プログラムに何が書かれてあるのか何となく理解できたところで、マクロを実行してみましたが、
私にとっては不思議な現象が起きました。
デスクトップ上に、以下の二つのフォルダを置いて、参照先に指定しました。
a.お客様から受け取ったフォルダ
b.a.の中のブックを値の貼り付けをして保存しなおしたフォルダ
すると、
a.を参照先に指定すると、奇数シートが一つにまとまり、
b.を参照先に指定すると、偶数シートが一つにまとまりました。
何度か新しいブックでマクロを実行してみたのですが、結果は同じでした。
a.とb.の違いは、
・a.計算式入り b.値のみ
・a.xlsx形式 b.xls形式
b.は以前に手作業で値のみ、97-2003ブックに変更したものを使用したので、マクロは入っていません。
一つ気になったのが、マクロを実行すると参照ダイアログボックスが開き、参照したいフォルダを
指定すると、「検索条件に一致する項目はありません」というメッセージが出てきたことです。
そのまま「OK」ボタンを押すと、参照したフォルダによって、奇数シート、偶数シートが一つに
まとまったシートができました。
まとまったシートはまさに欲しかったもので、
「これこれ！すごい！ブックを1個づつ開けなくてもいいんだ、すごいよMookさ〜ん♪」
と小躍りしてくるくる回りたいほど嬉しかったです。
ただ、何度読んでも奇数シート、偶数シートを指定するプログラムがどこに書かれてあるのかわかりません。
どの部分で、奇数、偶数シートを指定しているのでしょうか。
（えま）
偶数シートと奇数シートというのは何を見たら判断できるでしょうか。 もしシートに番号があるのだとしたら、現在はその番号は見ていません。
For シート位置 = 2 To 処理ブック.Worksheets.Count Step 2
は左から2番目のシートから最後のシートまで一つ飛ばし(Step 2) で処理しているという
ことですから、シートの偶数、奇数の判断が異なるのであれば、ここを修正する必要が
あります。
エラー（？）に関しては、ちょっと調べてみないとわからないですが、気になるところ ですが、また夜にでも調べてみます。 とりあえず、シート指定の説明まで。 (Mook)
b.のフォルダには元ブックから偶数シートのみを選んで保存していました。
ですので、a.,b.どちらのフォルダを参照先に指定しても、
プログラム通りシートの左から2番目を一つ飛ばしにして保存されていました・・・
B列がシート名になっているのにそこを見ずに、項目名が入っている行をみて、
あれ？あれ？？
となっていました。
すみませんでした。
（えま）
無事に処理ができたようでよかったです。
c) の処理は EXCEL 以外のアプリケーション処理でしたら、手が出ないかもしれませんが、 b)、d) の処理は手作業の改善をしたいところですね。
現在の手順をマクロの記録でマクロにしてみると、マクロ化のヒントが得られるかも しれませんし、シートが一つにまとまれば関数で処理するのも結構簡単かもしれません。
上で書いた私の宿題ですが、新規ブックに書くには
Set まとめシート = ThisWorkbook.Worksheets("まとめ")
の部分を
Set まとめシート = Workbooks.Add().WorkSheets(1)
まとめシート.Name = "まとめ" '// 名前を変えたければシート名を変更
のようにすれば、新規ブックにまとまるようになります。
Set まとめシート = Workbooks.Add().WorkSheets(1)
は端折った書き方ですが
Workbooks.Add '// 新規ブックの作成
Set まとめシート = ActiveWorkbook.WorkSheets(1) '// 新規ブックの先頭シートを指定
と同じことです。
マクロのステップ実行などで、動作を確認してみてください。 (Mook)
寝ても覚めてもマクロに当たっては砕け散っている、えまです。
ゴールデンウィークでご予定もおありでしょうに、お返事をありがとうございます。
現在、a)の処理は上でMookさんに教えて頂いたプログラムの"For シート位置 = 2〜"を1に
変えて使わせてもらい、
”For シート位置 = 1 To 処理ブック.Worksheets.Count Step 2”
まとめシートで訂正した箇所は元のシートも訂正しようと考えています。
そして、b)の処理ができないかと昨日から本、ネットを読んで試しています。
先に購入した2冊の本にはCSVファイルに出力する記述がなく、
ネット上でも数人の方が書かれたプログラムを読んだのですが、
長すぎて私には理解できませんでした。
そこで、新たに本を購入しました。
「Excel VBA 逆引き辞典パーフェクト」
http://www.amazon.co.jp/Excel-%E9%80%86%E5%BC%95%E3%81%8D%E8%BE%9E%E5%85%B8%E3%83%91%E3%83%BC%E3%83%95%E3%82%A7%E3%82%AF%E3%83%88-2010-2007-2003%E5%AF%BE%E5%BF%9C/dp/4798122084
csvではなく、「テキストファイルに書き込む」というタイトルでしたが、
内容をアレンジして下記のプログラムを書いてみました。
Sub csv出力()
Dim 行 As Long
Open "C:\Users\私の名前\Desktop\csv出力.csv" For Output As #1
For 行 = 3 To 23
Print #1, Cells(行, 1)
Next 行
Close #1
End Sub
これでは、列の指定が出来ていませんので、Mookさんに教えていただいた下記のプログラムと
くっつけられないかと試しているのですが、挫折しています。
奇数シートで指定したい範囲は3行目、B〜Q列、最終行はばらばらです。
B列の4行目から最終行までファイル名にしたい装置コードが入っています。
最終行を見るのは
lastRow = ws.UsedRange.Rows.Count
セルの値をファイル名にするにはws.Nameをファイル.Nameにするのだろう。
dstWS.Cells(dstRow, "B").Value = ws.Name
Dim ファイル as FileNameと書いておくのかな？
で、上のcsv出力とくっつけるには＠＠;
ここで、挫折しています。
ここがうまく出来ればファイル管理に進んで、一括処理できるはず…
ゴールデンウィーク明けまで待ちますので、お手すきの時にお教えいただけませんか。
よろしくお願いします。（えま）
Sub MakeOneSheet()
Dim dstWS As Worksheet
Set dstWS = Workbooks.Add().Worksheets(1)
Dim ws As Worksheet
Dim dstRow As Long
dstRow = 1
For Each ws In ThisWorkbook.Worksheets
lastRow = ws.UsedRange.Rows.Count
dstWS.Cells(dstRow, "A").Value = ws.Name
ws.Range("A1:S" & lastRow).Copy _
Destination:=dstWS.Range(dstWS.Cells(dstRow, "B"), dstWS.Cells(dstRow + lastRow - 1, "T"))
dstRow = dstRow + lastRow
Next
End Sub
単純に CSV に書き出したいという事であれば、いろいろな方法はありますが、 汎用の問題なので多くの方から回答が得られると思いますが、 指定列、指定行の CSV を作成するのであれば、下記のように書けます。
Sub CSV保存()
Dim 行 As Long
Dim 列 As Long
Dim 一行データ As String
Dim CSVFile As Long
CSVFile = FreeFile
Open "D:\Test.csv" For Output As #CSVFile
For 行 = 3 To 23
一行データ = Cells(行, 4).Value '// 最初は「,」を付けたくないので For の外で初期化
For 列 = 5 To 20
一行データ = 一行データ & "," & Cells(行, 列).Value
Next
Print #CSVFile, 一行データ
Next
Close #CSVFile
End Sub
追記： 別の部分へのコメント。 ちょっと混乱しているようですね。
>セルの値をファイル名にするにはws.Nameをファイル.Nameにするのだろう。 >dstWS.Cells(dstRow, "B").Value = ws.Name
ファイル名は保存するときに指定することになりますので、SaveAs などを 本やサイトを見て整理してみてください（Open なら開くときに指定していますよね。）
最終行を見るのは
> lastRow = ws.UsedRange.Rows.Count
で大体の場合は問題ないとは思いますが、ある列の最終行を見たいのであれば
lastRow = ws.Cells(Rows.Count,"A").End(xlUp).Row
と書けます（A列の場合）。
>奇数シートで指定したい範囲は3行目、B〜Q列、最終行はばらばらです。 >B列の4行目から最終行までファイル名にしたい装置コードが入っています。
ということは、行単位にファイルを作成したいということでしょうか。 このあたりは、どういうファイルをどのように作りたいかを説明した方が良いかと思います。
まずはマクロを書く前に、処理したい流れを整理してはどうでしょうか。 マクロを書くというのは、整理した手順をコードに変換するだけの作業ですから。
次回は週末のアクセスになりますが、気がついた点をコメントまで。 (Mook)
まずはマクロを書く前に、処理したい流れを整理してはどうでしょうか。
昨日このコメントを読んで、Mookさんに教えていただいた「新規ブックにまとめてみる」と
ネットで読んだ「ファイル名を任意のセルの値に変更して保存」を紙に書き出してみました。
今までは目にしたプログラムをエクセルにコピペし、右側に変数やステートメントの意味などを
日本語で書いて、何度も読んでいました。
ちょっとはわかってきたきがするし、もっといろんなプログラムを読み続けると
身につくかも、という気でいたのですが、
紙に書き出すと、どこが理解できて、理解できていない個所はどこか、というのがよりよくわかりました。
エクセルだと理解があやふやな部分は、そのうちわかるようになるはず、と飛ばして
いたのですが、紙だと細かく書き込めるのであやふやな個所こそ書きながら
理解ができるのがいいです。
今朝さっそくノートを買ってきて、お手本のプログラム、変数や文法などの意味を対で書き、
その下に自分でアレンジしたプログラム、なぜそう書いたのか、などを書いています。
Mookさんの一言で、見たものを片っ端から頭の中にギュウギュウに詰め込むのではなく、
すっきりと整理された形で理解してからプログラムを書く準備ができそうです。
冷静になれば普通のことなのに、焦って気が付いていませんでした。
お恥ずかしくもあり、本当にありがたくもあり、です^^
あ、こんなに後ろになってしまってすみません。
上で書いた私の宿題ですが、新規ブックに書くには
Set まとめシート = ThisWorkbook.Worksheets("まとめ")
の部分を
Set まとめシート = Workbooks.Add().WorkSheets(1)
まとめシート.Name = "まとめ" '// 名前を変えたければシート名を変更
のようにすれば、新規ブックにまとまるようになります。
できました。これもノートに取っています。ありがとうございました。
では、引き続き頑張ります！
（えま）
>今までは目にしたプログラムをエクセルにコピペし、右側に変数やステートメントの意味などを >日本語で書いて、何度も読んでいました。 >ちょっとはわかってきたきがするし、もっといろんなプログラムを読み続けると >身につくかも、という気でいたのですが、
やノートに書くというのも良い方だと思いますが、プログラムでは実際にコードを 動かしてみるというのも、理解の手助けになると思います。 できればただ実行するのではなく、ステップ実行をしてシートや変数の内容を確認しな がらですね。
デバッグの仕方（ステップ実行や変数の確認方法）は本にもあると思いますが、 ネットでも下記のように整理されているサイトもあります。 http://www.asahi-net.or.jp/~ef2o-inue/menu/menu04.html その上で、やりたいことをいろいろと試してみてはどうでしょうか。
先ほどのコメントと矛盾するようですが、「流れを整理して」というのは 1)フォルダの中のファイルを順番に、 2) 奇数シートを一つのファイルに集約する。 3)4行目以降の各行をCSVファイルで書きだす。 4) 書き出すCSV ファイルはB列の値をファイル名にする。 というように処理を言葉で書いてみては？ということですし、
「手を動かしてみては？」、といっているのはその中の ・1行をファイルを CSV にするにはどうしたらよいか ・ファイル名にB列の値を使うにはどうしたらよいか といった、細かい基本的な処理のやり方を本やネットで理解してはという意味で 書きました。
この二つが揃って、全体の処理をするマクロが実現できることになります。
最初はわからないことが多いかもしれませんが、ご自身でやったことを説明しながら その上で分からないことを質問するようにすれば、きっと多くの強力な回答者が喜んで 力になってくれることと思います。
がんばってください (Mook)
プログラムをあれこれ試しては、途中でわからなくなったりしてジタバタしていますが、
もっと時間をかけて少しずつ進めていこうと思います。
このスレッドは長くなってしまい、他の方の役に立つだろうコードも
目に触れにくくなるかと思うので、
次回は別に質問を立てようと思います。
私の長々とした説明にもお付き合いいただき、本当にありがとうございました。
今後ともよろしくお願いします。
（えま）
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.