copy without using clipboard?

g48dd · #1 07-16-2011, 08:14 AM

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?

Code:

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 · #2 07-16-2011, 07:30 PM

Hi g48dd,

Try something along the lines of:

Code:

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 · #3 07-16-2011, 10:22 PM

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

Ken

macropod · #4 07-16-2011, 10:28 PM

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.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Getting a copy of all emails sent	tonywatsonmail	Outlook	2	06-13-2011 08:21 AM
Copy from one ppt to another	uhlersa	PowerPoint	1	04-23-2011 05:05 PM
Copy to Word	markg2	Excel	2	05-01-2010 08:04 AM
Office Clipboard and Outlook	runiuksp	Outlook	0	04-29-2010 07:31 AM
copy a file which does not have copy option	jkind	Word	0	01-17-2010 09:25 AM