Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-16-2011, 08:14 AM
g48dd g48dd is offline copy without using clipboard? Windows XP copy without using clipboard? Office 2003
Novice
copy without using clipboard?
 
Join Date: Jan 2011
Location: Iraq
Posts: 12
g48dd is on a distinguished road
Default copy without using clipboard?

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.
Reply With Quote
  #2  
Old 07-16-2011, 07:30 PM
macropod's Avatar
macropod macropod is online now copy without using clipboard? Windows 7 64bit copy without using clipboard? Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 07-16-2011, 10:22 PM
g48dd g48dd is offline copy without using clipboard? Windows XP copy without using clipboard? Office 2003
Novice
copy without using clipboard?
 
Join Date: Jan 2011
Location: Iraq
Posts: 12
g48dd is on a distinguished road
Default

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

Ken
Reply With Quote
  #4  
Old 07-16-2011, 10:28 PM
macropod's Avatar
macropod macropod is online now copy without using clipboard? Windows 7 64bit copy without using clipboard? Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
copy without using clipboard? 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 without using clipboard? 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

Other Forums: Access Forums

All times are GMT -7. The time now is 06:41 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft