ＶＢＡ 任意のシートからコピーを始める。

質問

教えてください。
全てのシートをコピーして一つのシートにまとめるプログラムシートを作成しました。
1番目のシートからコピーを始める場合は
For i = 2 To Worksheets.Count
2番目のシートからコピーを始める場合は
For i = 3 To Worksheets.Count
とすればよいのですがこれだといちいちモジュールコードを出して数字を変更しなければならず面倒です。
そこでユーザーフォームのコンボボックスに任意の数字を入れてクリックを押せば希望するシートからコピーを始めるプログラムを作成してみましたがうまくいきません。どなたか教えてくださいませんか。
Sub matome()
Dim i As Integer
Dim lRow As Long, lCol As Long, lRow2 As Long, lRow3 As Long, SNo As Integer

'----何番目からコピーを始めるかを決定します
With UserForm2
SNo = .ComboBox1.value
End With
For i = 1 + SNo To Worksheets.Count
With Worksheets(i)
lRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
lCol = .Cells(1, Columns.Count).End(xlToLeft).Column
'----シートのデータが2行以上の場合にコピーします
If lRow >= 2 Then
lRow2 = Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row + 1
If lRow2 < Worksheets(1).Cells(Rows.Count, 5).End(xlUp).Row + 1 Then lRow2 = Worksheets(1).Cells(Rows.Count, 5).End(xlUp).Row + 1
.Activate
.Range(Cells(2, 1), Cells(lRow, lCol)).Copy Worksheets(1).Cells(lRow2, 1)
End If
End With
Next i

回答No.6

kkkkkm · ベストアンサー

> DIM SNo As Integer
> With UserForm2
> SNo = .ComboBox1.value
> End Withとしてマクロを実行したときに
> 「実行時エラー’13’: 型が一致しません」とエラーが出てしまうことです

コンボボックスで数値を選択してると思いますのでエラーが出るのは変ですが(こちらでテストしたところ全角の１、２、３で設定してもエラーにはなりませんでした)
とりえず以下のように強引に数値型に変更するという手もあります。
SNo = Val(.ComboBox1.Value)

回答No.7

kkkkkm

No6の補足です。

先に数値かどうか判定するというのも

Private Sub CommandButton1_Click()
If IsNumeric(ComboBox1.Value) = False Then
MsgBox "入力された値が数値ではありません"
ComboBox1.Value = ""
ComboBox1.SetFocus
Exit Sub
End If
Call matome
Unload Me
End Sub

お礼

#No6について
SNo = Val(.ComboBox1.Value)
とすればうまくいきました。有難うございました。String .textでもうまくいきますが、Integerにした時の数字型への変換方法がわかれば応用できるのかなと思います。
UserForm2には
Private Sub UserForm_Initialize()
Dim i As Integer
For i = 1 To 10
UserForm2.ComboBox1.AddItem i
Next
End Sub
でプルダウンで半角数字が入るようになっているので全角数字に対応する必要はないかと思いますが、数値かどうかを判定する方法も知っておいたほうが役に立ちますね。
ありがとうございました。これから質問するときはポイントを押さえた質問をします。

回答No.5

kkkkkm

No4の補足です。
SNo = Application.InputBox(Prompt:="何番目のシート？", Type:=1)
の場合×で閉じた場合SNoにはFalse(Falseは数値で0になります)が入りますので
If SNo = False Then Exit Sub
でプロシージャを終わらせてください。でないと、その後のコードがSNo=0の状態で実行されてしまいます。

回答No.4

kkkkkm

inputboxでうまくいったようですが、ちなみにUserForm2が開いた状態でmatomeが実行されるように実装はされてますよね。

クリックで閉じた後にmatomeが実行されていればエラーになりますが、それが質問にあるうまくいっていなかったという事でしょうか。

でしたら、
クリックボタンがCommandButton1でしたらプロシージャに
Private Sub CommandButton1_Click()
matome
Unload Me
End Sub

としてください。

なお、matomeは標準モジュールもしくはUserForm2のモジュールに記載されていないとエラーになります。

また、
SNo = Application.InputBox(Prompt:="何番目のシート？", Type:=1)
とするとTypeでデータの型を指定できます。指定以外の型を入力してOKするとエラーメッセージが出て入力を受け付けません。
型およびその他の細かな引数については、以下のページの「InputBoxの引数」を参照してください。
http://officetanaka.net/excel/vba/tips/tips37.htm

補足

UserForm2には以下のコードを記載してあり私が質問したモジュールコードにリンクできるようになってあります。
Private Sub CommandButton1_click()
Call matome
Unload Me
End Sub

また私が質問したかったのは
DIM SNo As Integer
With UserForm2
SNo = .ComboBox1.value
End Withとしてマクロを実行したときに
「実行時エラー’13’: 型が一致しません」とエラーが出てしまうことです。この質問ポイントを省いてしまったためにわかりにくい質問となってせっかく閲覧してくださった皆さんに迷惑をかけてしまいました。
ちなみにこれは
DIM SNo As string
With UserForm2
SNo = .ComboBox1.text
End With
に変更したらうまく実行できるようになりきちんと希望するシート番号からコピーを始めてくれます。
しかし、stringは文字型であり本来なら整数型のintegerを使うべきですが、SNo = .ComboBox1.valueではエラーが出てしまいます。このvalueを何に変更すべきでしょうか？またすべての値を表すVariantに対してValueを使ってもうまくいきますが、後学のためにIntegerには何を使うべきかをお教えいただけるとありがたいです。

回答No.3

imogasi

#2です。補足に関して
Dim n As Integer でよいとおもいます。

補足

#2さんへ
Dim n As Integer ではうまくいかなかったのですがDim n As Stringとすればうまくいくようです。

回答No.2

imogasi

何番目のシートをn=Inputbox("何番目")
とすればよいのでは。
ユーザーフォームなど、むつかしいこと、かっこよいと思うのか、をするのは、熟達してからでよかろう。
たとえばテスト用に
５枚の各シートのＡ１に１，２，３，４，５と入れて、下記をテスト実行
Sub test01()
n = InputBox("番目")
For i = n To Sheets.Count
MsgBox Sheets(i).Range("A1")
Next
End Sub
ーー
たとえば、３と入れると、左から３，４，５番目のシートのＡ１の値が出るだろう。
ｎがSheets.Countより少ないか、というチェックのコードは省略。

お礼

imogasiさん
ありがとうございます。なるほど、こんな簡単な方法があるのですね。それがしいきなりユーザーフォームから作成する方法から入ったためこの方法に気づきませんでした。ただ変数の宣言が必要だと思うのですが、
Dim n as [ ]の
[ 　]の型の所は何を入れたらよろしいのでしょうか？それとも別の宣言方法があるのでしょうか？

補足

#2さんへ
同じように最初に質問したユーザーフォームを使用する方法で
SNo As IntegerをSNo As Stringに変更しました。
また、SNo = .ComboBox1.valueをSNo = .ComboBox1.Textに変更してマクロを実行すればうまくいきました。

回答No.1

kkkkkm

フォームでシートが何番目が指定しているのに
For i = 1 + SNo To Worksheets.Count
だと、指定したシートの次のシートから始めてます
For i = SNo To Worksheets.Count
でいいのではないですか。

補足

kkkkmさんへ。閲覧ありがとうございます。自動的に作られるまとめのシート分も含むため
For i = 1 + SNo To Worksheets.Count
としています。