0

I want to create a simple macro that will copy a range of cells with information generated by formulas on sheet 2, to a range of cells on sheet one. I need to paste only VALUES on page 1, leaving the formulas on page 2 in place.

I tried the following:

Sheets("data").Range("A3:A16").Copy 
Sheets("combat").ActiveSheet.Range("A3:A16").PasteSpecial xlPasteValues
Sheets("data").Range("C3:C16").Copy 
Sheets("combat").ActiveSheet.Range("C3:C16").PasteSpecial xlPasteValues
Sheets("data").Range("D3:D16").Copy 
Sheets("combat").ActiveSheet.Range("D3:D16").PasteSpecial xlPasteValues
Sheets("data").Range("E3:E16").Copy 
Sheets("combat").ActiveSheet.Range("P3:P16").PasteSpecial xlPasteValues
Sheets("data").Range("F3:F16").Copy 
Sheets("combat").ActiveSheet.Range("R3:R16").PasteSpecial xlPasteValues
Sheets("data").Range("G3:G16").Copy 
Sheets("combat").ActiveSheet.Range("Q3:Q16").PasteSpecial xlPasteValues
Sheets("data").Range("H3:H16").Copy 
Sheets("combat").ActiveSheet.Range("S3:S16").PasteSpecial xlPasteValues

it copies data from the cells but does not paste into the other sheet.

If anyone know where I am going wrong, please let me know!

2
  • You are better off asking VBA questions at Stack OverFlow rather than Super USer
    – brettdj
    Feb 10, 2013 at 2:10
  • Your code should work if you remove .ActiveSheet from each paste line.
    – Excellll
    Feb 10, 2013 at 3:17

1 Answer 1

1

If you're going the VBA route, you may as well avoid copy/paste because it is not very efficient.

Sub CopyToCombat()

Dim shtData As Worksheet, shtCombat As Worksheet
Set shtData = Sheets("data")
Set shtCombat = Sheets("combat")

shtCombat.Range("A3:A16") = shtData.Range("A3:A16").Value
shtCombat.Range("C3:D16") = shtData.Range("C3:D16").Value
shtCombat.Range("P3:P16") = shtData.Range("E3:E16").Value
shtCombat.Range("R3:R16") = shtData.Range("F3:F16").Value
shtCombat.Range("Q3:Q16") = shtData.Range("G3:G16").Value
shtCombat.Range("S3:S16") = shtData.Range("H3:H16").Value

End Sub

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .