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.
Bookmarks