[[20220118202728]] 『マクロでデータコピーしたい』(くしゃみ) >>BOT

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『マクロでデータコピーしたい』(くしゃみ)

初心者です。
マクロでデータのコピー、抽出ができるとこことですが、やり方が分からず教えてください。

はじめに
既にA列からAL列までの表が作成されています。
しかし、全ての列が表示されておらず例えばA列は表示されていません。
A4からAL4までにフィルターがセットされています。
T列には日付が入力されています。

流れ
1 ボタンを押したら、「いつのデータをコピーしますか?」というメッセージと共に、データを入力するようにする(できたら、●/●(月/日)と日付を入力したら、年を入力しなくとも今年だと自動的に判断してほしいです。)
2 1で入力した日付をT列でフィルタし、表全体をコピーする。
3 「●月●日(1で入力した日付)のデータをコピーしました。データ数は●件です。」と表示し、表はもとのデータを抽出していない最初の状況に戻す。

よろしくお願いします。

< 使用 Excel:Excel2019、使用 OS:Windows10 >


とりあえず【マクロの記録】という機能をつかうと、手作業の内容がほぼコード化できます。
想像するに、やりたいことのいくつかは【マクロの記録】で必要な命令を調べることができますから、何にも手をつけられていないなら、そこからはじめてみてはいかがでしょうか?

(もこな2 ) 2022/01/18(火) 21:14


 >年を入力しなくとも今年だと自動的に判断してほしいです。

 日付け音痴なので...ちゃんと動作するかわかりませんが^^;
 Sheet1(元データ)と過程してを、Sheet2(コピー先)として練習させていただきました。

 こんなかんじでどうでしょうか?

 Option Explicit
 Sub Sample()

    Dim flg As Boolean
    Dim tmp As String, MyDate As String '' InputBox関数の戻り値は文字列の為、String型
    Dim MaxRow As Long, CriteriaRow As Long, OpCnt As Long
    Dim ws As Worksheet
    Set ws = Sheets(1)
    MaxRow = ws.Cells(Rows.Count, 20).End(xlUp).Row '' T列( ※日付範囲 )最終行を取得
    flg = False

    Do '' ループ処理にてキャンセル処理しない限り、日付が入力されるまでインプットボックスを表示
            tmp = InputBox("いつのデータをコピーしますか?")
            If StrPtr(tmp) = 0 Then '' tmpに代入されている値をチェック
                    If IsNumeric(tmp) Then flg = True '' IsNumeric 関数で、InputBoxの戻り値を数値か判定
                            MsgBox "終了します"
                    Exit Sub '' キャンセル処理
            End If
            MyDate = Format("2022/" & tmp, "yyyy/m/d") '' 2022年度分のみ対応
            If IsDate(MyDate) = True Then '' 日付型変換
                    With ws.Range(ws.Cells(4, 1), ws.Cells(MaxRow, 38)) '' ( ※A列〜AL列範囲 )
                            .AutoFilter Field:=20, Criteria1:=MyDate '' 入力した日付をT列でフィルタ
                            OpCnt = Application.WorksheetFunction.Subtotal(3, Range("T5:T1048576"))
                            MsgBox CDate(MyDate) & "(入力した日付)のデータをコピーしました" & vbCrLf & _
                           vbCrLf & vbCrLf & _
                           "データ数は、" & OpCnt & "件です"
                    End With
            End If
    Loop Until IsDate(MyDate)
            CriteriaRow = ws.Cells(Rows.Count, 20).End(xlUp).Row '' 絞り込み後の範囲を取得
            '' (コードは絞り込み日付の範囲)を転記します
            '' 表全体をコピーするなら ws.Cells(4, 1)と変更し、コピー先の行を調整
            ws.Range(ws.Cells(5, 1), ws.Cells(CriteriaRow, 38)).Copy Sheets(2).Range("A2") '' シート2の2行目から下へ転記
            '' 表はもとのデータを抽出していない最初の状況に戻す
            ActiveSheet.ShowAllData '' 絞込解除

 End Sub
(あみな) 2022/01/19(水) 18:20

 訂正〜です

 CriteriaRow = ws.Cells(Rows.Count, 20).End(xlUp).Row

 '' 絞り込み後の範囲を取得ではなく

 '' 絞り込み後の範囲を取得する為に、T列の最終行を取得かな

(あみな) 2022/01/19(水) 18:30


あみな様

ありがとうございます!!
ただ、成功していないようです。
流れとしては以下の通りです。

いつのデータをコピーしますか?と表示される
○/○(例えば1/20)と入力する
2022/1/20(入力した日付)のデータをコピーしました データ数は、0件です と表示される
※実際にはデータがあります
空いているところに貼り付けてしようとしても、データがない(貼り付けできない)

つまり、指定した日だけのデータを表示させることができなくなっているのかと思いました。
原因はわかりますでしようか?
(くしゃみ) 2022/01/20(木) 19:41


動きをみていると、T列(日付が入った列)でフィルタの表示が一瞬変わるため、T列でフィルタしようと稼働しているようなのですが、なぜか該当データを表示させることに失敗しているのかと思いました。
(くしゃみ) 2022/01/20(木) 19:47

すみません。
分かりました。

Format("2022/" & tmp, "yyyy/m/d") '' 2022年度分のみ対応

Format("2022/" & tmp, "m/d") '' 2022年度分のみ対応
に修正したところうまくいきました。

本当にありがとうございました。
(くしゃみ) 2022/01/20(木) 19:50


すみません。
別のところで失敗しました。

データ数は100件です。
といったように表示されるようになりました。

しかし、貼り付けしようとして右クリックすると、貼り付けオプションが白くなっており、選択できません。
ctrl +vでも何も貼り付けできません。

データの選択がやコピーができていないということでしょうか。
原因はわかりますか?

よろしくお願いします。
(くしゃみ) 2022/01/20(木) 20:20


 こんばんは

 まず、日付けの確認から

 確認1. セルの日付けは、セルの書式設定で 1/20 などに全てしていますか?
        2022/1/20とかではないと言うことです

 確認2. データ数は100件です。が表示された時、絞り込みの行は対象の日付けがキチンと
    絞り込みされていますか?

 確認3. 上記の抽出をした日付けの入力は、どんな形式ですか?

(あみな) 2022/01/20(木) 20:54


 確認4. 日付けで、2021/1/20 などをセルの書式設定で 1/20 に表示させていると
        ご自身は2022/1/20だけを抽出されたつもりでも...2021/1/20分も絞り込みされますよ

 基本、セルの日付けは、2022/1/29 などとしてあるつもりでマクロは作ってありますので

(あみな) 2022/01/20(木) 21:20


 質問1. 因みにですが...日付けの行は何行位あるのでしょうか?
(あみな) 2022/01/20(木) 21:33

あみな様

ご連絡ありがとうございます。

確認1について
T列の日付はセルには2022/1/20と入力されていますが、書式設定で1/20と表示させています。

確認2について
2022/1/20のデータをコピーしました データ数は100件です と表示されてときは、スクロールができないため、絞り込まれた全体を確認できませんが、表示されている部分については1/20のデータのみが表示されています。
またデータ数は100件ですという件数も実際とあっています。

確認3について
t列のセルの書式の表示方法という意味でしょうか?
日付の種類は3/14という表示方法です。

確認4について
基本的に、セルに日付を入力すると、年月日が反映されるかと思います。
例えば、今、1/21と入力されれば、自動的に2022/1/21とセルには入力されているといった感じです。
2021年のデータは存在しないので、2021のデータと2022のデータが混ざることはないと思います。

質問1について
表示体は1000行ほどあり、今後も増えてきます。
ただ、データは増えますが、過去の日付のデータが増えるのではなく、新しい日付のデータが増えていきます。
各日付ごとにデータ数の上限はないのですが、1日に多くても200くらいだと思います。

これで、原因はわかりますでしょうか。

自分なりに考えてみたものですと、データはコピーされた状態で、自分が好きな場所に、形式を選択して、貼り付けしたいと思っています。
しかし、作成していただいたのではシート2に反する形かと思いますので、sheet(2).range("A2")を削除したらうまくいくのかと思ったのですが、駄目でした。
(くしゃみ) 2022/01/21(金) 09:05


 それは、仕様を変更するならマクロも変えないといけませんわぁよ

 Copy メソッドが、勝手に取るなやって...怒ってますわぁ(笑)

 .Copy から削除して .Select にしてみてください

 で ActiveSheet.ShowAllData も削除してください
 それで好きな形式で選択してコピーしてみて

 今、私仕事中なので...詳しくは今夜ね
(あみな) 2022/01/21(金) 09:45

ありがとうございます。
教えていただいた箇所の修正と削除をしました。

すると、コピーしました というメッセージは出るのですが、そのまま空いている場所に貼り付けしようと思っても、貼り付けが選択出来ません。
コピーしました というメッセージのあとで、改めて自分でコピーをしないとダメでした。

また、フィルターで、全範囲を表示するところまでをマクロにしてほしいのですが、それは出来ないのでしょうか?

よろしくお願いします。
(くしゃみ) 2022/01/21(金) 13:53


 コピーしましたも修正しないとダメですよ
 ご自身で、形式選択して好きな場所へ転記したいのでしょ?
 フィルター後に、手動でコピーすると...非表示範囲がどうなるか
 知りませんが...

 因みに、空いてる場所ってどこなんですか?

(あみな) 2022/01/21(金) 14:15


マクロを使わないと、フィルタで日付を指定→コピー→フィルタを解除→別に貼り付け出来ますが、マクロでは難しいということでしようか?

コピーしました の修正、、、?

別のエクセルに値として貼り付けしたいです。
日毎に貼り付けるエクセルが用意してあり、例えば1/20のコピーしたデータは1/20用のエクセルに値として貼り付けします。
ちなみに、日毎のエクセルは日毎のフォルダに収納されています。
(くしゃみ) 2022/01/21(金) 19:59


 >フィルターで、全範囲を表示するところまでをマクロにしてほしいのですが、それは出来ないのでしょうか?

 最初のマクロをテストした時に、動作確認はされましたか?
 ちゃんと絞り込んだ後に、別のシートにコピーして...元の表に戻ってますよ。
(あみな) 2022/01/21(金) 20:21

 手動で形式を選択したいのではないのですね?

 では、質問の仕方が根本的に違います。
 最初のマクロに追加で形式を設定して転記できますか?と聞かないと

(あみな) 2022/01/21(金) 20:25


 >自分なりに考えてみたものですと、データはコピーされた状態で、自分が好きな場所に、形式を選択し 
 >>て、貼り付けしたいと思っています。

 上記文章は手動でしますと言ってますよね?違いますか?

 もしくは、マクロを改変するならご自身ですれば良いことだと思いますよ。
(あみな) 2022/01/21(金) 20:33

 1000行のシートで実行すると理解できないので
 BOOKを複製して、20〜30行にして...最初のコードでテストを
 もう一度してください。

 Format("2022/" & tmp, "yyyy/m/d") ←この部分は下記にしてもいいです

 Format("2022/" & tmp, "m/d")
(あみな) 2022/01/21(金) 20:48

20〜30行に減らしましたが、ダメでした。

貼り付けは手動でします。
しかし、現状だとコピーができていないため、コピーも手動でしなければいけない状態です。
コピー→フィルタ解除→貼り付けの流れで、フィルタ解除までマクロにより処理して貼り付けだけは手動でやりたい、という意味です。
説明が悪くて申し訳ありません。
(くしゃみ) 2022/01/24(月) 15:39


 >20〜30行に減らしてダメでした。

 ↓これ入れてくれたかな? 無いとコピーできませんよ。
 .Copy Sheets(2).Range(“A2”)

 貼り付けた情報を削除するマクロを...まだ入れてないので
 手動で消しながら確認してください。

 >別のエクセルに値として貼り付けたいです。

 値貼り付けや、セルの幅調整などもVBAで出来ます。
 また、別のエクセルにも勿論コピー可能ですから
 一旦最初なマクロで元表が戻る所まで確認してください。
(あみな) 2022/01/24(月) 19:49

 >日毎に貼り付けるエクセルが用意してあります。
 >例えば1/20のコピーしたデータは、1/20用のエクセルに
 >値として貼り付けします。

 貼り付け先の エクセルを用意しなくても新規ファイルを
 作りながらフォルダに収納出来ます。

 インプットボックスに入力した日付で、1/24と入力したら
 ファイル名が 2022.01.24 となりますね。
 ファイルの名前ですが、半角(スラッシュ)は使えませんよ。

 因みに拡張子は、( .xlsx )で良いのですよね? 
 まだ出先なので、出来るかわかりませんが
 もう少し作ってみます。
(あみな) 2022/01/24(月) 20:51

コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.