copy without using clipboard?

[g48dd]

Excel 2003: I am using the macro recorder to create macro that copies some cells that have formulas that pull data from other cells. So when I paste I get #VALUE unless I paste special value only, which I can do except there will be 200 of these and this is what the macro looks like for just one set of data that I have to retrieve.... is there a better way to do this?

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 7/16/2011 by KBR IPS  Kenneth Logan 326676
'

'
    Sheets("Sheet1").Select
    Range("A2").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Sheet1").Select
    Range("G13").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet2").Select
    Range("C6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Sheet1").Select
    Range("G11").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet2").Select
    Range("D6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Sheet1").Select
    Range("H13").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet2").Select
    Range("E6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Sheet1").Select
    Range("H11").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet2").Select
    Range("F6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
End Sub

What I am doing is pulling data out from a web query, I don't need everything that is down loaded so I have to run a few simple formulas to create what I need and then move it to another sheet as the next day all this data will be down loaded again.

[macropod]

Hi g48dd,

Try something along the lines of:

Sub Macro3()
Dim Source As Worksheet, Target As Worksheet
Set Source = Sheets("Sheet1")
Set Target = Sheets("Sheet2")
With Target
  .Range("A1").Value = Source.Range("A2").Value
  .Range("C6").Value = Source.Range("G13").Value
  .Range("D6").Value = Source.Range("G11").Value
  .Range("E6").Value = Source.Range("H13").Value
  .Range("F6").Value = Source.Range("H11").Value
End With
End Sub

[g48dd]

Thanks, works great and now I can see how to write that. That is so much better than what the recorder creates.

Ken

[macropod]

Hi ken,

The macro recorder's abilities are fairly basic, but it does have it's uses. There's a great deal more that can be done - and far more efficiently - once you get into writing your own code.