Subscipt out of Range

[Jahson]

Hi

I have the following code which should copy certain ranges
from workbook 'Remittance Procedure Form Template Test'
to 'Remittance Template' but it keeps coming up with the
error Subscript out of Range.

Can anyone help?


Sub Copy_to_Template()

Set outfile = Workbooks("C:\Documents and
Settings\ThoemmE1\Desktop\Remittance Project\Remittance
Template.xls")
Workbooks("Remittance Procedure Form Template
Test").Activate
Worksheets("92212353").Activate
Range("b1").Copy Destination:=outfile.Range("b6")
Range("a1:a5").Copy Destination:=outfile.Range("a16")
Range("c1:c5").Copy Destination:=outfile.Range("b16")
Range("d1:d5").Copy destinationL = outfile.Range("c16")

'outfile.Range("b10").Value = ActiveSheet.Name

End Sub

 

[Chip Pearson]

Jahson,

Remove the path name from the workbook. E.g.,

Set outfile = Workbooks("Remittance Template.xls")

This assumes that the file is already open. If not, use

Set outfile = Workbooks.Open("C:\your path\filename.xls")


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Jahson]

Thanks for your help Chip.

It is now kicking out another error saying:
Object doesnt support this method or property:

Sub aaa()

Set outfile = Workbooks.Open("C:\Documents and
Settings\ThoemmE1\Desktop\Remittance Project\Remittance
Template.xls")
Windows("Remittance Procedure Form Template
Test.xls").Activate
Worksheets("92212353").Activate
Range("b1").Copy Destination:=outfile.Range("b6")
Range("a1:a5").Copy Destination:=outfile.Range("a16")
Range("c1:c5").Copy Destination:=outfile.Range("b16")
Range("d1:d5").Copy destinationL = outfile.Range("c16")

'outfile.Range("b10").Value = ActiveSheet.Name

End Sub

 

[Chip Pearson]

What line of code is causing the error?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Jahson]

Hi

Range("b1").Copy Destination:=outfile.Range("b6")
Range("a1:a5").Copy Destination:=outfile.Range("a16")
Range("c1:c5").Copy Destination:=outfile.Range("b16")
Range("d1:d5").Copy destinationL = outfile.Range("c16")

Cant quite work this one out

Thanks

 

[Chip Pearson]

Jahson,

You need to include the worksheet reference in the destinations.
E.g.,

Range("b1").Copy
Destination:=outfile.Worksheets("Sheet1").Range("b6")



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com