Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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!

share|improve this question
    
You are better off asking VBA questions at Stack OverFlow rather than Super USer – brettdj Feb 10 '13 at 2:10
    
Your code should work if you remove .ActiveSheet from each paste line. – Excellll Feb 10 '13 at 3:17

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
share|improve this answer

You must log in to answer this question.

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