Copiying entire sheet into another workbook to the designated sheet

[ADAEKIN]

Hi everyone,

I hope you can help me with this. I have below code to use for copying data on the entire sheet (Sheet1 of source the workbook) to the Sheet2 (tab name "Data") in the destination workbook. My problem is that the code copies Sheet1 from Source workbook into th destination Workbook as a new sheet/tab and not over or into the Sheet2 (Data tab) of the Destination Workbook. All the macros and formulas work based on the data available in the Sheet2 (Data tab) of the destination Workbook but it is blank since the data I am trying to copy from the Soyrce Workbook is being added as a new heet/tab and not being copied into the Sheet2 (Data tab) of the Destination Workbook. Any help to fix this is greatly appriciated!!!! Thanks a lot in advance!!!


Sub CreateLocal3()

'Copy Data into the template'

Dim wkbSource As Workbook
Dim wkbDest As Workbook
Dim shtToCopy As Worksheet

Set wkbSource = ActiveWorkbook
Set wkbDest = Workbooks.Open("C:\Documents and Settings\skazan200\Desktop\POLITICAL\Templates\TEMPs for macro\LUR Calculator 2012 ZONE - Philly temp")
Set shtToCopy = wkbSource.Sheets(1)
shtToCopy.Copy wkbDest.Sheets(2)

ActiveWorkbook.Saveas "C:\Documents and Settings\skazan200\Desktop\POLITICAL\2012 Reports\LOCAL\LUR Calculator 2012 ZONE - Philly" & ".xlsm"

ActiveWorkbook.Close

End Sub

 

[patel45]

I don't think is possible, you can use

shtToCopy.Copy After:=wkbDest.Sheets(1)

but not paste in the existing sheet2, otherwise you have to copy contents of wkbSource.Sheets(1) into wkbDest.Sheets(2)