エクセルで入力シートから別シートに転記・蓄積について

質問

エクセルＶＢＡで入力シートのA1:D5（５行）の範囲を別シート（DBシート）の転記・蓄積させる方法を教えてください。また、入力データがA1:A5（１行）のときもあれば、A1:D5（５行）の場合ですが・・・

Sub test01()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim x As Long, y As Long
Set ws1 = Sheets("入力")
Set ws2 = Sheets("DB")
x = ws2.Cells(Rows.Count, "A").End(xlUp).Row + 1
y = ws1.Cells(Rows.Count, "A").End(xlUp).Row
ws1.Cells(1, "A").Resize(y, 4).Copy ws2.Cells(x, "A")
End Sub

の場合、数式等、入力値すべてが転記となってしまいます。
値だけ転記する方法はありませんか？
宜しくお願いいたします。

回答No.5

merlionXX · ベストアンサー

> 入力シートにA1:D5の5行のセルには関数が入力されております。
> 転記したいのはそれに反映された値（文字列や数字）のみで、値が出る行数は可変です。

merlionXXです。
そういう前提は最初から書かなければ回答者にわかるはずもないことでしょう？
それにこれだけではまだ情報がたりませんので、以下の解釈で回答します。

.現在のコードがA列で表示の有無を判定しているので、それを踏襲しましたが、A列に表示がある行を対象にします。
1行だけでおわる場合、2行目まである場合、5行目まで等、値が出る最終行が可変ということですね？途中に空白行はないものとします。

Sub test02()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim x As Long, y As integet
Set ws1 = Sheets("入力")
Set ws2 = Sheets("DB")
x = ws2.Cells(Rows.Count, "A").End(xlUp).Row + 1
For y = 1 To 5
If ws1.Cells(y, "A").Value = "" Then Exit For
Next
ws1.Cells(1, "A").Resize(y - 1, 4).Copy
ws2.Cells(x, "A").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub

お礼

ご回答ありがとうございます。
一部エラーがでましたが、なんとか自力で修復しまして、希望のＶＢＡができました。皆様のご協力が無ければ出来ない非力さと説明不足であった非礼に反省しきりです。ありがとうございました。

回答No.6

imogasi

#3です。
＞空欄行の削除コードを新たに作成しなければならないのでしょうか
実情データ例が質問に書いてないし、説明も無いのでわからなかった。
コピー元とコピー先があるとして、回答はコピー先のことを主に書きました。コピー元のセル範囲に空白行とか、集積したくないデータがあれば、コピー元のセル範囲をコピー（一括）ではなく、対象全行について、空白か、（例）数字データかなどを判定して、集積に相応しい行をコピー先に持ってくる。
その（持ってくる）とき、セル範囲一括コピー・貼り付けでなく、１行づつコピー貼り付けするか、、または参照・値代入（累積シート代入先＝入力シート代入データ）方式をとったほうが、質問のような心配もなくなる。列は必要なだけ（例Ａ－Ｈ列列）繰り返す。
初心者はコピー貼り付けを好むようだが、ビジネスなど値中心のデータが多いので参照・値代入も考えてみるほうが良い。
For i=5 to 入力シートの最終行
IF i 行が累積すべき条件を満たすか聞く
for J=1 to 9　'Ａ－Ｊ列について
ws2.Cells(x,j)=ws1.Cells(i,j)
Next j
x=x+1 '累積シートの行ポインタ
end If
Next i

お礼

アドバイスありがとうございます。なんとか希望のＶＢＡができました。
ありがとうございました。

回答No.4

merlionXX

> 転記はされるのですが追記が必ず５行目からとなり、４行以下の転記した場合、空白行できてしまいます。

質問の文で提示されたコードはwasabi033さんが書いたのですよね？
なら意味は分かるっていると思いますが、追記が必ず５行目からとなるようなコードではありません。

仮にDBシートがまったく空白、入力シートにA1:D5の5行のデータがあった場合は、DBシートのA2:D6に転記されます。
次に、入力シートにA1:D1の1行のデータがあった場合は、DBシートのA7:D7に追加されるはずです。空白行はでません。

なお、行の判定はwasabi033さんお書きのコードではA列でのみおこなっています。
ひょっとして入力シートのA列に、入力されたデータ以外にスペース等、目に見えないデータが入っていませんか？

お礼

お騒がせしております。
説明が不十分でした。申し訳ありません。
入力シートにA1:D5の5行のセルには関数が入力されております。
転記したいのはそれに反映された値（文字列や数字）のみで、値が出る行数は可変です。
ＶＢＡは初心者ですので、いろいろ教わって書いたコードです。
宜しくお願い致します。

回答No.3

imogasi

転記すべきタイミングで（安易なのは「入力完了」のようなボタンを設け、そのクリックイベントに累積処理コードを書く）、累積するシートの最終行をx = ws2.Cells(Rows.Count, "A").End(xlUp).Row
で知りそのあとに貼り付ける。
コピー貼り付けはセルの値＋書式＋コメント＋・・を取ってくるというのは常識です。
値だけというのは
形式を選択して貼り付けー値の操作のマクロの記録をとると
Selection.Copy
Range("E3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
のようなのが分りますから、それを使えば仕舞いです。

お礼

imogasi様　お世話になります。
フォームコントロールボタンにてクリックイベントのコードで試したところ、蓄積時に空欄行が出来てしまいます。
入力シートのA1:D5には関数式があり値だけの転記となりますが、確かに数式は転記されませんでした。
空欄行の削除コードを新たに作成しなければならないのでしょうか？

回答No.2

merlionXX

No1です。
一行書きもれてました。

Sub test01()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim x As Long, y As Long
Set ws1 = Sheets("入力")
Set ws2 = Sheets("DB")
x = ws2.Cells(Rows.Count, "A").End(xlUp).Row + 1
y = ws1.Cells(Rows.Count, "A").End(xlUp).Row
ws1.Cells(1, "A").Resize(y, 4).Copy
ws2.Cells(x, "A").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub

に訂正します。

回答No.1

merlionXX

値だけ転記する方法の質問なんですね？
では、wasabi033さんのコードをそのまま生かして

Sub test01()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim x As Long, y As Long
Set ws1 = Sheets("入力")
Set ws2 = Sheets("DB")
x = ws2.Cells(Rows.Count, "A").End(xlUp).Row + 1
y = ws1.Cells(Rows.Count, "A").End(xlUp).Row
ws1.Cells(1, "A").Resize(y, 4).Copy
ws2.Cells(x, "A").PasteSpecial Paste:=xlPasteValues
End Sub

お礼

早速のお返事ありがとうございます。
試した結果、ＤＢシートに転記はされるのですが追記が必ず５行目からとなり、４行以下の転記した場合、空白行できてしまいます。空白行が出来ないようには出来ませんか？