【初月無料キャンペーン中】gooドクター

他のブックからデータをsheet1で指定しsheet2に取込み、取込んだデータの不必要な行・列を削除したい為、以下のコードを使いますが ①でエラーが出ます。
コピーしたシートを保存・別名で保存のダイアログが出なくSheet2にはデータは取込まれていますが、フィルタはついてません。
①を除いた場合は問題ありません。

回避する方法はありますか?

-----------------------------------------
Dim var As Variant
Dim FilePath As String
Dim InSheetNo As Integer
Dim OutSheetNo As Integer
Dim OutCell As String

With ThisWorkbook.Sheets(1)
FilePath = .Range("B2")
InSheetNo = .Range("B3")
OutSheetNo = .Range("B4")
OutCell = .Range("B5")
End With

'他のEXCELファイルデータを取り込む
var = GetExcelData(FilePath, InSheetNo)

Dim MaxRow As Long
Dim MaxCol As Long

'最大行数を取得する
MaxRow = UBound(var, 1)

'最大列数を取得する
MaxCol = UBound(var, 2)

'自ワークブックの1番目のシートのセルA1を基点に取り込んだデータを出力する
ThisWorkbook.Sheets(OutSheetNo).Range(OutCell).Resize(MaxRow, MaxCol).Value = var

'取込んだ表示形式を修正する
Worksheets("Sheet2").Range("J:J,L:L,N:N").NumberFormatLocal = "0_ "

'取込んだ台帳の不必要項目を削除する
  Worksheets("Sheet2").Range("B:I,P:S,Y:AA,AD:BF,BH:BL").Delete
① Worksheets("Sheet2").Range("O2", Cells(Rows.Count, "O").End(xlUp)).SpecialCells(xlCellTypeConstants, 23).EntireRow.Delete

'フィルタを設定する
Worksheets("Sheet2").Range("A1:O1").AutoFilter

'シートをコピー
ThisWorkbook.Worksheets("sheet2").Copy

'コピーしたシートを保存・別名で保存のダイアログを開く
Dim fname As String

FileName = Application.GetSaveAsFilename _
(FileFilter:="Excelブック,*.xlsx,Excelマクロ,*.xlsm,テキスト,*.txt")

If FileName <> "False" Then

ActiveWorkbook.SaveAs FileName:=FileName

ActiveWorkbook.Close

End If

'シートを指定して値をクリアする
Worksheets("Sheet2").Range("A1:Y40000").ClearContents

'すべてのブックを保存
Dim wb As Workbook
For Each wb In Workbooks
wb.Save
Next

'台帳取込を終了する
'Application.Quit
ThisWorkbook.Worksheets("sheet1").Activate
-----------------------------------------

ご教授お願いします。

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

  • ありがとうございます。
    サンプルにやりたいことをベタで記述し作ったものでして・・・
    別BooKに保存したあと他利用する為にです。

    >O列に該当する値は存在しているのでしょうか?
    ②で列削除した後のO列なのですが・・・
    そもそも間違え?↓

    >Worksheet.Copy で当該シートだけの新規ブックが作成できます
    >https://docs.microsoft.com/ja-jp/office/vba/api/
    サンプルの↓
    .SaveAs Filename:=Environ("TEMP") & "\New1.xlsx", FileFormat:=xlOpenXMLWorkbook
    .Close SaveChanges:=False
    End With

    TEMPを共有フォルダの場合はパスを書けばよいのですか?

    無知ですみません

    No.1の回答に寄せられた補足コメントです。 補足日時:2021/03/09 16:11
  • ありがとうございます。

    ①のエラーは保存方法を教えて頂いたサイトサンプル記述前に記述し直したら
    tempディレクトリに思う通り保存出来ました。

    >申し訳ありませんが、ご質問の意味がわかりません。
    そもそもEnviron関数がわかってなかったですね。
    .SaveAs Filename:=Environ("TEMP") & "\New1.xlsx", FileFormat:=xlOpenXMLWorkbook
    TEMP(tempディレクトリ)ではなく共有フォルダを指定したいという意味でした。

    No.2の回答に寄せられた補足コメントです。 補足日時:2021/03/09 17:17
gooドクター

A 回答 (2件)

こんにちは



>①でエラーが出ます。
Range.SpecialCellsメソッドの場合、該当セルが存在しないとエラーになります。
実際の内容がわかりませんが、O列に該当する値は存在しているのでしょうか?
対処法としては、事前に、必ず含まれるようにしておくか、
 On Error ~~
などで、エラー処理をするかのどちらかの方法になるものと考えられます。


ご質問には直接関係はありませんけれど・・・
・GetExcelDataが示されていないので、変数の内容が不明ですが…
 (文章から推測すると、どうやら問題はなさそうな雰囲気ですけれど)

・OutSheetNoなどをセル値で設定するようなイメージにしようとしているようですが、コードの途中からこれらの変数を無視して、ベタで記述されているので、全体としては有効ではない仕組みになっているように見受けられます。

・ブック間のシートコピーなら、配列変数に読み込まなくても、ブックを開いてシートをコピーしてしまえばよさそうにも思いますが・・・
・また、最後にシートをクリアしているようなので、対象シートの新規ブックを作成して、そこで作業し保存しておしまいという手順のほうが後始末が不要で簡単そうな気がしました。
 Worksheet.Copy で当該シートだけの新規ブックが作成できます
https://docs.microsoft.com/ja-jp/office/vba/api/ …
この回答への補足あり
    • good
    • 1

No1です



>>O列に該当する値は存在しているのでしょうか?
>②で列削除した後のO列なのですが・・・
「②」がどの処理なのか不明ですが、別に削除するのは自由だと思います。
エラーが出るならそこでVBAは停止するでしょうから、その状態でO列に「該当するセルが無いのではないか」という意味です。
そうでない場合は、エラーの原因は別のところにある可能性もありますので。


>そもそも間違え?↓
新規ブックのことを意味しているのなら、別に間違えではありません。
「新規ブック」の話は、別法としてのアイデアの一つとしてとらえてください。


>TEMPを共有フォルダの場合はパスを書けばよいのですか?
申し訳ありませんが、ご質問の意味がわかりません。
また、ご提示のサンプルでは、Environ関数は使っていませんよね??
Environ関数は、システムの環境変数に関連付けられたパスを返すものなので、別の指定フォルダにしたいということであれば、そのフォルダのパスに変えればよいだけですが…?
この回答への補足あり
    • good
    • 1

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

gooドクター

このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング