Hello,

I am a novice to Excel macro programming. I need to Copy one entire sheet and paste values and formats only in place and Save as a different excel file. The sheet has formulas referring to other sheets in the same work book.

I searched this forum and tried the following code...but getting error "PasteSpecial method of Range class Failed"

Dim objXLApp, objXLWb, objXLWs

Set objXLApp = CreateObject("Excel.Application")

objXLApp.Visible = True

Set objXLWb = objXLApp.Workbooks.Open("path\Excel file name")
Set objXLWs = objXLWb.Sheets("Summary")

objXLWb.Sheets("sheet name").Activate

objXLWb.Sheets("sheet name").Select
objXLWb.ActiveSheet.Copy
With objXLWb.ActiveSheet.UsedRange
.Copy
.PasteSpecial xlValues
.PasteSpecial xlFormats
End With
Application.CutCopyMode = False


Please advise, Thanks a lot in advance.