マクロ内のワークシートの名前をInput関数で指定する方法を教えてください。

またまた投稿しました。
下のように、InputBox関数を用い、マクロ内部のワークシートの名前を指定しようとマクロを書いているのですが、うまくいきません。。どなたか助けてください。よろしくお願いします。


'Work Sheet Selection
InputSheetName:

Dim myStr As String
On Error GoTo ErrHandles

myStr = InputBox(Prompt:="Please" & vbCrLf & "Input Sheet No. ! ", _
Default:=ActiveSheet.Name)　

'---(1)Cancel or Blank
If Len(myStr) = 0 Then
MsgBox "Cancel Macro Execution", vbInformation

'---(2)Other Value
ElseIf myStr <> ActiveSheet.Name Then
MsgBox "Sheet" & myStr & "Macro Start", vbInformation
myStr = Worksheets.Name　←　コンパイルエラーが発生　！！

End If

Exit Sub

ErrHandle:
'---(3)Error
MsgBox Err.Description & String(2, vbCrLf) & _
"Input Sheet No. again!", vbCritical

'---Re Input sheet No.
Resume InputSheetName

'Data Extracting
Dim x As Long
For x = 0 To 250
Worksheets("FQ.dat").Select
Range(A5).Select '
Selection.Copy
Worksheets("mystr").Select '　　←　ココのシートを選択したい！
Range(Cells(x + 31, 3), Cells(x + 31, 6)).Select
ActiveSheet.Paste
Next x

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

• 

  VBAに関する質問が2回目ということで、また。。。という表現を使いました。
  言葉足らずですね。
  私がやりたい事は、　InputBoxに入力された数値を　元データ（今回の場合「FQ.dat」）にある数値から検索し　自動的に他のシートへコピペしようするマクロを考えています。VLOOKUP関数みたいな
  感じです。
  「Data Extracting 」以降の構文がどういうわけか抜けていますね。
  正しくは、
  Worksheets("FQ.dat").Select
  Range(Cells(29 * x + (6), 1), Cells(29 * x + (6), 4)).Select '
  です。
  IｎBoxを使用してシートを指定すること（シート名は元データ内にあるセルの値と同じ）でマクロが実行されると考えているのですが。。。。
  
  どのように処理したら良いかわかりますか。。。？

  No.3の回答に寄せられた補足コメントです。 補足日時：2017/06/02 13:45

• 

  ①，②　現在、探したい数値は 、A列　6行から規則的に29行おき6937行まで存在します。
  A B C 　　 D
  6　　10.87　　109.5　 50.9　 -3
  7　　10.38　　 88.8　 35.8 -7
  　 .. 　 .. 　 .. 　 .
  35　　10.87　　208.3 352.1　 2
  36　　10.38　　 156.8　 5.8 -10
  このようなデータが29行おきに　元データに配置されています。
  これをA行は、「シート名」に使用し、B行からD行を各振り分けられたシートへ
  コピペしたいです。
  ③　仰るとおり、シート名は数値です。
  よろしくお願いします。

  No.4の回答に寄せられた補足コメントです。 補足日時：2017/06/02 16:06

• 

  選択されたシートへの貼り付け先の行は固定です。　
  貼り付けは1行のみでは、1シートに250行、増えると800行になります。
  よろしくお願いします。

  No.5の回答に寄せられた補足コメントです。 補足日時：2017/06/02 16:54

• 

  貼り付け先がどの行というか、
  一つのシートに複数回データを下へ貼り付けて行き
  元データのCellの値がBLANKになった時点で、
  元データの次の行へ移り、別のシートへ貼り付ける
  という作業です。

  No.6の回答に寄せられた補足コメントです。 補足日時：2017/06/02 17:27

No.12 ベストアンサー 回答者： GooUserラック 回答日時： 2017/06/02 22:27

No.11 のお礼について

「Cells(Rows.Count, 3).End(xlUp).Row」でＣ列（３列目）の一番最後の値が入っている行番号を得ることが出来ます。「+1」で、その次の行になります。
もちろん「3」を変える事で他の列の最後の行を調べることが出来ます。

外国住まいだったのですね。なぜメッセージが英語？の疑問が解けました。

この回答へのお礼

よく理解できました。。
本当にありがとうございました！！
助かります！！！

お礼日時：2017/06/02 22:30

No.11 回答者： GooUserラック 回答日時： 2017/06/02 22:10

No.10 の訂正です。

スペルミスを犯してしまいました。

「If DeNxt < 31 Then DeNxt = 31」に直して下さい。

申し訳ございませんでした。

この回答へのお礼

うわー！凄いですね！！
本当にありがとうございます。　
海外にいて誰も頼れる人がいないので、マクロも
見よう見まねでやっていました。　
ついでに教えてほしいのですが、
「End(xlUp).Row + 1」の「＋１」とは何を示しているのですか？
教えて頂ければ幸甚です。

お礼日時：2017/06/02 22:15

No.10 回答者： GooUserラック 回答日時： 2017/06/02 22:08

No.9 お礼について

「DeNxt = Sheets(WsName).Cells(Rows.Count, 3).End(xlUp).Row + 1」と「For SoCom = 1 To 4」の間に「If DeNxt < 31 Then denex = 31」を追加してみてください。

No.9 回答者： GooUserラック 回答日時： 2017/06/02 19:23

こんな事でしょうか？

------------------------------------------------------------------------------
Sub Macro1()

Dim Ws As Worksheet
Dim WsName As String ' 対象ワークシート名
Dim WsOK As Boolean ' ワークシート有無フラグ
Dim SoRow As Long ' 元行番号
Dim SoCom As Long ' 元列番号
Dim DeNxt As Long ' 次コピー先行番号

Do
WsName = InputBox( _
Prompt:="Please" & vbCrLf & "Input Sheet No. ! ", _
Default:=ActiveSheet.Name)
For Each Ws In Worksheets
If Ws.Name = WsName Then WsOK = True
Next Ws
If WsOK = False Then
If MsgBox( _
Prompt:="Sheet not found" & vbCrLf & "Please try again!", _
Buttons:=vbOKCancel + vbDefaultButton1) = _
vbCancel Then Exit Sub
End If
' Cancel → ExitSub
' Other → Loop
Loop Until WsOK
With Sheets("FQ.dat")
For SoRow = 6 To .Cells(Rows.Count, 1).End(xlUp).Row
If Cells(SoRow, 1).Value = WsName Then
DeNxt = Sheets(WsName).Cells(Rows.Count, 3).End(xlUp).Row + 1
For SoCom = 1 To 4
Sheets(WsName).Cells(DeNxt, SoCom + 2).Value = .Cells(SoRow, SoCom).Value
Next
End If
Next
End With

End Sub
------------------------------------------------------------------------------

この回答へのお礼

現場から戻ってきて今確認しました。　
凄いですね！！　イメージどおりです。
ありがとうございます！！
VBA初心者なのでわからないのですが、これを31行目から表示することは出来ませんか？
1行目から30行目までの間にグラフを入れるので、31行目から設定したいです。
宜しければご教示頂きたくよろしくお願い申し上げます。

お礼日時：2017/06/02 21:26

No.8 回答者： GooUserラック 回答日時： 2017/06/02 18:03

元のコードが合っているか微妙ですが

１回目に一致したらC31～F31セルに貼り付け
２回目に一致したらC32～F32セルに貼り付け
以下続くのように最初の行は３１行目で良いのですか？

No.7 回答者： yokomaya 回答日時： 2017/06/02 17:25

まず

myStr = Worksheets.Name　←　コンパイルエラーが発生　！！
これってエラーの原因が判っていないという理解でいいでしょうか？

WorksheetsはWorksheetのコレクションでNameなんて
プロパティはありませんから当然コケますね。
コレクションはとても頻度の高い概念ですから
しっかり理解しましょうね。

sがついて複数形のようなイメージで私は理解しています。
実際には名前ないしは番号を()内に付けることで
そのコレクション中の個々にアクセスします。
あとで出てくる構文にはちゃんとカッコ内にシート名を
選んで選択しているのにどうしてここは端折ったんでしょうね？

なんだこれ、重症だなぁ。
Worksheets("mystr").Select '

Dim myStr As Stringって定義しておきながら
いきなりそれとは全く無関係の"mystr"って文字列
持ってきちゃいましたねぇ。
入力したのは捨てちゃうんだ・・・。

あとマクロ記録だからSelectってよく出てくるけど
あくまでもそれは手で操作しているから、Selectが必要な
ケースは10％以下とおもっていいと思いますよ。

この回答へのお礼

ご指摘ありがとうございます。
VBA初心者なので見よう見まねやっております。
やっぱり上級者はSelectを使わないのですね。
マクロの記録を使いながらやってます。

お礼日時：2017/06/02 21:25

No.6 回答者： GooUserラック 回答日時： 2017/06/02 17:11

貼り付け先はどの行ですか？

複数回有ったら下へ貼り付けていくと言う事でしょうか？

No.5 回答者： GooUserラック 回答日時： 2017/06/02 16:19

対象シートが無かった場合の処理は？

貼り付け先の行は固定でしょうか？
貼り付けは１行のみですか？

No.4 回答者： GooUserラック 回答日時： 2017/06/02 15:01

① 探したい数値を「FQ.dat」シートのA列から探して、その行から下に〇行をまとめて指定のシートに貼り付けると言う事ですか？ちなみに〇行はいくつですか？

② 貼り付け先はアクティブシートでよければ、シート名の確認は不要だと思うのですが…
③ 探したい数値も貼り付け先も同じ「myStr」で指定しているのでしょうか？もしかしたらシート名が数値なのでしょうか？

No.3 回答者： GooUserラック 回答日時： 2017/06/02 13:27

根本的に何をやりたいのかよく判りません。

元の質問に書かれているならば、その質問のURLを提示してください。
特に「'Data Extracting」以降は「FQ.dat」シートの「A5」セルを「mystr」で指定したシートの「C31」～「F281」セルに貼り付けているだけ(同じものを貼り付けるだけ)のような感じですが…良いのでしょうか？