連想配列を使います。キーは、厳密には、品名＋タブ＋単価です。 エクセルで シート を コピー・移動する方法とエラー対策についてまとめました。. まさかとは思いますが、このマクロを記録したファイルの名前は「友の会・観察会資料2017.xlsx」や「友の会・1月観察会資料2017.xlsx」などにはしていませんよね？ Option Explicit If Not c Is Nothing Then '//←念のため// INDEX(参照, 行番号, [列番号], [領域番号])と書いてありますが 例えば、3：15 → 3.25 となります。 　　'2行目から Set sh2 = Worksheets("Sheet2") End With ComboBox のList に、どこのシートからどのように重複を許してImport するコードを見せてほしいと言っているわけですね。最近、コードは見せられない、だから文章の説明的な質問の人が増えてきたと思うのです。画像添付でも、相手に伝えられる情報ではないです。 =SUMPRODUCT((MONTH(A$1:A$1000)=D1)*B$1:B$1000) 基本的な手順はもちろん、エクセルでシートのコピーができない、という方の参考になるように、エラーのパターンもいくつかピックアップしてみました。 これで大丈夫だと思います。 Next i 「vba 入力規制を解除する」 という検索で、このサイト・インストラクターのネタ帳へのアクセスがありました。 「入力 規制 」となっていますが、「入力 規則 」を解除・削除するVBA（Visual Basic for Applications）のコード・Excelマクロを探している方による検索でしょう。 セル B2 と、セル範囲 A2:C6 の 5 行目と 2 列目の交差するセル B6 の合計を求めます。 Dim elm() As String sub sumple() For Each key In dicT ＞INDEX 関数には、次の 2 つの使い方があります。 If cnt = 7 Then Exit For Sheet2!B列の入力規則のリスト 上記の1日=1.0(1日=24時間）を利用すれば、もっと簡単に 1行名はタイトル行 =OFFSET($G1,,,,COUNTIF($G1:$Z1,"? 1行名はタイトル行 コードを調べたのですが下記のようなコードをうまく応用して使用したらいけるのでしょうか？ 各時間帯の休憩時間がそれぞれ、 これに関しては手作業で範囲指定　→　並び替え　というコトが可能だと思いますので、今回はスルーします。 ＞指定したセルまたはセルの配列の値を求める場合は、「配列形式」を参照してください。 Excel VBA マクロのシートをコピーする方法を紹介します。Sheets.Copy メソッドまたは Worksheets.Copy メソッドでシートをコピーできます。複数まとめてやシートの位置、ワークブックなどを指定できます。 Dim i As Long, j As Long ・入力されたセルの行の位置を引数に入れる． 　=T*24 ちなみに私は現在、福岡市博多区の会社で経理の仕事をやっています。 （拒否られるかと思いきや、動作してしまうようですが） Dim i As Long, cnt As Long Sub Sample1() さっそく、恐怖の警告メッセージ（既にある名前が含まれています）が出ないようにする方法を紹介します。 やることは、『名前の管理で参照エラーになっている名前を削除する』だけ。 名前というのは、下の画像のように『セルの場所（範囲）につけられる名前』のことです。 では、シートコピーエラー（名前重複警告）の対策方法です。 下の画像のエクセルは、『シートC（3）』をコピーしよ … 右へ下へオートフィル ①Dのセルに出力するマクロ ・上記引数とCellsを利用してD列に値を出力する． set myFirst=myFound とすることで、時間単位（15分は0.25時間）に該当する値を得られます。 この例のように、SUMで合計するセル範囲をINDEX関数で可変的に指定したり、MAXやMIN関数で調べる範囲をINDEX関数でセル参照が戻るようにしておくと、便利に使えることはあります。, エクセルの数式を教えてください！ 自分一人では解決できないためどうかお知恵を貸してください。, マイクロソフトの説明が理解できません。 set myFirst=myFound 表の作り方を変更もしくはソート等他の方法を考えない場合、E列に各月の合計金額を 引数の説明に、「参照」はなく、代わりに「対応範囲」なるものが突然湧いて出てきました。 Dのセルの行カウントの値を出力する時はSheet1のAのセル値を検索値にSheet2のAのセル値と合致するセルの行番号として出力すれば良いという事になります． このプログラムを下記条件に変更したいのですがアドバイスお願いいたします Dim maxrow As Long ※D3,D4の番号について→002は固定です。5は会社の番号のA列から。2は行カウントの数字ですが、一番上にある行カウントを以下の1が入力された行に自動表示。 myRng2.Resize(, 6).Delete shift:=xlUp ③　は仮にA～F列の1か月分（複数ヶ月でも）データを消去した場合にA～F列は上詰め、 ＞下記のようなプログラムを作成しました ・入力されたセルの行の位置を引数に入れる． Range("E:E").ClearContents ・拘束時間・労働時間・日勤時間・通常残業・深夜時間・深夜残業に反映させるようにしたいのですが、休憩が2枠あるため数式がうまくいきません。。 内容を読むとマクロは少なくとも2つ用意した方が良さそうですね． Sheet2!E1セルに 一件ずつを呼び出し、コピーする方法しか思い浮かびません。 *")) エクセル関数に強い方、どうか教えていただけませんか？, ●入力シートのA列一番下の事業所リストから、商品名を表示する End sub, VBAマクロで作成した新規ブックのリンク解除またはリンクさせない方法を教えてください, エクセル：マクロ「Application.CutCopyMode = False」って？, エクセルのVBAについて教えていただけませんでしょうか？ ＞指定したセルの参照を求める場合は、「セル範囲形式」を参照してください。 Dim cnt As Long,lastcol As Long,Ws As Worksheets そもそも「セルの参照を返す」とはどう定義されているものなんでしょうか？ コピー先のファイルは個別のファイルとして扱いたいのでリンク解除して値として内容を保存したいのですが、シート保存するたびにファイルをあけてリンク解除するのが非常に手間なので保存時にリンク解除をするようにしたいと考えて色々調べたのですが 　　Loop While Not fnd Is Nothing And fnd.Address <> fAddress 例　A3一致の場合O3までを抽出する With Worksheets("情報") ①　は画像のような配置ではなく、最初はA～F列だけにデータが羅列してある。 考え方から説明しますと A2　商品イ　　　　200円　　　空欄　　　　空欄 Dim sh2 As Worksheet G～L列は上詰めした月数分を繰り上げてA～F列に移動させる！という解釈です。 Sub Sample③() Else If cnt = 7 - WorksheetFunction.CountIf(Range("A:A"), "小計") Then Exit For 2007のコマンドで、重複を排除する方法もありますし、両方なら関数で解く方法もあります。ComboBox に入れる以上は、千件以上のものを入れるというのは常識的にありえません。 sheet2に集計するマクロを教えて下さい。 ActiveWorkbook.close 作成したのなら、修正もたやすいのではと思いますが？？ 考え方から説明しますと ②　の「表内での並び替え」とはどの列がキーになるかが判りません。 今回は、Hyperlinks.Addメゾットの使い方を説明いたします。Hyperlinks.Addメゾットは、パイパーリンクの追加・設定を行う事が出来ます。ハイパーリンクを設定する事で、指定したセルにジャンプしたり、別のシートにジャンプ（シート切替）したりする事が出来ます。 下記コードのように自動でシートを別ファイルに保存するようにプログラムしたのですが、コピー先のファイルを開くとコピー元のリンクがそのままになってしまっています これに関しては手作業で範囲指定　→　並び替え　というコトが可能だと思いますので、今回はスルーします。 Next Twitterもやってます。というかそっちの方が主戦場かも。 Dim maxrow As Long End If row = 2 End sub =IFERROR(INDEX(Sheet1!$A:$A,SMALL(IF(INDEX(Sheet...続きを読む, VBA初心者です。 Dim lastRow1 As Long, lastRow2 As Long 列幅や列数が異なる場合があるかも知れません。 ②表内で並び替えが可能としたい。 とありますが、セルの値を求めるというのはわかるのですが、セルの参照を求めるとはどういうことでしょ...続きを読む, 「セルの参照を返す」とはどう定義されているものなんでしょうか？ データは２行目から入力されています。 入力と同時にD列に番号を表示。 以上　よろしくお願いいたします, Excel VBA 文字抽出について Application.DisplayAlerts=Ture ※コードをコピーする時は、右上のアイコン をクリックしてください このマクロを使う理由. 入力と同時にD列に番号を表示。 マクロでの合計金額算出方法を教えて頂けませんでしょうか？, こんにちは！ Sheet2のA1が1，A2が5になります． シリアル値は1日が1.0となるように計算しています。 Public Sub 品名単価集計() =IFERROR(INDEX(Sheet1!A:A,SMALL(IF(INDEX(Sheet1!B$2:D$20,,MATCH(LOOKUP("ーー",A:A),Sheet1!$B$1:$D$1,0))<>"",ROW(Sheet1!B$2:B$20)),ROW(A1))),"") A列から順に品名、単価、数量、金額です。 また、ここの説明の「セル範囲形式」の書式は 深夜帯(深夜時間・深夜残業)は22:00～5:00で、1日の労働時間が8時間を超えた場合に残業となります。 Dim sh1 As Worksheet ＞指定したセルまたはセルの配列の値を求める場合は、「配列形式」を参照してください。 VBAのコードを求めていらっしゃるのなら、回答者側としては、 ①　は画像のような配置ではなく、最初はA～F列だけにデータが羅列してある。 ②Cのセルを判定するマクロ Dim key As Variant 今回の記事でメインとして紹介したいのは、Excelブックの中にあるシートを安全に切り離す方法だ。他のシートとの繋がりがある計算式が入っていたりすると、その計算式を外しておかないとシート中の数値とかが変わったりエラーになってしまうことがある。 　　　Set fnd = .FindNext(fnd) A4＝5　B4　×× C4="" D4=002-5-2 Dim cnt As Long,lastcol As Long,Ws As Worksheets どうしてもVBAで！という場合、一例です。 ちなみに、マクロを入れたファイルは開始時と終了時に毎回消去しているので何も表示されないはずです。出来上がった「友の会・観察会資料2017.xlsx」はどうなっていますか？ １行目はタイトル行、 例えばA8に会社番号が6というのが入力された場合には行カウントは3になりますか？ key = sh2.Cells(row, "BG").Value & delm & sh2.Cells(row, "CX").Value ...続きを読む, マイクロソフトの説明が理解できません。 Const delm As String = vbTab Set c = Range("D:D").Find(what:=Month(Cells(i, "A")) & "月売上", LookIn:=xlValues, lookat:=xlWhole) まだマクロを組んでテストをしていないので，実際のマクロの記述は回答できていませんが，ご参考までに 　　If Not objDic.Exists(.Cells(i, 1).Value) Then 　End If Set Ws=worksheets("結果") この記事はExcelのVBAで「外部リンク」を一括で解除する方法についての解説です。「外部リンクエラー」に悩まされている方は是非ご覧ください。ブックの使いまわしやリンク先シートの削除など意図せず発生した外部リンクエラーの解除におすすめします sheet1はデータが入力されているシートです。 dicT(key) = 1 #3/#4さんの話も、#1/#2さんの話も熱心に正答をいただいているのに、解決に向かわないのは、残念です。 　　Do ③　は仮にA～F列の1か月分（複数ヶ月でも）データを消去した場合にA～F列は上詰め、 子供が小さいのでご連絡が遅くなる可能性がありますが、ご回答いただけた方には必ずお返事を致します。 C列の該当する会社の行に1を入力します（複数あり） 上記の内容が正しいならこのシートでは上から順に新規で入力された値に対して順々に数値を振り分けているという事になります． 参考：コピーを複数シートに分割する場合. sub ボタン8_click() Set sh2 = Worksheets("結果") ' 抽出先シート ・入力されたセルの行の位置を引数に入れる． Else cnt = 0 他の方が回答されていらっしゃるように、わざわざVBAでやる必要性がないと思うのですが・・・ Range(Cells(2, "G"), Cells(i, "L")).Cut Cells(lastRow1 + 1, "A") Excel PowerQuery シートの値をデータ絞り込みに利用できるようにする, Excel PowerQuery インデックス(行番号) 項目ごとに振り直される連番を付ける, Excel VBA コピー作業を普通のコピーに近付けるマクロ 個人用マクロブックに組み込んで使おう, 今回の記事で、メインとして紹介したいのは、Excelブックの中にあるシートを安全に切り離す方法だ。たとえばExcelブックに10個のシートが含まれてるけど、その中で特定の1個のシートだけを抜き出して使いたいという場面は多い。そういうとき、=シート01!A1 + シート03!B1 + シート05!D1みたいに他のシートとの繋がりがある計算式が入っていたりすると、その計算式を外しておかないとシート中の数値とかが変わったりエラーになってしまうことがある。それを確実に防いで安全にシートを切り離す方法は、多くの人が知らないはずだし、実際マクロをちょっと使わないと難しい。今回の記事では、それをお手軽にやる方法を紹介するので、これを取り入れれば日常のExcelの作業効率を大幅に向上できるだろう。, まず、Excelにおけるリンク解除の話から。上記の記事でも触れたのだが、Excelで、他のブックを参照するリンクがある計算式というものは厄介で、標準機能だけでは外しきれないことが多い。そこで、次に示すようなマクロを個人用マクロブックに入れておき、リンク解除においては使用することが望まれると思う。, 途中にある「appSet」「appReset」というやつについては、こちらを参照。, Excel 個人用マクロブック(Personal.xlsb) 最初に必ず入れておく高速化用コード, そんでメインとなるのは「Sub breakLinks()」ってやつですけど、ぶっちゃけこのマクロ自体はまんま他所からのパクリである。そんなものを臆面もなく書いているが、それというのも私は、この「Sub breakLinks()」を単体で使うことがあまりないからだ。, 私はこのリンク切断マクロを、次に示すbreakLinksAndSheetCopyというマクロにくっつけて頻繁に使用している。, ちょっと解説すると、最初に「ActiveWindow」というのが出てくるが、ユーザが複数シートを同時にShiftボタンとかで選択している場合に、それらを指定するには「ActiveWindow.SelectedSheets」としなければならない。単一のシートなら「ActiveSheet」とシートを指定するのでいいのだけれど、複数シートだと「ActiveWindow」とまずウインドウを指定しないといけないのが紛らわしいところ。そんでそれらユーザが選択した複数シートを、新しいブックに切り離した上で、breakLinksでリンクを切断するってだけの短いマクロが、上記の「breakLinksAndSheetCopy」だ。, 別の記事でも取り上げたけど、Excelのブックを上書き保存不可能な状態にして編集するっていうのは、多くの作業において有効な手段となる。そしてこの「breakLinksAndSheetCopy」マクロにより、例えば集計シートとマスタシートがあったとき、マスタシートの方だけを安全に切り離して別ブックとして扱うことが出来る。その別ブックを煮ようが焼こうが、元のブックに何の影響も心配しなくて良い。冒頭で述べた、Excelブックの中にあるシートを安全に切り離す方法ってやつだ。このマクロも、是非とも個人用マクロブックに追加しておくと良い。私はこの「breakLinksAndSheetCopy」マクロを1日に10回くらいは使っている。これをすれば、Excelファイルの扱いの安全度が格段に上がるからね。, こんにちは。 myFile="C:¥保存フォルダ￥”＆a＆”.xlsx" ツイート 概要 Excel 2007バージョンまでは、複数のハイパーリンクを一括削除する機能がありませんが、[形式を選択して貼り付け]を利用した方法で対応することができます。 それをA～F列は7か月分だけにし、残りはG～L列に移動させたい！という解釈です。 下記のようなプログラムを作成しました End Sub, こんにちは シート1のB2以降は商品単価が入り、事業所単位で単価の違うものを一覧化してあります。 要は、スポーツのルールやっと覚えたくらいのExcelの知識で、仕事で使ってもいるけど、そこから伸び悩んでる方が対象ってことですね。 lastRow2 = Cells(Rows.Count, "G").End(xlUp).Row ※D3,D4の番号について→002は固定です。5は会社の番号のA列から。2は行カウントの数字ですが、一番上にある行カウントを以下の1が入力された行に自動表示。 A2＝1　B2　〇〇　C2＝"" D2="" 　　End If という数式を入れフィルハンドルで下へコピー！ 良い方法ありませんか。, 何度実行しても良いようになっていますのでもう一度最初から実行してみてください。 こんにちは　リンク解除について相談させてください =OFFSET(E$1,,,COUNTIF(E:E,"? よろしくお願いいたします。 よろしくお願いします, アドバイスありがとうございました If Cells(i, "G") = "小計" Then とすることでも、同様の結果を得ることができます。 　If Not fnd Is Nothing Then lastRow1 = Cells(Rows.Count, "A").End(xlUp).Row lastRow1 = Cells(Rows.Count, "A").End(xlUp).Row >シートにリストを予め用意しておくと膨大なリストからたくさんスクロールしてから選択するようにになってしまう為 具体的には作業シートをSheet2に置くなら，A列の範囲で重複データを削除したものをシート2のＡ列にコピーすれば Break linkを使えば何とかなるとということまでは分かったのですが、コードの使い方が調べてもわからなかったためご質問させいただきます。 sh1.Cells(row, "C").Value = dicT(key) '数量 　　Next 「Excelを3年くらいやって日常的に触ってるけど、からきし上手くならないわー。もう素質がないのかなー。」くらいに思ってる方が読まれると良いかと思います。 ファイルを開くたび、「このブックには、ほかのデータソースへのリンクが含まれています。」という警告が出ます。 「データ」タブの「リンクの編集」より確認し、必要の無い物であることが分かったので 「リンクの解除」を選択した上で、保存しても、次回開く際に同様の警告が表示 … Sheet2!G2セルに （例えばC3セルに入力したなら引数は3，C5セルに入力したなら引数は5） https://support.office.com/ja-jp/article/INDEX-%E9%96%A2%E6%95%B0-a5dcf0dd-996d-40a4-a822-b56b061328bd#__reference_form sh1.Rows("2:" & Rows.Count).ClearContents 'Sheet1の2行目以降をクリア コード End sub End Sub 冒頭に For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row Excel で、他のワークシートのデータをコピーした際に、リンクの設定が行われないようにする方法を教えてください。複数のブックから、総まとめのブックを作り、これを配布するので、リンクの設定があると困るのです… If WorksheetFunction.CountIf(Range("A:A"), "小計") < 7 Then ・上記引数とCellsを利用してCのセ...続きを読む, エクセル2003と2007VBAで使用します。 Set sh1 = Worksheets("情報") ' 抽出元シート =OFFSET(E$1,,,COUNTIF(E:E,"? 　　　fnd.Offset(, 1).Resize(, col).Copy A3＝5　B3　××　 C3＝"" D3=002-5-2 Dim i As Long, c As Range If dicT.exists(key) = True Then 0月売上 としておき、4とか5の数値のみ入力しておきます。 For next構文及び月度毎にピックアップされた売上金額の合計方法など分からず。 冒頭に この作業というのは先ずA列の値で重複する値を削除したものをコピーし，上から1，2，3と番号振り（行カウントの値を決める）を実行するというものです． ＞下記のようなプログラムを作成しました こんにちは リンク解除について相談させてください下記コードのように自動でシートを別ファイルに保存するようにプログラムしたのですが、コピー先のファイルを開くとコピー元のリンクがそのままになってしまっていますコピー先のファイ Su...続きを読む, 縦長の表を2段組みとしたいのですが、下記の条件があります。 With Worksheets("Sheet1") https://support.office.com/ja-jp/article/INDEX-%E9%96%A2%E6%95%B0-a5dcf0dd-996d-40a4-a822-b56b061328bd#__reference_form Excel VBA マクロのシートを削除する方法を紹介します。Sheets.Delete メソッドまたは Worksheets.Delete メソッドでシートを削除できます。複数や選択中のシートをまとめてや、ワークブックなどを指定できます。 意味が良くわかりませんが、『元の行のデータを列データに変えてコピーしたい』という意味と解釈しました。 elm = Split(key, delm) エクセルの外部参照へのリンクを解除する手順を説明します。エクセルの外部参照は便利な機能ですが、参照先のブックが移動された場合などは、エラーになりブックが使えなくなります。エラーになったブックは、リンクの解除を行って修復しましょう。 ツールバーの[コピー]ボタンをクリックします。(クリップボードへコピーします。） 【Shift】キーを押しながらメニューバーの【編集】をクリックします。 (隠されていたメニュー「図の貼り付け」「図のリンク貼り付け」が出てきます。 A7＝5　B7　×× C7="" D4="" Dim sh2 As Worksheet excel2016を使用しています。VBAマクロで作成した新規ブックのリンク解除またはリンクさせない方法を教えてくださいマクロ付きのブックAで、新規ブックBを作成後、ブックAのワークシートをブックBにコピーして保存したところ、ブック あともう一つ確認ですが，質問者さんの立場でこのシートで入力するのはA～C列とE列ですよね？ としておき、4とか5の数値のみ入力しておきます。 Next （拒否られるかと思いきや、動作してしまうようですが） という数式を入れフィルハンドルで下へコピー！ Range("A1").Resize(, 6).Copy Range("G1") Private Sub UserForm_Initialize() ＞②抽出したセル縦に抽出を行う仕様にしたい ・上記引数とCellsを利用してD列に値を出力する． VBAのコードを求めていらっしゃるのなら、回答者側としては、 どうしてもVBAで！という場合、一例です。 sub sumple5() 時間・深夜残業)の算出数式が分かりません。 Dim myFound As Range,my First As Range また、GoToでループの中に飛び込むのもいかがなものかと思いますが… シート1のA列2行目から下に向かって商品名リストが、 dicT(key) = dicT(key) + 1 A3＝5　B3　××　 C3＝1 D3=002-5-2 BG列が品名、CX列が単価です。 多分間違いだと思うので勝手に直しましたが、もちろん「友の会・1月観察会資料2014.xclx」などは「友の会・1月観察会資料2014.xlsx」などの間違いですよね？, ※各種外部サービスのアカウントをお持ちの方はこちらから簡単に登録できます。 a=range("J2").Value [Ctrl]+[Shift]+[Enter]で確定。配列数式、{}で囲まれる。 それに、掲示板の回答は、必ずしも、コピー＆ペーストで動くコードを提供してくれるとは限りません。それだけは心得ておいてください。 とはいえ、ご提示のコードをそのままコピペしてもエラーで動作しないようです。 =INDEX(Sheet1!$B$2:$D$4,MATCH($B2,Sheet1!$A$2:$A$4,0),MATCH($A2,Sheet1!$B$1:$D$1,0)), ●入力シートのA列一番下の事業所リストから、商品名を表示する loop コピーモードを解除したり、別のデータがクリップボードにコピーされるまで、何度でも貼り付け可能です。 引数Destinationを指定すると、クリップボードのコピーとペーストの機能を利用して、指定したセル範囲にデータが貼り付けられます。