Macro Problem

[Guest]

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.

 

[Don Guillett]

Sub copyrngtoOPENworkBook()
Range("a1:j29").Copy
Workbooks("OCC_Top10.xls").Sheets("sheet1").Range("a1") _
..PasteSpecial Paste:=xlPasteValues
End Sub

 

[Guest]

Thank you Don.

I can't get it to work. I think there might be a typo ?

 

[Don Guillett]

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.

 

[Guest]

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 = "(e-mail address removed)"
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]

I guess you didn't look at what I proposed?

Sub Macro1()
Workbooks.Open Filename:="J:\Projects\top250\MarketShare July 2007.xls"
Dim s As String
s = Application.InputBox("enter tab name:", 2)
Sheets(s).Range("A1:j29").Copy
'put in your destination sheet name below
Workbooks("OCC_Top10.xls").Sheets("sheet1").Range("a1") _
.PasteSpecial Paste:=xlPasteValues 'ONE dot

'rest of your code. Idon't use Outlook
End Sub

 

[Guest]

Thanks Again. I must be doing something wrong. The code still gets hung up.
Here's what I tried.

Sub Macro1()





ChDir "J:\Projects\top250"

Workbooks.Open Filename:="J:\Projects\top250\MarketShare July 2007.xls"





Dim s As String

s = Application.InputBox("enter tab name:", 2)



Sheets(s).Range("A1:j29").Copy

'put in your destination sheet name below

‘Hung Up Here:

Workbooks("OCC_Top10.xls").Sheets("Sheet1").Range("a1") _

.PasteSpecial Paste:=xlPasteValues