Copying data from 1 workbook to another

[jobur]

Hi

I am looking at the possiblity of copying data from 1 workbook to another without activating the 2nd workbook;.i.e the one I want to copy to

My code at the moment is
$-------------
Sub CopyData()

NbOfTimeStep = 3
NbOfNodes = 3

GroupName="Data" 'This is a variable in reality

Set ActiveReportFile = Workbooks("Book2").Sheets(1)
Set TemplateSheet = Workbooks("template_v1.xls")_
.Worksheets("Template")


TemplateSheet.Copy Before:=TemplateSheet

'Rename sheet just copied
Workbooks("template_v1.xls") _
.Sheets("Template (2)").Name = GroupName

Row = 3
Column = 2

'Error is here
ActiveReportFile.Range(Cells(3,2),Cells(Row + NbOfNodes, Column + NbOfTimeStep)).Copy _
Destination:=Workbooks("template_v1.xls") _
.Worksheets(GroupName) _
.Range(Cells(18, 4), Cells(18 + NbOfNodes, 4 + NbOfTimeStep))

End Sub
$-----------
The programm stops when it tries to copy the data to the destination with "application-defined or object-defined error"

Any suggestions?

Thanks

Regards

JB

 

[PHV]

And this ?
ActiveReportFile.Range(Cells(3,2),Cells(Row + NbOfNodes, Column + NbOfTimeStep)).Copy _
Destination:=Workbooks("template_v1.xls") _
.Worksheets(GroupName).Range("D18")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rmikesmith]

JB,

The problem is the unqualified Cells references in the Destination clause. These default to the active sheet, which in this case is in the source workbook. Try something like the following:

Dim Wks as Worksheet
Set Wks = Workbooks("template_v1.xls").Worksheets(GroupName) 
ActiveReportFile.Range(Cells(3,2),Cells(Row + NbOfNodes, Column + NbOfTimeStep)).Copy _
Destination:=[b]Wks[/b].Range([b]Wks[/b].Cells(18, 4), [b]Wks[/b].Cells(18 + NbOfNodes, 4 + NbOfTimeStep))

HTH
Mike