VBA Object Required

jondavis1987

Active Member
Joined
Dec 31, 2015
Messages
416
Office Version
  1. 2019
Platform
  1. 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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,696
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...

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

Hope this helps!
 
Solution

Forum statistics

Threads
1,136,302
Messages
5,674,965
Members
419,537
Latest member
ucatchy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top