|
Macro Problem
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. |
Macro Problem
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. |
Macro Problem
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. |
Macro Problem
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. |
Macro Problem
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 -- Don Guillett Microsoft MVP Excel SalesAid Software "carl" wrote in message ... 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. |
Macro Problem
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 He Workbooks("OCC_Top10.xls").Sheets("Sheet1").Range( "a1") _ .PasteSpecial Paste:=xlPasteValues "Don Guillett" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "carl" wrote in message ... 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. |
| All times are GMT +1. The time now is 03:21 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com