Excel VBA のエラー 5 でトラブルです

質問

'下記のVBAで---------で囲った部分でエラー 5 「プロシージャの呼び出し、または引数が
'不正です。」となります。色々試してみたのですが、何をやってもうまくいきません。
'ご教授頂けないでしょうか
'何卒、宜しくお願い致します。

Sub 月次工数_M()

' 月次工数_M Macro

MsgBox "挿入する「ファイル」を選択してください"

Dim strCdir As String
Dim strFname As String

With CreateObject("WScript.Shell")
'カレントディレクトリ控え
strCdir = CurDir
'カレントネットワークパスの設定
.currentdirectory = "\\fileserver\91_Users\EXCEL"
'ファイルを開くダイアログ表示
strFname = Application.GetOpenFilename("挿入ファイル,*.CSV")
If strFname <> "False" Then
' MsgBox "選択したファイル名は" & strFname & "です。"
Else
MsgBox "キャンセルされました。"
Exit Sub
End If

Dim newbk As String

'新規にブックを追加
Workbooks.Add
'追加したブックの名前を取得
newbk = ActiveWorkbook.Name

Workbooks(newbk).Activate

'カレントディレクトリ復元
.currentdirectory = strCdir

End With

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & strFname, _
Destination:=Range("$A$1"))

' .CommandType = 0
.Name = "TEXT;" & strFname
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 65001
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

Dim wk As Variant 'シートの名前を付ける
wk = Application.InputBox("新しいシート名を入力してください", Type:=2)
If wk <> False Then
ActiveSheet.Name = wk
Else
MsgBox "キャンセルされました。"
Exit Sub
End If

Range("A1").Select

Sheets.Add

MsgBox "選択したシート名は" & wk & "です。"
'----------------------------ここでエラーが発生します------------------------------------------------------
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
wk & "!R1C1:R95C18", Version:=xlPivotTableVersion15).CreatePivotTable _
TableDestination:="Sheet1!R3C1", TableName:="ピボットテーブル2", DefaultVersion _
:=xlPivotTableVersion15
'---------------------------------------------------------------------------------------------------------------------

Sheets("Sheet1").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("UserName")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("ピボットテーブル2").AddDataField ActiveSheet.PivotTables( _
"ピボットテーブル2").PivotFields("作業時間"), "合計 / 作業時間", xlSum
ActiveSheet.PivotTables("ピボットテーブル2").CompactLayoutRowHeader = "UserName"
Rows("1:2").Select
Selection.Delete Shift:=xlUp
Range("C16").Select
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "名別"

回答No.1

ushi2015

こんにちは
Excelのバージョンは？
ActiveSheet.Name = wk　
はWorkbooks.Addで出来たブックのActiveSheetです。
なのでそのブックにはSheet1というシートは無くなります。
その後、Sheets.Addしているので、ブック作成時に1シートならSheet2
3シートならSheet4のようになります。
なので、
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
wk & "!R1C1:R95C18", Version:=xlPivotTableVersion15).CreatePivotTable _
TableDestination:="Sheet1!R3C1", TableName:="ピボットテーブル2", DefaultVersion _
:=xlPivotTableVersion15
の中の"Sheet1!R3C1"は存在しない事になります。
ActiveSheet.Name & "!R3C1"
のようにしないとダメです。
その後の、Sheets("Sheet1").Selectも当然エラーになります。

If wk <> False Then
ActiveSheet.Name = wk
Else
MsgBox "キャンセルされました。"
Exit Sub
End If
Range("A1").Select
Sheets.Add
を、
If wk <> False Then
Sheets.Add
ActiveSheet.Name = wk
Else
MsgBox "キャンセルされました。"
Exit Sub
End If
のようにすれば、その後のコードもエラー無く進むので、本来は
それが希望の処理？

お礼

お返事が遅れて申し訳ございませんでした。ご返答有難うございました。参考にさせて頂きましたが、私も質問の仕方が悪かったせいかご助言の内容では克服出来ませんでした。
結果、質問の上記で記載したWorkBookにCSVデータを入れると次の新しいシートがSheet2となる事を利用して以下のコードで対応しました。
ushi2015さんのご回答の意図がつかめず、すみませんでした。
めげずに今後も勉強していきます。今後とも質問させて頂きますが、その節には何卒、宜しくお願い致します。

Dim NewSt As Variant 'シートの名前を付ける
NewSt = Application.InputBox("元データ用シート名を入力してください", Type:=2)
If NewSt <> False Then
ActiveSheet.Name = NewSt
Else
MsgBox "キャンセルされました。"
Exit Sub
End If

' 名別ピポットテーブル作成

Sheets.Add Before:=ActiveSheet
Sheets("Sheet2").Name = "名別"
Sheets(NewSt).Select
Range("A1").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
NewSt & "!R1C1:R95C18", Version:=xlPivotTableVersion15).CreatePivotTable _
TableDestination:="名別!R1C1", TableName:="ピボットテーブル1", DefaultVersion:= _
xlPivotTableVersion15
Sheets("名別").Select
Cells(1, 1).Select
With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("UserName")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("ピボットテーブル1").AddDataField ActiveSheet.PivotTables( _
"ピボットテーブル1").PivotFields("作業時間"), "合計 / 作業時間", xlSum
Range("A1").Select
ActiveSheet.PivotTables("ピボットテーブル1").CompactLayoutRowHeader = "UserName"
ActiveSheet.PivotTables("ピボットテーブル1").RowAxisLayout xlTabularRow
ActiveWorkbook.ShowPivotTableFieldList = FalseF