Thanks Again Don. I thought I would show you a larger piece of the code.
Basically I am trying to change line 11 from PasteAll to PasteValue. For some
reason, I cannot get it to work.
1 Sub Macro1()
2 Workbooks.Open Filename:="J:\Projects\top250\MarketShare July 2007.xls"
3 Dim s As String
4 s = Application.InputBox("enter tab name:", 2)
5 Sheets(s).Activate
6 Range("A1:j29").Select
7 Application.CutCopyMode = False
8 Selection.Copy
9 Windows("OCC_Top10.xls").Activate
10 Range("A1").Select
11 Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
13 Application.CutCopyMode = False
14 Dim rng As Range
15 Dim OutApp As Object
16 Dim OutMail As Object
17 With Application
18 .EnableEvents = False
19 .ScreenUpdating = False
20 End With
21 Set rng = Nothing
22 Set rng = ActiveSheet.UsedRange
23 Set OutApp = CreateObject("Outlook.Application")
24 OutApp.Session.Logon
25 Set OutMail = OutApp.CreateItem(0)
26 On Error Resume Next
27 With OutMail
28 .To = "
29
30 .CC = ""
31
32 .BCC = ""
33
34 .Subject = "OCC TOP 20 " & Sheets("Sheet1").Range("B7").Value
35
36 .HTMLBody = RangetoHTML(rng)
37
38 .Send 'or use .Display
39
40 End With
41
42 On Error GoTo 0
"Don Guillett" wrote:
Yes, somehow there are TWO periods in front of paste. Also, This is fired
from the source sheet assuming the destination sheet is already open.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"carl" wrote in message
...
Thank you Don.
I can't get it to work. I think there might be a typo ?
"Don Guillett" wrote:
Sub copyrngtoOPENworkBook()
Range("a1:j29").Copy
Workbooks("OCC_Top10.xls").Sheets("sheet1").Range( "a1") _
..PasteSpecial Paste:=xlPasteValues
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"carl" wrote in message
...
I am trying to use this macro to copy and paste a section of my
spreadsheet.
It get;s hung up at the indicated line:
Range("A1:j29").Select
Application.CutCopyMode = False
Selection.Copy
Windows("OCC_Top10.xls").Activate
Range("A1").Select
HUNG UP Here
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks
_
:=False, Transpose:=False
Application.CutCopyMode = False
What am I doing incorrectly ?
Thank you in advance.