[[20170620095252]] 『マクロでのシート作成・シートのコピーについて』(はにわ) ページの最後に飛ぶ

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

 

『マクロでのシート作成・シートのコピーについて』(はにわ)

はじめまして
シートの新規作成とシートのコピーでマクロを組んでみましたがうまく動かず困っています。

やりたいのはコピー先のブック・シートを選択し選択シートの隣にシートを新規作成し選択していたシート内容をコピー(最初から選択シートのコピーでも可)したの後だ選択したシートの内容を削除

コピー元のブック・シートを選択し最初に選択したブック・シートに張り付け
以上のことをできるようにしたいです。

挿入する新規シート名は出来れば”選択シート名(コピー)”のように出来ればありがたいです。
現状はやり方が分からずできておりません。

下記のマクロ組んでみましたが新規作成のシート位置がうまくいかず困っております。

MsgBox "コピー先のファイル・シートを選択", vbOKOnly, "タイトル"

Application.Dialogs(xlDialogActivate).Show
Set Sh = ShowSelectSheetDialog() '貼り付け先ファイルとシートを選択

Dim ws As Worksheet
Set ws = ActiveSheet

Worksheets.Add Before:=ActiveSheet

ws.Activate

Cells.Select
Selection.Copy

ActiveSheet.Previous.Select

ActiveSheet.Paste '新規シートへのコピー完了

MsgBox "コピー元のファイル・シートを選択", vbOKOnly, "タイトル"

Application.Dialogs(xlDialogActivate).Show 'コピー元選択
Set Sh = ShowSelectSheetDialog()

Cells.Select
Selection.Copy

ws.Activate
Set ws = Nothing

Cells.Select
ActiveSheet.Paste

よろしくお願いします。


ShowSelectSheetDialog() って、自作関数かと思いますが、中身も貼ってください。 シートの選択を行っているのでしょうか?
また、Activeなシートがあっちにいったりこっちにいったりして判りにくいので、WorkSheetオブジェクトに代入し、元と先を明示するほうが良いでしょう。

あとは、「新規作成のシート位置がうまくいかず…」というのは、どういう意味でしょう? シート位置というのは、シートの並び順ですか? アクティブシートの前に挿入してますよね? コーディング通りで問題無いように見えますが…。
(???) 2017/06/20(火) 13:00


返信ありがとうございます。

ダイアログは

' // シート選択ダイアログを表示
' // 戻り値: 選択されたシートオブジェクト キャンセル時:Nothing
Public Function ShowSelectSheetDialog() As Worksheet

    Dim ShBackup As Worksheet
    Application.ScreenUpdating = False
    Set ShBackup = ActiveSheet
    With CommandBars.Add(Temporary:=True)
        .Controls.Add(ID:=957).Execute
        .Delete
    End With
    ' Return
    If Not ActiveSheet Is ShBackup Then
        Set ShowSelectSheetDialog = ActiveSheet
    End If
    ShBackup.Select
    Application.ScreenUpdating = True

End Function

です。

上手くいかないというのはダイアログから指定したシートの隣に新規シートを挿入したいのですが
選択したシートと別のシートの横に挿入されたりしてしまいます。

よろしくお願いします。
(はにわ) 2017/06/20(火) 13:44


選択したシートオブジェクトを返す関数なのですね。 ならば、Worksheets.Add Before:=ActiveSheet を、ActiveSheet ではなく、sh に変えれば、選択したシートの前に挿入されるかと思いますよ。
(???) 2017/06/20(火) 13:52

返信ありがとうございます。
おっしゃるように挿入される位置は理想通りになりましたが今度は鼠入後の貼り付けがずれる様に
なってしまいました。

すみませんがご教授願えますでしょうか?
(はにわ) 2017/06/20(火) 14:24


ずれる、という表現が判りません。セル位置がずれる? 違うシートをコピーしてしまう? 違うシートに貼ってしまう?
とりあえず、貴方のロジックを言葉で書くと、以下になります。コピー元シートを選択した後に、この情報を全く使っていないあたりが問題でしょうか? 元→新シート→元と、同じ内容をあっちに貼って、逆に元に貼って、と、無意味な事をしているコードに見えます。

貼付先ブックとシートを選択。
選択したシートの前に新シート挿入。
元のアクティブシートをコピー。
新シートにペースト。

コピー元ブックとシートを選択。
新シートをコピー。(シートオブジェクトを指定していないから、最後にアクティブだったシートをコピーする)
元のアクティブシートにペースト。
(???) 2017/06/20(火) 14:51


返信ありがとうございます。
上手く伝えれずにすみません。
ずれるというのは違うシートに張り付けてしまうことです。

どういう風にシート指定すればよいか教えていただけませんでしょうか?
(はにわ) 2017/06/21(水) 13:25


後半はコピー元指定しているのは正しいとして、どのシートからどのシートに貼り付けるのが正解なのでしょう? 今は無指定なので、元のシートに上書きしちゃっています。
ws:元のアクティブシート
sh:後半で選択した、コピー元シート

例えば、コピー元シートからコピーするのなら、Cells.Select 部分を、sh.Cells.Select に変えるとか。
コピー先はどこにしたいのかさっぱり判りませんので、答えようがないです。新シートは元シートのコピーだから、元シートに貼れば良い? でもそれなら、「違うシートに貼ってしまう」というのと矛盾するし…。(違うシートをコピーしてしまう、だったら判るのですが…)

とりあえず、貼り付ける箇所にブレークポイントを設定して止めてみて、ws.name、sh.name、ActiveSheet.name がどうなっているか確認してみてください。
(???) 2017/06/21(水) 14:58


上手くいかないのは2度目のダイアログで指定したシートを最初にダイアログで選択したシートにコピーするところです。
新規作成したシートの方はうまくいっております。

現状シートの指定方法が悪いのか違うシートに張り付けされてしまいます。

当方の知識ではなぜ違うシートに張り付けられるのかわからず困っております。

(はにわ) 2017/06/21(水) 16:30


2回目に選択したシートを、1回目に選択したシートに貼りたかったのですか!

そうなると、現状は1回目も2回目も同じshという変数を使い回しているので、このままでは無理です。変数をもう一つ増やして、後の選択シートは sh2 とかに変えて、sh2 をコピーして sh に貼り付けるように変えてみてください。
(???) 2017/06/21(水) 18:53


色々やってみたのですがうまいっていない箇所が分かりました。
2回目のダイアログでのシート選択が何故かどのシートを選択してもsheet1を開いてしまっているようです。

1回目のシート選択・シート作成・貼り付けまでは問題なく動いております。

Sub Macro()
'
' Macro Macro
'

MsgBox "コピー先のファイル・シートを選択", vbOKOnly, "タイトル"

Application.Dialogs(xlDialogActivate).Show
Set Sh = ShowSelectSheetDialog() '貼り付け先ファイルとシートを選択

Worksheets.Add Before:=Sh

sh.Activate

Cells.Select
Selection.Copy

ActiveSheet.Previous.Select

Range("A1").Select
ActiveSheet.Paste '新規シートへのコピー完了

MsgBox "コピー元のファイル・シートを選択", vbOKOnly, "タイトル"

Application.Dialogs(xlDialogActivate).Show 'コピー元選択
Set Sh2 = ShowSelectSheetDialog()

Cells.Select
Selection.Copy

sh.Activate

Cells.Select
ActiveSheet.Paste

End Sub

(はにわ) 2017/06/22(木) 09:32


コメント返信:

[ 一覧(最新更新順) ]


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