Macro to copy Excel values between worksheets

C

celia

hi,
I have use the Excel Tip Copy a column or columns from each sheet into
one sheet using VBA in Microsoft Excel.

I try to use the copy values A5:A20 and IQ5:IT20 from sheet 1 to Sheet
2. However, when i run the macro, it only copy the value in A5:A20 to
Sheet2. There are formulas (SUM) used in IQ5:IT20.

How can I modify the macro to copy the two ranges of value into
another sheet?

Will the format of the cells influence the results?


Please help me asap.

Thankyou very much!


+----------------------------------------------------------------+
| Attachment filename: b1.zip |
|Download attachment: http://www.excelforum.com/attachment.php?postid=353270|
+----------------------------------------------------------------+
 
T

tippy

Are you sure you need a macro? It's fairly easy when your worksheets
are all in the same workbook. If you don't need a macro, why not
"point" in the cell of the desired worksheet to the desired cell in
the "giving" worksheet. Then copy to lower cells in the column.

Tippy--
who still can't get his macro to unprotect his multiple worksheets to
work.

hi,
I have use the Excel Tip Copy a column or columns from each sheet into
one sheet using VBA in Microsoft Excel.

I try to use the copy values A5:A20 and IQ5:IT20 from sheet 1 to Sheet
2. However, when i run the macro, it only copy the value in A5:A20 to
Sheet2. There are formulas (SUM) used in IQ5:IT20.

How can I modify the macro to copy the two ranges of value into
another sheet?

Will the format of the cells influence the results?


Please help me asap.

Thankyou very much!


+----------------------------------------------------------------+
| Attachment filename: b1.zip |
|Download attachment: http://www.excelforum.com/attachment.php?postid=353270|
+----------------------------------------------------------------+
Click to expand...

_____________

Tippy
 
C

celia

I need a macro because i want to copy the values into another sheet /
workbook and create pivot table from that. And actually it will given
to many users to filled up the job no and time spent on each job no.

Initially, I used Vlookup for the job no and returned the value for
Normal, OT1-OT4. But, Vlookup cannot returned multiple values that is
when the same job no is entered more than once. So, I need to create
pivot table to get the sum of time if one job no has been entered more
than once. That is why i need to copy the values in another sheet. And
I cannot just create pivot table out of the data in the sheet i
attach.

Can you help me to create a macro for that??

Thanks!
 
D

Don Guillett

try this to copy values only
sheet2!a5:a20=sheet1!a5:a20
sheet2!iq5:it20=sheet1!aiq5:it20
 
T

Tom Ogilvy

It think Don meant
[sheet2!a5:a20]=[sheet1!a5:a20]
[sheet2!iq5:it20]=[sheet1!iq5:it20]

or the faster

With Worksheets("Sheet2")
.Range("A5:A20").Value = _
worksheets("Sheet1").Range("A5:A20)").Value
.Range("IQ5:IT20").Value = _
worksheets("Sheet1").Range("IQ5:IT20")
End With

--
Regards,
Tom Ogilvy


Don Guillett said:
try this to copy values only
sheet2!a5:a20=sheet1!a5:a20
sheet2!iq5:it20=sheet1!aiq5:it20
Click to expand...
 
D

Don Guillett

Tom,
You are correct, a slip of the finger.
But, why is the with, in THIS case, faster.
And, just how fast do we need to be with 2 items?

--
Don Guillett
SalesAid Software
(e-mail address removed)
Tom Ogilvy said:
It think Don meant
[sheet2!a5:a20]=[sheet1!a5:a20]
[sheet2!iq5:it20]=[sheet1!iq5:it20]

or the faster

With Worksheets("Sheet2")
.Range("A5:A20").Value = _
worksheets("Sheet1").Range("A5:A20)").Value
.Range("IQ5:IT20").Value = _
worksheets("Sheet1").Range("IQ5:IT20")
End With
Click to expand...
 
T

Tom Ogilvy

Not the with, the use of standard range references, rather than having to go
back to the excel application and have it interpret the notation.

You all worry about -- vice multiplication, I suspect the penalty here is
much more severe.

As an example,
Dana posts clever solutions to avoid loops - improve speed, but uses such
notation. Kind of inconsistent.

In my tests, it is 2 - 3 times slower than standard range references.

So why promulgate bad practice?

--
regards,
Tom Ogilvy


Don Guillett said:
Tom,
You are correct, a slip of the finger.
But, why is the with, in THIS case, faster.
And, just how fast do we need to be with 2 items?

--
Don Guillett
SalesAid Software
(e-mail address removed)
Tom Ogilvy said:
It think Don meant
[sheet2!a5:a20]=[sheet1!a5:a20]
[sheet2!iq5:it20]=[sheet1!iq5:it20]

or the faster

With Worksheets("Sheet2")
.Range("A5:A20").Value = _
worksheets("Sheet1").Range("A5:A20)").Value
.Range("IQ5:IT20").Value = _
worksheets("Sheet1").Range("IQ5:IT20")
End With

--
Regards,
Tom Ogilvy


Don Guillett said:
try this to copy values only
sheet2!a5:a20=sheet1!a5:a20
sheet2!iq5:it20=sheet1!aiq5:it20

--
Don Guillett
SalesAid Software
(e-mail address removed)

hi,
I have use the Excel Tip Copy a column or columns from each sheet into
one sheet using VBA in Microsoft Excel.

I try to use the copy values A5:A20 and IQ5:IT20 from sheet 1 to Sheet
2. However, when i run the macro, it only copy the value in A5:A20 to
Sheet2. There are formulas (SUM) used in IQ5:IT20.

How can I modify the macro to copy the two ranges of value into
another sheet?

Will the format of the cells influence the results?


Please help me asap.

Thankyou very much!


+----------------------------------------------------------------+
| Attachment filename: b1.zip |
|Download attachment:
http://www.excelforum.com/attachment.php?postid=353270|
+----------------------------------------------------------------+
Click to expand...
Click to expand...
Click to expand...
 
D

Don Guillett

Didn't know it was slower by using the [ ] over range( ).

--
Don Guillett
SalesAid Software
(e-mail address removed)
Tom Ogilvy said:
Not the with, the use of standard range references, rather than having to go
back to the excel application and have it interpret the notation.

You all worry about -- vice multiplication, I suspect the penalty here is
much more severe.

As an example,
Dana posts clever solutions to avoid loops - improve speed, but uses such
notation. Kind of inconsistent.

In my tests, it is 2 - 3 times slower than standard range references.

So why promulgate bad practice?

--
regards,
Tom Ogilvy


Don Guillett said:
Tom,
You are correct, a slip of the finger.
But, why is the with, in THIS case, faster.
And, just how fast do we need to be with 2 items?

--
Don Guillett
SalesAid Software
(e-mail address removed)
Tom Ogilvy said:
It think Don meant
[sheet2!a5:a20]=[sheet1!a5:a20]
[sheet2!iq5:it20]=[sheet1!iq5:it20]

or the faster

With Worksheets("Sheet2")
.Range("A5:A20").Value = _
worksheets("Sheet1").Range("A5:A20)").Value
.Range("IQ5:IT20").Value = _
worksheets("Sheet1").Range("IQ5:IT20")
End With

--
Regards,
Tom Ogilvy


try this to copy values only
sheet2!a5:a20=sheet1!a5:a20
sheet2!iq5:it20=sheet1!aiq5:it20

--
Don Guillett
SalesAid Software
(e-mail address removed)

hi,
I have use the Excel Tip Copy a column or columns from each sheet into
one sheet using VBA in Microsoft Excel.

I try to use the copy values A5:A20 and IQ5:IT20 from sheet 1 to Sheet
2. However, when i run the macro, it only copy the value in A5:A20 to
Sheet2. There are formulas (SUM) used in IQ5:IT20.

How can I modify the macro to copy the two ranges of value into
another sheet?

Will the format of the cells influence the results?


Please help me asap.

Thankyou very much!


+----------------------------------------------------------------+
| Attachment filename: b1.zip |
|Download attachment:
http://www.excelforum.com/attachment.php?postid=353270|
+----------------------------------------------------------------+
Click to expand...
Click to expand...
Click to expand...
 
C

celia

Sorry,

How should I put the code to make it run (Copy/paste)?

[sheet2!a5:a20]=[sheet1!a5:a20]
[sheet2!iq5:it20]=[sheet1!iq5:it20]

or

With Worksheets("Sheet2")
.Range("A5:A20").Value = _
worksheets("Sheet1").Range("A5:A20)").Value
.Range("IQ5:IT20").Value = _
worksheets("Sheet1").Range("IQ5:IT20")
End With
 
D

Dave Peterson

Assigning the values is pretty much the same as copy|Paste special|Values.

So you have your choice between them.

(I'd bet working directly with the values would be quicker, if you're concerned
with that.)
Sorry,

How should I put the code to make it run (Copy/paste)?

[sheet2!a5:a20]=[sheet1!a5:a20]
[sheet2!iq5:it20]=[sheet1!iq5:it20]

or

With Worksheets("Sheet2")
Range("A5:A20").Value = _
worksheets("Sheet1").Range("A5:A20)").Value
Range("IQ5:IT20").Value = _
worksheets("Sheet1").Range("IQ5:IT20")
End With
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help:Macro to copy Excel values between worksheets 7
Need help with macro to copy, paste, and clear cell values 8
problems getting this macro to work 23
Creating a Macro Button to Extract Data 1
How to separate data.... 1
Macro is not working(XL2K) 2
[newbie] Macro for "copy and paste" 1
Macro to copy value in field and insert it into filter 2

Top