Copy and Paste a whole Row

Copy and Paste from one workbook to another workbook

Page 1 of 1

2 Replies - 1461 Views - Last Post: 13 May 2009 - 12:22 AM Rate Topic: -----

#1 stuartg1978  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 09-March 09

Copy and Paste a whole Row

Posted 11 May 2009 - 05:35 AM

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

Is This A Good Question/Topic? 0
  • +

Replies To: Copy and Paste a whole Row

#2 thava  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,606
  • Joined: 17-April 07

Re: Copy and Paste a whole Row

Posted 12 May 2009 - 06:33 PM

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
Was This Post Helpful? 0
  • +
  • -

#3 stuartg1978  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 09-March 09

Re: Copy and Paste a whole Row

Posted 13 May 2009 - 12:22 AM

View Postthava, 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
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1