Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / May 2008

Tip: Looking for answers? Try searching our database.

efficient code to copy/paste

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Ivano - 14 May 2008 14:20 GMT
Hi,
I need to copy and paste various cells within various tabs for various
worksheets to other worksheets. So, through the Record Macro feature it came
up with a bunch of code that records every click and step but I want to make
it more efficient. This is part of the code:

Windows("Workbook1.xls").Activate
   Sheets("Benefit Analysis - Salary").Select
   Range("A6:F19").Select
   Selection.Copy
   Windows("Workbook2.xls").Activate
   Sheets("Benefit Analysis - Salary").Select
   Range("A6").Select
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
       :=False, Transpose:=False

Is there way in a single comand line I can tell it to:
copy range "A6:F19" from "Benefit Analysis - Salary" from "workbook1"
paste values to range "A6" of "Benefit Analysis - Salary" from "workbook2"

Thanks
Ivano
Don Guillett - 14 May 2008 14:29 GMT
This assumes your macro fired from the destination workbook.

Sub copyvaluesfrom()
Workbooks("sourc.xls").Sheets("sheet1").Range("a2:a22").Copy
Range("a6").PasteSpecial Paste:=xlPasteValues
End Sub

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> Hi,
> I need to copy and paste various cells within various tabs for various
[quoted text clipped - 21 lines]
> Thanks
> Ivano
Ivano - 14 May 2008 15:23 GMT
Thanks Don, it works.

> This assumes your macro fired from the destination workbook.
>
[quoted text clipped - 28 lines]
> > Thanks
> > Ivano
Norman Jones - 14 May 2008 14:38 GMT
Hi Ivano,

Try:

   Workbooks("Workbook.xls").Sheets _
       ("Benefit Analysis - Salary"). _
               Range("A6:F19").Copy _
                   Destination:=Workbooks("Workbook2").Sheets _
                              ("Benefit Analysis - Salary").Range("A6")

---
Regards.
Norman

> Hi,
> I need to copy and paste various cells within various tabs for various
[quoted text clipped - 21 lines]
> Thanks
> Ivano
Norman Jones - 14 May 2008 14:47 GMT
Hi Ivano,

I missed the  .xls extension from the
destination file.

The suggestion should, therefore, read:

   Workbooks("Workbook.xls").Sheets _
       ("Benefit Analysis - Salary"). _
           Range("A6:F19").Copy _
               Destination:=Workbooks("Workbook2.xls").Sheets _
                              ("Benefit Analysis - Salary").Range("A6")

---
Regards.
Norman
Norman Jones - 14 May 2008 15:23 GMT
Hi Ivano,

You specifically asked for a one line
instruction and I responded accordingly.

However, without such condition, I
would have suggested:

'=========>>
Public Sub Tester()
   Dim srcWb As Workbook
   Dim destWb As Workbook
   Dim srcSH As Worksheet
   Dim destSH As Worksheet
   Dim srcRng As Range
   Dim destRng As Range

   Set srcWb = Workbooks("Workbook1.xls")
   Set destWb = Workbooks("Workbook2.xls")
   Set srcSH = srcWb.Sheets("Benefit Analysis - Salary")
   Set destSH = destWb.Sheets("Benefit Analysis - Salary")
   Set srcRng = srcSH.Range("A6:F19")
   Set destRng = destSH.Range("A6:F19")

   'Your preceding code
       srcRng.Copy Destination:=destRng
   'Your subsequent code

End Sub
'=========>>

This may seem an unnecessarily lengthy
approach, but, in my experience, it
produces more efficient, more legible code
which has the additional advantage of being
easier to revise, reuse and maintain.

As a simple example, the assignment of a
range to an object variable enables that
variable to be  used in  subsequent code in
place of the full range address. This means
that any change in the range only needs to be
effected once, in the assignment statement,
rather than at each point of use.

In any event, in my opinion, legibility, clarity
and efficiency are more important objectives
than simple concision

---
Regards.
Norman

> Hi Ivano,
>
[quoted text clipped - 11 lines]
> Regards.
> Norman
Ivano - 14 May 2008 16:45 GMT
HI Norm,
you are right, your one liner does conform to my requirement.  
The structure of your second suggested code does make things more efficient
in the end for me since I have many spread sheets to do this with.  However,
not being very experienced with VBA I will need to take your code, go over it
and tweek it to fit my situation.  
Thanks very much for coming back with the suggestion... it will make my life
easier in the end.

Ivano

> Hi Ivano,
>
[quoted text clipped - 64 lines]
> > Regards.
> > Norman
Ivano - 14 May 2008 17:21 GMT
I ran into a snag.  A1 to D1 are merged and A2 to D2 are also merged

from workbook1 - ".Range("A1:D2").copy" and
to workbook2 - ".Range("A5:D6").PasteSpecial Paste:=xlPasteValues

I get runtime error: PasteSpecial method of Range class failed

But if I change the destination of workbook2 to paste to A1:D2 then it
works... it's like I can't past special to any other destination other then
where it got copied from?

any ideas?

> Hi,
> I need to copy and paste various cells within various tabs for various
[quoted text clipped - 19 lines]
> Thanks
> Ivano
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.