元データのシートにあるボタンを押すと、後ろのシートにデータをそのままコピーされてシートが追加できるようにしたい

質問

大変困っています。

会員マスタという元データを作成し、そのシートに「シート追加」という
ボタンを作りました。

そのボタンを押すと、元データのシートの後ろにそのまま同じデータがコピーされて追加される様にＶＢＡで設定したいです。

・行などはずれない様に設定したい。
・シート名は変更できるようにしたい。
・コピーして追加したシートにはボタンは表示されないようにしたい。

急いでます。知恵を貸してください。

回答No.4

fumufumu_2006 · ベストアンサー

どんどん作るsheetに同じ名前は付けられませんので、連番にします。

'sheetの存在チェック
Private Function isExistSheet(sheetName As String) As Boolean
Dim ws As Worksheet
For Each ws In Worksheets
If ws.name = sheetName Then
isExistSheet = True
Exit Function
End If
Next
isExistSheet = False
End Function

'新しいシート名の検索
Function GetNewSheetName(newSheetName As String) As String
Dim n As Integer
n = 1
Do
If isExistSheet(newSheetName & str(n)) = False Then
Exit Do
End If
n = n + 1
Loop
GetNewSheetName = newSheetName & str(n)
End Function

Private Sub ボタン11_Click()
Dim ws As Worksheet
Dim newSheetName As String
newSheetName = "新しいシート" '追加シートの先頭名(適当な名前を付けてください)

Set ws = Worksheets.Add(after:=Worksheets(Worksheets.Count)) '新しいシートを最後のシートの後ろに作る
Sheets("会員マスタ").Cells.Copy Destination:=ws.Cells(1, 1) '会員マスタのCellデータを新しいシートにコピー
ws.name = GetNewSheetName(newSheetName)'新しいシート名
End Sub

お礼

このようにやりたかったんです。
ありがとうございます。
すごいですね！！
無事に完了しました。
ご丁寧に
ありがとうございました。

回答No.3

fumufumu_2006

Private Sub シート追加_Click()
の部分を
Private Sub ボタン11_Click()
にしたらどうでしょうか？

CommandButtonには、オブジェクト名と、ボタンに表示される文字（Caption)があります。
エラーからすると、「シート追加」と言うボタンは、オブジェクト名が「ボタン11」でCaptionが「シート追加」になっているんじゃないかと思います。

上記でもだめなようなら、デザインモードでコマンドボタンをダブルクリックしてみてください。
自動的に
Private Sub ボタン11_Click()

End Sub

と言うモジュールが作られると思います。
その中に回答のモジュールをコピーしてみてください。

補足

出来ました！！ありがとうございます。
あともう1つだけお願い致します。
ボタンを押すとどんどん後ろにシートが追加されるようにしたいのですが、それにはどうしたらよいですか？

回答No.2

fumufumu_2006

これではどうでしょう

Private Sub シート追加_Click()
Dim ws As Worksheet
Set ws = Worksheets.Add(after:=Sheets("会員マスタ")) '新しいシートをsheets("会員マスタ")の後ろに作る
Sheets("会員マスタ").Cells.Copy Destination:=ws.Cells(1, 1) '会員マスタのCellデータを新しいシートにコピー
ws.Name = "新しいシート" '追加したシートの名前を変更
End Sub

補足

なんどもすみません。
マクロの内容はばっちりなんだと思うのですが、設定の仕方が悪いらしく
ボタンをクリックすると「マクロ′会員マスタ.xls!ボタン11＿click'が見つかりません」と出てしまいます。
どのように設定したらよいでしょうか？

回答No.1

fumufumu_2006

こんなのではどうですか？
Private Sub CommandButton1_Click()
Dim ws As Worksheet
Set ws = Worksheets.Add(after:=Me)
Me.Cells.Copy Destination:=ws.Cells(1, 1)
ws.Name = "新しいシート"
End Sub

補足

ありがとうございます。早速やってみたのですが、ボタンを押しても見つかりませんとメッセージが出てしまいます。また、MEの部分を変更してくださいと出るのですがどうしたらいいでしょうか？
すみません、VBA初心者なので・・・。
ちなみにさくせいしたボタンの名前は”シート追加”です。
元データが入っているシートの名前は"会員マスタ”になります。