VBA Object Required

[jondavis1987]

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

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

 

[Domenic]

The keyword Set is only used to assign an object to a variable. Therefore, since you are simply assigning a string to your variable, you'll need to remove it...

fName = Workbooks("Superpaves").Sheets("A").Range("B7").Value

Hope this helps!