Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
efficient code to copy/paste
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
efficient code to copy/paste
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 "Ivano" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
efficient code to copy/paste
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
efficient code to copy/paste
Thanks Don, it works.
"Don Guillett" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Ivano" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
efficient code to copy/paste
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 "Norman Jones" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
efficient code to copy/paste
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 "Norman Jones" wrote: 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 "Norman Jones" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
efficient code to copy/paste
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? "Ivano" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Efficient Code to Copy a 1-D Array to a Worksheet Column | Excel Programming | |||
Copy & Paste code more efficient | Excel Programming | |||
More efficient copy/paste?? | Excel Programming | |||
Efficient Copy/Paste | Excel Programming | |||
More efficient method to copy-paste values in place? | Excel Programming |