Excelの最終行に上行書式などをコピーして一行追加するには

改めて質問さえて頂きます。（すいません）
Excel画像にあるようなデータの最終行のデータと同じように下記要件を合わせ
罫線や書式を一行追加したいと考えております。
　・A列には連番の番号を投入
　・B列には日付を入力させるので日付の表示形式にする
　・C列にはあらかじめデータの入力規則に沿ったリストで選択できるようにし、
　　かつApplication.InputBoxで名前を入力できるようにする。
　　（当然リストにない名前が入力したら拒否される）
　・D列はApplication.InputBoxで何でも入力できるようにする。
　・新たに追加された最終行には上の行の書式と罫線を引き継ぐようにする。
　　（フォントなど書式や表示形式やセンタリングなど）

また応用になりますが、日付や頭文字列を取得して割番号をする事などできるのでしょうか？
例えば2016/5月であれば、201605＋01～99など。。。
もしくは、ABCと固定値があれば、ABC001・・・・ABC999と割連番
初心者ですいません、よろしくお願いいたします。

質問者からの補足コメント

• B列～D列は空欄指定でもできるようにする。

    補足日時：2016/06/30 00:53

No.3 ベストアンサー 回答者： ママチャリ 回答日時： 2016/07/01 22:42

こんな感じです。

参考になれば幸いです。

基本的に上の行をオートフィルでコピーして新しい行を作成しています。最初の基準は先頭データ（シート上では2行目）になるので、書式やセンタリングの設定をする場合は、2行目にしておいてください。

コードの途中にExit Subが入っていますが、そこまでが、「ABC001」対応のコードです。
Exit Sub以降は「日付＋連番」を設定するロジックなので、もし「日付＋連番」にしたい場合は、Exit Subの行を削除して、全ロジックを実行するようにしてください。

キュアティるさんの想定と違うところがありそうですが、あとはお好みで修正して下さい。

Sub sample()
Dim NextRow As Long
Dim r As Range
Dim SeqNum As Variant
NextRow = Cells(Rows.Count, "A").End(xlUp).Row + 1
If NextRow > 2 Then
Rows(NextRow - 1).AutoFill Destination:=Rows(NextRow - 1 & ":" & NextRow), _
Type:=xlFillDefault
Rows(NextRow).Columns("B:D") = ""
Else
Range("A2") = 1
Range("E2") = "ABC001"
End If
Cells(NextRow, "B") = InputBox("日付")
Cells(NextRow, "C") = InputBox("担当")
Cells(NextRow, "D") = InputBox("商品")

Exit Sub 'これ以降は年月＋連番の付番処理！！
Cells(NextRow, "E") = ""
With Cells(NextRow, "B")
If Not IsDate(.Value) Then
Cells(NextRow, "E") = "*** Error ***"
Else
For Each r In Range("B2:B" & NextRow)
If r.Value >= DateSerial(Year(.Value), Month(.Value), 1) And _
r.Value < DateSerial(Year(.Value), Month(.Value) + 1, 1) Then
If SeqNum < r.Offset(, 3).Value Then SeqNum = r.Offset(, 3).Value
End If
Next
If IsNumeric(SeqNum) Then
If SeqNum = 0 Then
Cells(NextRow, "E") = Format(.Value, "yyyymm") & "001"
Else
Cells(NextRow, "E") = SeqNum + 1
End If
Else
Cells(NextRow, "E") = "*** Error ***"
End If
End If
End With
End Sub

この回答へのお礼

ありがとうございます！すごいです、理想な形になっており非常に参考になります。ちなみに、仮にE列以外にもJ列が日付の場合、複数*** Error ***とさせるにはどうしたらよいでしょうか？　また、可能であれば担当者のところをInputboxではなくlistboxで選択できるようにする事もできるのでしょうか？（フォームが予め必要ですか？）

お礼日時：2016/07/03 00:56

No.4 回答者： ママチャリ 回答日時： 2016/07/03 08:54

参考になったようで、こちらとしても嬉しいのですが･･･。

「複数*** Error ***」については、質問内容がアバウトすぎます。想像すらできないので、アドバイスのしようがありません。ごめんなさい。
ListBoxについては、ユーザフォームを作成すれば可能です。ただ、この辺の説明を最初からするとなると、かなりの労力が必要です（実装自体は簡単ですが、プロパティの設定やイベントプロシジャ等、複数のコーティングに対する説明が必要になるため）
とりあえず、ご自身で挑戦してもらって、分からないことがあれば、別途、ご質問されることをお勧めします。

No.2 回答者： ママチャリ 回答日時： 2016/06/30 20:44

何点か確認させてください。

InputBoxではリスト表示はできません。リストから選択させたいのであれば、入力規則のリストで対応することになります。もしくは、ユーザフォームで作るという手もありますが、そこまで必要ですか？

「No.」および「割番号」ですが、例えば、1から３まで振られた後に2の行を削除した場合、３→２に変える必要はありますか？それとも2は欠番のままで良いのでしょうか？
「年月＋連番」は、日付（B列）の年月毎に1からの連番で良いのですよね？（20160601、20160602、20160701･･･）
ABC001のABCはどこから出てきたのでしょうか？もし固定であれば、連番は全体を通しての番号（No.と同じ？）になりますか？
ちなみに、「日付＋連番」と「ABC＋連番」を共存させることはできないと思います。どちらを採用したいですか？

この回答へのお礼

Inputboxでリスト表示できるものだと思っておりました。了解しました。
それではリスト選択なしでInputbox優先でお願いします。
＞「No.」および「割番号」ですが、例えば、1から３まで振られた後に2の行を削除した場合、３→２に変える必要はありますか？それとも2は欠番のままで良いのでしょうか？　⇒そうですね、運用上、削除したものは欠番にしておきたいです。
＞「日付＋連番」と「ABC＋連番」を共存させることはできないと思います。　⇒ごもっともです。共存はできないと思いますが、２パターンのパターンだとどんな表記になるか知りたいです。
よろしくお願いいたします（o_ _)ｏ））

お礼日時：2016/06/30 23:06

No.1 回答者： haijin1996 回答日時： 2016/06/30 01:04

2007以降のExcelなら、テーブルを設定すれば書式や数式は上の行のものがそのまま適用されます。

A列に何か入力されただけでB～D列も書式や数式が設定されます。
なのでInputboxの方は書式云々は全く無視して設定してけっこうです。

>日付や頭文字列を取得して割番号をする事
どこかに日付が入力されているなら、その日付からYEAR関数で年度の数値がMONTH関数で月の数値が出ます。
その後ろの数値は行番号や列番号をもとにすれば連番にもしやすいでしょう。それらは「＆」でもCONCATENATE関数でも好みの方法で連結できるでしょう。

この回答へのお礼

了解いたしました、簡単にテーブル設定をする方法もありましたね、コメントありがとうございます。

お礼日時：2016/06/30 23:07