Help:Macro to copy Excel values between worksheets

[celia]

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 macro to copy column A5:A20 and IQ5 to IT20 from sheet
1 to sheet 2. There are formulas (SUM) used in IQ5:IT20. When I run the
macro CopyRanges(), it only copy the values from A5:A20 to Sheet 2.

How can I copy the values from A5:A20 and IQ5:IT20 to another sheet? I
would like to place the values from column A:F.

CAn anyone help me on this?


+----------------------------------------------------------------+
| Attachment filename: b1.xls |
|Download attachment: http://www.excelforum.com/attachment.php?postid=353268|
+----------------------------------------------------------------+

 

[Bob Phillips]

Celia,

Trfy using PasteSpecial

Worksheets("Sheet1").Range("IQ5:IT20").Copy
Worksheets("Sheet2").Range("IQ5").PasteSpecial Paste:=xlPasteFormulas


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[celia]

Hi,

How can the code be modified to paste to another workbook instead of
another worksheet?

Please advice!

Thanks a lot for the help!

 

[Dave Peterson]

Just qualify it with more info:

Worksheets("Sheet1").Range("IQ5:IT20").Copy
Worksheets("Sheet2").Range("IQ5").PasteSpecial Paste:=xlPasteFormulas

could become:

workbooks("book1.xls").Worksheets("Sheet1").Range("IQ5:IT20").Copy
workbooks("book2.xls").Worksheets("Sheet2").Range("IQ5").PasteSpecial

(I qualified both ranges.)

 

[celia]

Thanks, I tried the code. However, if you refer to the attachment B1.xl
in the attachment where the values I would like to copy to anothe
workbook.

Sub Copy()
Workbooks("Eng.xls").Worksheets("PE6").Range("A14:A51").Copy
Worksheets("PT_PE6").Range("A1:A39").PasteSpecial
Application.CutCopyMode = False

Workbooks("Eng.xls").Worksheets("PE6").Range("IQ14:IU51").Copy
Worksheets("PT_PE6").Range("B1:F39").PasteSpecial
Application.CutCopyMode = False

End Sub

It can copy A14:A51 and paste the values in A1:A39, but it paste #RE
in B1:F39.

Anything wrong with my code?

Thank

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

 

[gocush]

What happens if you select just the first cell in your destinatio
range? That is:

Change:
Worksheets("PT_PE6").Range("B1:F39").PasteSpecial

to:
Worksheets("PT_PE6").Range("B1").PasteSpecia

 

[celia]

Dear Paul,

If I change the code as u said, it still give out #REF! answers.
Originally, there are formulas used in the cells where i need to cop
the values.

Actually, I want to copy the range of values because I cannot creat
pivot table out of the data in the original B1.xls.

Any idea?


Thanks

 

[Dave Peterson]

IQ14:IU51 is 38 rows by 5 columns.
B1:F39 is 39 rows by 5 columns.

Shouldn't they either be the same?