別のワークブックにアクティブシートをコピーして挿入したいと思っております。
挿入先に同じ名前のワークシートが存在する時はシートを挿入しない、
という場合は自力で実現できました。
ところが、頑張ってみたのですがシートが存在した場合に上書きする方法がわかりません。
※既存のシートを削除して新規のシートを挿入する
すいませんが詳しい方、説明の上手な方、直接、コードで説明できる方、
お手数ですが教えて下さい。よろしくお願いします。
詳細~シートの挿入時に必要な機能、etc.
1)挿入先に同じ名前のシートがある場合、既存のシートを削除して新規のシートを挿入
2)挿入先のワークブックが開いている場合
※「開いていているための挿入出来ない」のエラーメッセージの表示
3)ユーザーフォームのボタンから実行します。
動作が分割される場合はボタン一発で実行できるものでお願いします。
→Callの利用は無理だと思います。
4)挿入前にシート名に「No」の記載がされているか
「はい」、「いいえ」を確認するメッセージを入れたい。
※マクロで「No」を追加するのはなしです。
今後も社内ルールの確認用のメッセージに使う予定ですので。
5)A列とH列に全角文字があれば半角に変換に変換したシートを挿入する
6)便宜上、ワークブックの名前、フォルダーは以下の通りでお願いします
ワークブックの名前
コピー元:コピー元.xlsm
コピー先:挿入先.xlsm
「挿入先.xlsm」のフォルダーはCドライブの
「A」フォルダーの中ということでお願いします。
追記
こちらも参考にトライしてみました、ご参考にどうぞ
↓
https://oshiete.goo.ne.jp/qa/9476111.html
※文字数がオーバーするので、前述の
「挿入先に同じ名前のワークシートが存在する時は、シートを挿入しない場合」
のコードは記載しませんでした。
必要でしたら別途質問を立てて、そちらに貼り付けますので、
お気軽にリクエストしてください。
すいませんがよろしくお願いします。
No.3ベストアンサー
- 回答日時:
No1です。
既にNo2の方が回答されてますので、参考までに。コマンドボタン1をクリックした想定です。
--------------------------------------
Option Explicit
Private Sub CommandButton1_Click()
Const FolderName As String = "c:\A"
Const TrgBookName As String = "挿入先.xlsm"
Dim trgBook As Workbook
Dim srcBookName As String
Dim srcSheetName As String
Dim srcBook As Workbook
Dim wBook As Workbook
Dim wSheet As Worksheet
Set srcBook = Application.ActiveWorkbook
srcBookName = Application.ActiveWorkbook.Name
srcSheetName = ActiveSheet.Name
If MsgBox("シート名は[" & srcSheetName & "]です。" & vbLf & "シート名にNoが記載されていますか?", vbYesNo) <> vbYes Then
Exit Sub
End If
Call ZenToHan("A") 'A列を全角から半角変換
Call ZenToHan("H") 'H列を全角から半角変換
'挿入先のBookが開いているかチェックする
For Each wBook In Workbooks
If UCase(wBook.Name) = UCase(TrgBookName) Then
MsgBox (TrgBookName & "は既に開いています。閉じて下さい。")
Exit Sub
End If
Next
'挿入先のBookが存在するかチェックする
If Dir(FolderName & "\" & TrgBookName) = "" Then
MsgBox (FolderName & "\" & TrgBookName & "が存在しません。")
Exit Sub
End If
'Bookオープン
Set trgBook = Workbooks.Open(Filename:=FolderName & "\" & TrgBookName)
If trgBook.ReadOnly = True Then
trgBook.Close
MsgBox (TrgBookName & "は既に使用されています。")
Exit Sub
End If
'挿入先に該当シートが存在すれば削除する
For Each wSheet In Worksheets
If UCase(wSheet.Name) = UCase(srcSheetName) Then
Application.DisplayAlerts = False
Worksheets(wSheet.Name).Delete
Application.DisplayAlerts = True
Exit For
End If
Next
'シートコピー
Workbooks(srcBookName).Worksheets(srcSheetName).Copy after:=Worksheets(Worksheets.Count)
'Bookクローズ
ActiveWorkbook.Save
ActiveWorkbook.Close
'元のBook,Shhetをアクティブに設定
Workbooks(srcBookName).Activate
Worksheets(srcSheetName).Activate
MsgBox (srcSheetName & "のコピー完了")
End Sub
'全角から半角へ変換する
Private Sub ZenToHan(ByVal col As String)
Dim row, maxrow As Long
maxrow = Cells(Rows.Count, col).End(xlUp).row '最終行を求める
For row = 6 To maxrow
Cells(row, col).Value = StrConv(Cells(row, col).Value, vbNarrow)
Next
End Sub
----------------------------
ご解答ありがとうございます
すごいコードをいつもありがとうございます。
イメージ通り動作するので、本当に感謝してます。
多忙で、勉強がおろそかになっておりますが、
教えて頂いたコードについて、
ちょっとづつ身に着けていこうと思います。
いつも丁寧なご解答ありがとうございます。
機会がありましたらまたお願いします。
No.5
- 回答日時:
No2です。
わざわざ開けておいていただいて恐縮です。「開いているための挿入出来ない」のメッセージは、他の人が開いている場合を想定していて、自分で開いている場合を想定していませんでした。tatsu99さんはそこまで考えていたようで、さすがですね!!
しかし、挿入先ブックって、担当者に公開しているのですか?てっきり、今回のマクロを使うことにより、その存在を担当者から秘匿するものだと思っていました。よって、担当者自身が開いていることは想定しませんでした。
想定していたのは、「今回のマクロを複数の担当者が同時に実行した。」もしくは、「管理者使(ZZ-TOPさん?)が、挿入先ブックで何らかの作業を行っている」等のケースのみです。
どちらにしても、tatsu99さんの回答が完璧なので、この質問もクローズして頂いて結構です。また、追加分の質問も忘れずにクローズしてくださいね。
ご解答ありがとうございます。
なるほど意味がわかりました。
実は昨日、職場のサーバー(NAS)上で確認したところ
上手く機能しました。
お礼の記載をちゃんとしたかったので、
その時、急いで入力せずに、今日、自宅で自分のPCのローカール環境で、
改めて動作チェックしたところ、先のような症状でした。
おっしゃるとおり、ローカルPC上で、
自分で開けていて気が付かないというのはあまりないし、
そこまで想定する必要はないですよね。
わざわざご返答頂きありがとうございます。
今回はいろいろありがとうございました。
機会があればまたお願いします。
こちらもちょっとづつ勉強したいと思います。
ありがとうございました。
※2つ目の質問は削除しておきます。
No.4
- 回答日時:
No3です。
私が提示したソースは、コピー元のA列とH列を半角に変換し、それをコピーしています。
それが都合悪い場合は(コピー元のデータを変えたくない場合)、その旨補足してください。
No.2
- 回答日時:
全角→半角変換は、漢字や平仮名など変換できないケースもありますが、そこは問題ないですか?
Sub sample()
Dim wsFrom As Worksheet
Dim wbTo As Workbook
Dim r As Range
Set wsFrom = ActiveSheet
If MsgBox("シート名に「No」の記載がされているか?", vbYesNo) = vbNo Then
Exit Sub
End If
Set wbTo = Workbooks.Open(Filename:="C:\A\挿入先.xlsm")
If wbTo.ReadOnly Then
MsgBox "開いていているための挿入出来ない"
wbTo.Close
Exit Sub
End If
For Each r In Intersect(wsFrom.UsedRange, wsFrom.Range("A:A,H:H"))
r.Value = StrConv(r.Value, vbNarrow)
Next r
Application.DisplayAlerts = False
On Error Resume Next
wbTo.Sheets(wsFrom.Name).Delete
On Error GoTo 0
Application.DisplayAlerts = True
wsFrom.Copy Before:=wbTo.Sheets(1)
wbTo.Close SaveChanges:=True
End Sub
ご解答ありがとうございます。
お礼が遅くなりすいません。
全角→半角変換は、漢字や平仮名など変換できないケースもありますが、そこは問題ないですか?
↓
大丈夫です。漢字、平仮名はスルーでOKです。
で、、、、お手数でなければ1つ教えて欲しいことがあります。
挿入先が開いている時に
MsgBox "開いていているための挿入出来ない"
が表示されずに、シートの挿入を実行して「挿入先」を閉じてしまいます。
こちらの環境だけかもしれませんし、
私が何か間違っているのかもしれません。
NO.3さんのコードは問題なく動作したので、
もし何かご存じでしたら教えて下されば、うれしいです。
※お手数でしたらスルーでOKです。
今回はご解答ありがとうございました。
機会がありましたら、またお願いします。
No.1
- 回答日時:
補足要求です。
質問1
>4)挿入前にシート名に「No」の記載がされているか
>「はい」、「いいえ」を確認するメッセージを入れたい。
これは、挿入対象となるシートのシート名(アクティブシートのシート名)に「No」が含まれているかという意味でしょうか。
又、[No]が含まれているか否かは、下記の全ケース、含まれていると考えて良いですか。
NoXXX ・・・前方一致
XXXNo ・・・後方一致
XXXNoYY・・・部分一致
No ・・・完全一致
(XXX、YYは任意の文字)
Noの文字であるとみなすのは半角のNo(大文字のN、小文字のo)のみですか。
no(半角小文字のno)、NO(半角大文字のNO)、No(全角のNo)等も[No]の文字であるとみなすのでしょうか。
[No]の文字がアクティブシートのシート名に含まれない場合、確認メッセージを表示し、
応答が「はい」であればコピーを行い、「いいえ」であれば、コピーを行わない。・・・と理解しましたが、あってますか。
([No]の文字がアクティブシートのシート名に含まれる場合は、確認メッセージを表示せず、コピーを行います)
質問2
>5)A列とH列に全角文字があれば半角に変換に変換したシートを挿入する
これは、A列とH列の全行をチェックするのでしょうか。(1行目を含む)
それとも1行目はチェック対象外でしょうか。(1行目は見出し行なのでチェックしない)
質問3
>動作が分割される場合はボタン一発で実行できるものでお願いします。
>→Callの利用は無理だと思います。
これは、サブプロシージャをつくるなという意味であれば、間違っています。
サブプロシージャを作ってもボタン一発で実行可能です。
それとも「→Callの利用は無理」とは、他の意味でしょうか。
サブプロシージャがあっても、ボタン一発で実行可能です。
Private Sub CommandButton1_Click()・・・本体
・・・
call サブプロシージャ
・・・
end sub
private sub サブプロシージャ
end sub
今回、サブプロシージャはつくることはないと思いますが念の為。
(サブのFunctionプロシージャは全角文字があるか否かの判定で作る予定です)
ご解答ありがとうございます。
いつもお世話になります。
ご返答、ご確認の質問を頂きありがとうございます。こちらの説明がわかりにくくてすいません。
順番に行きます。
質問1
>4)挿入前にシート名に「No」の記載がされているか
>「はい」、「いいえ」を確認するメッセージを入れたい。
これは、挿入対象となるシートのシート名(アクティブシートのシート名)に「No」が含まれているかという意味でしょうか。
↓
返答A
そうです。
アクティブシート名が「No3-99」のような形式にする社内ルールになっております。
※Noは1~2桁
※ハイフン以下は1~3桁
※ハイフンを含めて文字は全て半角
[No]の文字がアクティブシートのシート名に含まれない場合、確認メッセージを表示し、
応答が「はい」であればコピーを行い、「いいえ」であれば、コピーを行わない。・・・と理解しましたが、あってますか。
↓
返答B
あっています。ですが補足があります。
メッセージは事務員に社内ルールを思い出してもらうためのものです。
マクロの処理でアクティブシート名の最初の文字が「No」であるか確認する必要はありません。
以下のコードを見て頂くと意味をわかって頂けると思います。
※社内ルールが変更になった時に便利なので、このコードをよく使います。
------------------------------
Sub AAB_メッセージボックス_02()
'「変数」にメッセージボックスの結果を入れる
変数 = MsgBox("【はい】か【いいえ】か選んで下さい", vbYesNo)
'条件文で変数に結果を入力する
If 変数 = vbYes Then
'【はい】をクリックしたとき
'vbYesが「True」を返してきたとき
MsgBox "【はい】ですね?実行します"
'処理のコードを記載
Else
'【いいえ】をクリックしたとき
'vbYesが「False」を返してきたとき
MsgBox "【いいえ】ですね?訂正してから実行して下さい"
'マクロの終了
End If
End Sub
------------------------------
文字数がオーバーしたので、残りは補足に記載するか
別途、「同じ名前-2」で質問を立てます。
いったんここで区切ります。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- その他(Microsoft Office) 関数を教えてください 1 2022/12/13 15:56
- Visual Basic(VBA) エクセルのマクロで対象ごとにシート分けしてその内容をセルに書き込みたい 9 2022/08/24 13:23
- Excel(エクセル) Excel VBA 指定フォルダに格納されている全エクセルファイルに指定シートを挿入する方法について 1 2022/08/22 11:53
- Visual Basic(VBA) シート間で同じ値があったらコピペ 1 2022/05/08 09:39
- Visual Basic(VBA) 特定の文字を含むシートだけマクロ処理をしたい 1 2023/05/22 01:43
- Excel(エクセル) フォルダ内の全ブックのシート名を変更したい 7 2022/09/22 21:34
- その他(Microsoft Office) Wordを変換してExcelに挿入 2 2022/07/04 23:59
- Visual Basic(VBA) 【VBA】Excelで罫線を引きたい 3 2022/07/14 12:04
- Visual Basic(VBA) excelにて、特定の列に数字入力してあれば、入力してある行コピーして 別ファイルに張り付ける 2 2022/08/11 05:33
- Excel(エクセル) エクセルで”入力シート”の文字書式の変更を”出力シート”で同じ文字書式で印刷したいです。VBA希望 4 2023/04/24 11:07
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
別のシートから値を取得するとき
-
VBAの天才来てください
-
【ExcelVBA】全シートのセルの...
-
ユーザーフォームに入力したデ...
-
VBA 存在しないシートを選...
-
エクセルのシート名変更で重複...
-
XL:BeforeDoubleClickが動かない
-
同じ作業を複数のシートに実行...
-
ExcelVBA シート名を複数セルか...
-
エクセルのマクロでアクティブ...
-
【VBA】指定した検索条件に一致...
-
excelのマクロで該当処理できな...
-
VBAでオブジェクト変数にsetし...
-
エクセル・マクロ シートの非...
-
ブック名、シート名を他のモジ...
-
Excelマクロのエラーを解決した...
-
VBA 検索して一致したセル...
-
Worksheet_Changeの内容を標準...
-
シートが保護されている状態で...
-
エクセルVBAでダブルクリックを...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
別のシートから値を取得するとき
-
ユーザーフォームに入力したデ...
-
Excelマクロのエラーを解決した...
-
excelのマクロで該当処理できな...
-
同じ作業を複数のシートに実行...
-
ExcelVBA シート名を複数セルか...
-
【ExcelVBA】全シートのセルの...
-
Excel マクロについての相談
-
VBA 存在しないシートを選...
-
実行時エラー'1004': WorkSheet...
-
特定の文字を含むシートだけマ...
-
ExcelのVBAのマクロで他のシー...
-
ブック名、シート名を他のモジ...
-
XL:BeforeDoubleClickが動かない
-
VBA 複数の各シートに行を追加...
-
エクセルのシート名変更で重複...
-
【Excel VBA】Worksheets().Act...
-
シートが保護されている状態で...
-
Excel VBA 複数行を数の分だけ...
-
for 文の 繰り返し処理に使える...
おすすめ情報
マクロでの処理です。
VBAのカテゴリなので書きませんでしたが
一応、付け加えておきます。
よろしくお願いします。
すいません
続きはの返答は別の質問を立ててこちらに記載しました
↓
【EXCEL】別のワークブックにアクティブシートをコピーして挿入したい-2
https://oshiete.goo.ne.jp/qa/9697511.html
みなさんご解答ありがとうございます。
多忙になったので、夜以降に拝見させて頂きます。
追記
文字数制限にひっかかた場合、以下のページに
記載するかもしれないです。
↓
【EXCEL】別のワークブックにアクティブシートをコピーして挿入したい-2
https://oshiete.goo.ne.jp/qa/9697511.html
ご解答ありがとうございます。
No.2さんの、お礼の件があるので、
しばらく質問はあけておきます。
※タイミングをみて閉じますので、
スルーの場合もお気遣いなさらずにOKです。
みなさん、いつも教えて頂いてありがとうございます。