ExcelVBAで複数のブックからデータを別ブックのシートに横に貼り付けるには
受付中
回答 2
投稿
- 評価
- クリップ 0
- VIEW 7,401
前提・実現したいこと
ここに質問したいことを詳細に書いてください
ExcelVBAで基本統計量を計算するツールを作っています。
複数のブック(例:ブック名”2016/05”など)から、1企業1シートで、シートの右方向に年度順に並べて貼り付けていきます。その際、特定の列のみを抽出し、かつ、フィールドの見出し(1行目)を除いて貼り付けていきたいと考えております。
お尋ねしたいのは、ある年度において、企業のデータ複数のブック(複数月)にまたがっている場合に5月6月という順番で列方向に隙間なく並べていくにはどうしたらよいのかということと、シートの右方向に並べて貼り付ける方法です。
ファイルは一つnフォルダに2016/5,2016/06、2015/04-05,2015/06,2014/04-05,2014/06という風にはいっています。
現在は、以下のようコードまで書きました。この後どのようにコードを書いていけばよいかご教授ください。よろしくお願い申し上げます。
発生している問題・エラーメッセージ
エラーメッセージ
該当のソースコード
Sub sample()
Application.ScreenUpdating = False
Dim vBk As Workbook
Set vBk = Workbooks.Open(ThisWorkbook.Path & "\Book2.xlsx")
vBk.Worksheets("Sheet1").Range("A1").AutoFilter field:=5, Criteria1:="A株式会社"
vBk.Worksheets("Sheet1").Range("A1").CurrentRegion.Resize(.Rows.Count - 1).Offset(1).Copy
ThisWorkbook.Worksheets("Sheet2").Paste
ThisWorkbook.Worksheets("Sheet2").Columns(1).Delete
ThisWorkbook.Worksheets("Sheet2").Columns(1).Delete
ThisWorkbook.Worksheets("Sheet2").Columns(1).Delete
ThisWorkbook.Worksheets("Sheet2").Columns(1).Delete
vBk.Close
End Sub
試したこと
横に貼り付けるするために、
With ThisWorkBook.Worksheets("sheet2")
.Activate
.Cell(.Columns.Count,1).Offset(1).Activate
.Paste
End With
を調べて試しましたがうまくいきませんでした。
補足情報(言語/FW/ツール等のバージョンなど)
Excel2010, Win7
-
気になる質問をクリップする
クリップした質問は、後からいつでもマイページで確認できます。
またクリップした質問に回答があった際、通知やメールを受け取ることができます。
クリップを取り消します
-
良い質問の評価を上げる
以下のような質問は評価を上げましょう
- 質問内容が明確
- 自分も答えを知りたい
- 質問者以外のユーザにも役立つ
評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。
質問の評価を上げたことを取り消します
-
評価を下げられる数の上限に達しました
評価を下げることができません
- 1日5回まで評価を下げられます
- 1日に1ユーザに対して2回まで評価を下げられます
質問の評価を下げる
teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。
- プログラミングに関係のない質問
- やってほしいことだけを記載した丸投げの質問
- 問題・課題が含まれていない質問
- 意図的に内容が抹消された質問
- 過去に投稿した質問と同じ内容の質問
- 広告と受け取られるような投稿
評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。
質問の評価を下げたことを取り消します
この機能は開放されていません
評価を下げる条件を満たしてません
質問の評価を下げる機能の利用条件
この機能を利用するためには、以下の事項を行う必要があります。
- 質問回答など一定の行動
-
メールアドレスの認証
メールアドレスの認証
-
質問評価に関するヘルプページの閲覧
質問評価に関するヘルプページの閲覧
+1
年度のデータということですのでforを使って1月から12月までのデータを開いていく感じだと思います。
その際に、ファイルの存在を確認し、ファイルがある場合のみ処理を行うようにしましょう。
また、列方向に隙間なく並べるということなので、もとになるファイルが何列あるかを取得してその列の+1列目に
データを貼り付けるようにすれば良いと思います。
あくまでも参考ですが、下記のような感じかと思います。
Set vBk = Workbooks.Open(ThisWorkbook.Path & "\Book2.xlsx")
'1月から12月までループする
For i = 1 To 12
'ファイル名を取得する ※ファイル名が201601の場合
Filename = "2016" & Format(i, "00") & ".xls"
'ファイルの存在を確認する
If Dir(ThisWorkbook.Path & "\" & Filename) <> "" Then
'ファイルがある場合
Set work = Workbooks.Open(ThisWorkbook.Path & Filename)
'コピーする範囲を指定する
vBk.Worksheets("Sheet1").Range("A1").AutoFilter field:=5, Criteria1:="A株式会社"
'コピーする
vBk.Columns("E:E").Copy
'vBkの列が何列あるか取得する
c = vBk.Range("A1").CurrentRegion.Columns.Count
'vBkの列の+1列目に貼り付ける
vBk.Cells(1, c + 1).Paste
'保存せずに閉じる
work.Close SaveChanges:=False
End If
'繰り返す
Next
投稿
-
回答の評価を上げる
以下のような回答は評価を上げましょう
- 正しい回答
- わかりやすい回答
- ためになる回答
評価が高い回答ほどページの上位に表示されます。
-
回答の評価を下げる
下記のような回答は推奨されていません。
- 間違っている回答
- 質問の回答になっていない投稿
- スパムや攻撃的な表現を用いた投稿
評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。
0
質問の内容について
①情報の提示は正確にお願いします。
ブック名=ファイル名だと思いますが、Windows7ではファイル名に/を含めることはできません。
このため2016/04のようなブック名はありえないと思います。
今回はブック名から年月を判断するということでしたので、この部分の情報提供は正確にお願いします。
②うまく動かない?
「試してみたがうまく動きませんでした」という質問者さんはよくいますが、どう動いてほしかったけど、どういう動きをしてしまう(=うまく動かない)という説明がありません。
期待する動きと、ダメだった動き(エラーが発生したとか、何も出力されなかったとか)を詳しく記載していただけると回答しやすくなります。
アドバイス①
まず「試したこと」のコードについてのアドバイスです。
おそらく、データの存在する最終列の右隣の列にデータを貼り付けたいのではないかと思いますが、記述されたコードにはいくつか間違いがあります。
①Cellsの指定はCells(行,列)
.Cellsでの座標指定は、(X, Y)の座標指定のように勘違いされる方も多いですが、Cells(行番号, 列番号)の順です。
同様にOffsetも第一引数は行数です。
右にひとつずらした列を指定する場合、.Offset(,1)とする必要があります。
②Column.Countは利用可能な最大列番号
Column.Countで取得できる値は、Excel上で利用できる最大列番号です。
Excel2010ではXFD列(16384)が返されます。
データのある最終列を取得したい場合は、例えば.Cells(1, .Columns.Count).End(xlToLeft)
のように記述することで1行目の最終データのセルを取得できます。
(利用可能な最終列から「Ctrl + ←」をした位置、という意味合いです。)
以上を踏まえて、提示されたコードを意図した動きにするには
With ThisWorkBook.Worksheets("sheet2")
.Activate
.Cell(1, .Columns.Count).End(xlToLeft).Offset(,1).Activate
.Paste
End With
のようなコードになると思います。
アドバイス②
目的の動作を行うために、フォルダ内のファイルを順次取得して、ファイル名を判断しながら処理を行う必要があると思います。
そのひとつの方法として、以下のようなやり方があります。
Dim strFileName As String
Dim lRow As Long
'フォルダ内の.xlsxファイルを取得
strFileName = Dir(ThisWorkbook.Path & "*.xlsx")
lRow = 0
Do While strFileName <> ""
'ファイル名を利用して操作(このサンプルではファイル名をセルに出力するだけ)
lRow = lRow + 1
Cells(lRow, 1) = strFileName
'次のファイル名を取得
strFileName = Dir()
Loop
冒頭にも書きましたが、実際に取得されるファイル名(ブック名)がよくわからないのと、複数月にまたがるファイルでどんな動作をしたいのかなど、細かな部分がわかりませんのでファイル名を取得して利用する部分だけ記述させていただきました。
長文になってしまいましたが参考になれば幸いです。
がんばってください。
投稿
-
回答の評価を上げる
以下のような回答は評価を上げましょう
- 正しい回答
- わかりやすい回答
- ためになる回答
評価が高い回答ほどページの上位に表示されます。
-
回答の評価を下げる
下記のような回答は推奨されていません。
- 間違っている回答
- 質問の回答になっていない投稿
- スパムや攻撃的な表現を用いた投稿
評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。
15分調べてもわからないことは、teratailで質問しよう!
- ただいまの回答率 88.23%
- 質問をまとめることで、思考を整理して素早く解決
- テンプレート機能で、簡単に質問をまとめられる