[stuartg1978]

Hi,

I am currently trying to speed up some code that I currently use. It works and does exactly what I want it to but it takes to long so I have been trying to quicken up one of the sections that is copying information from one Workbook to another.

Quick overview of what I am trying to do:-
I have one control workbook that contains the code. I click a button and this allows you to select the input file (contains anywhere from 2000-50000) records. The code then loops through all lines in the input file and outputs them to 1 of 4 Output files depending on what the record is. These records will then go to one of 2 tabs in the relative destination file.

For c = 1 To 39
		ams.Sheets("Data").Cells(amsPctr, c) = wkb.Sheets(mySht).Cells(r, c)
Next c

Is there one or 2 lines of code that can replace the above so i can remove the loop of 39. I have used :-

wkb.Sheets("Data").Range("C:C").Copy Destination:=wkb1.Sheets("Data").Range("A:A")

and it has worked however if i try and amend this to :-

wkb.Sheets(mySht).Range("A2:AM2").Copy Destination:=wkb1.Sheets("Peregrine Data").Range("A2:AM2")

I get an Object Required error.

Any help would be greatly appreciated or if you need to see all the code please let me know

Thanks

Stuart

[thava]

i think the space will raise the error

may be, call like this

wkb.Sheets(mySht).Range("A2:AM2").Copy Destination:=wkb1.Sheets("[Peregrine Data]").Range("A2:AM2")

i am not sure about this

[stuartg1978]

thava, on 12 May, 2009 - 05:33 PM, said:

i think the space will raise the error

may be, call like this

wkb.Sheets(mySht).Range("A2:AM2").Copy Destination:=wkb1.Sheets("[Peregrine Data]").Range("A2:AM2")

i am not sure about this

I have meanaged to get it to work with the following line of code

host.Sheets("Peregrine Data").Range("A" & hostPctr & ":AM" & hostPctr) = wkb.Sheets(mySht).Range("A" & r & ":AM" & r).Value

this reduced the time by 50%.

Thanks for your help