Subscipt out of Range

  • Thread starter Thread starter Jahson
  • Start date Start date
J

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
 
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
 
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
 
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
 
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
 
Back
Top