vbaでシートをコピーし、名前を付けて保存するコードを書くためには、excelの微妙な癖を踏まえる必要がある。それが流儀だ。

vbaでシートをコピーし、名前を付けて保存するコードは、excelのクセを踏まえた上で、コードを書いていく。

Sub CopyandSaveFile()
Dim file1 as String
    'menuシートのBxxセルにあるファイル名をゲットしておく。
    file1 = Worksheets("menu").Range("Bxx").Value
    'まず、新しいブックを作成する。即ち、新しいファイルをオープンしておく。
    Workbooks.add
    'シートをコピーする。
    Worksheet("table").Range("A1:G4").Copy
    'シートを貼り付ける。
    ActiveSheet.Range("A1:G4").PasteSpecialAll
    '式を値にする。
    ActiveSheet.Range("A2:G4").Value = ActiveSheet.Range("A2:G4").Value　 
    '列幅を整える。
    ActiveSheet.Range("A:G").Autofit
    '名前を付けて保存する。
    ActiveWorkbook.Saveas fileNmaes:=file1
    'ファイルをクローズする。
    ActiveWorkbook.Close　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 
end Sub

　Workbooks.addしたら、ActiveSheetは、新たに作成した空のファイルのシートになる。それがExcelの癖。そして、最後にファイルをクローズしておくのもexcelの癖に合わせるためだ。