VBScript - How to copy one excel sheet to another
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set Excel1= objExcel.Workbooks.Open("C:\1.xlsx")
Set Excel2= objExcel.Workbooks.Open("C:\2.xlsx")
Excel1.Worksheets("Sheet1").UsedRange.Copy
Excel2.Worksheets("Sheet1").Range("A1").PasteSpecial -4163
Excel1.save
Excel2.save
Excel1.close
Excel2.close
objExcel.quit
set objExcel=nothing
See more on PasteSpecial & XlPasteType Enumeration here.
[Via]