こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

VBA 任意のシートからコピーを始める。

教えてください。
全てのシートをコピーして一つのシートにまとめるプログラムシートを作成しました。
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

投稿日時 - 2016-04-20 18:03:18

QNo.9161373

困ってます

質問者が選んだベストアンサー

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

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

投稿日時 - 2016-04-22 14:56:06

ANo.6

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(7)

ANo.7

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

投稿日時 - 2016-04-22 15:34:59

お礼

#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
でプルダウンで半角数字が入るようになっているので全角数字に対応する必要はないかと思いますが、数値かどうかを判定する方法も知っておいたほうが役に立ちますね。
ありがとうございました。これから質問するときはポイントを押さえた質問をします。

投稿日時 - 2016-04-22 16:12:11

ANo.5

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

投稿日時 - 2016-04-21 16:28:01

ANo.4

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

投稿日時 - 2016-04-21 15:59:18

補足

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には何を使うべきかをお教えいただけるとありがたいです。

投稿日時 - 2016-04-22 13:54:24

ANo.3

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

投稿日時 - 2016-04-21 09:09:55

補足

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

投稿日時 - 2016-04-21 10:35:33

ANo.2

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

投稿日時 - 2016-04-20 23:11:39

補足

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

投稿日時 - 2016-04-21 10:50:40

お礼

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

投稿日時 - 2016-04-21 08:32:41

ANo.1

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

投稿日時 - 2016-04-20 19:51:48

補足

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

投稿日時 - 2016-04-21 07:44:39

あなたにオススメの質問