jondavis1987
Active Member
- Joined
- Dec 31, 2015
- Messages
- 443
- Office Version
- 2019
- Platform
- Windows
Trying to open a workbook, copy a sheet from the original workbook into the new workbook, but not have any of the formulas paste. Then save as at the directed file path. It highlights fName in the setting and says object required
VBA Code:
Option Explicit
Sub New_Mix_Design()
Dim fName As String
Set fName = Workbooks("Superpaves").Sheets("A").Range("B7").Value
ActiveWorkbook.Sheets("A").Range("A1:H53").Copy
Workbooks.Add
ActiveWorkbook.Range("A1").PasteSpecial xlPasteValues
ActiveWorkbook.Range("A1").PasteSpecial xlPasteFormats
ActiveWorkbook.Range("A1").PasteSpecial xlPasteAllUsingSourceTheme
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\" & Environ("username") & "\Dropbox\Quality Control\Old Worksheets\ " & fName, _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Workbooks(wbName).Close SaveChanges:=True
End Sub