Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste Code
Below is my code...
First the user will always copy something on a spreadsheet and then they will click the button that has the below code in it(stored in personal.xls). What the code does is pastes the contents of the clipboard in cell C5 of sheet1 on workbook P:\Stats Manager.xls. If the workbook is open the code works perfect. If P:\Stats Manager.xls is not open, the code then opens the file but then I get the error "Paste method of Worksheet Class Failed.". I have to go back to the source workbook/worksheet and hit copy and then I run the code again and it works (because P:\Stats Manager.xls is open) Can anyone please help me? Thanx Sub ImportData() ' ' ImportData Macro ' Macro recorded 12/1/2003 by SEGeneric ' ' Dim wb As Workbook If MsgBox("Would you like to Import Data?", vbYesNo) = vbNo Then Exit Sub End If On Error Resume Next Set wb = Workbooks("Stats Manager.xls") On Error GoTo 0 If wb Is Nothing Then Set wb = Workbooks.Open("P:\Stats Manager.xls") With Worksheets(1) Set rng = .Range("A4:Z100") Set rng1 = .Range("A4:AZ4") End With res = Application.Match("Ext", rng1, 0) If Not IsError(res) Then Set rng2 = rng1(1, res) rng.Sort Key1:=rng2, Order1:=xlAscending, _ Header:=xlYes, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom End If '***Start of Paste Code 'wb.Worksheets(1).Range("C5").Select Application.Goto reference:=wb.Worksheets(1).Range("C5") ActiveSheet.Paste Destination:=Worksheets(1).Range("C5") 'ActiveSheet.Paste Range("B5:AZ100").Select Range("AZ5").Activate With Selection .HorizontalAlignment = xlCenter .WrapText = False .Orientation = 0 .AddIndent = False .ShrinkToFit = False .MergeCells = False End With Range("A2:AZ3").Select MsgBox "Data Imported Successfully!", vbOKOnly If MsgBox("Would you like to Save Workbook?", vbYesNo) = vbNo Then Exit Sub End If ActiveWorkbook.Save End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste Code
Hey guys,
I think I got it. I made the following modifications to it and now it seems to be working great. Thanx Todd Sub ImportData() ' ' ImportData Macro ' Macro recorded 12/1/2003 by SEGeneric ' ' Dim wb As Workbook If MsgBox("Would you like to Import Data?", vbYesNo) = vbNo Then Exit Sub End If On Error Resume Next Set wb = Workbooks("Stats Manager.xls") On Error GoTo 0 If wb Is Nothing Then Set wb = Workbooks.Open("P:\Stats Manager.xls") ActiveWindow.WindowState = xlMinimized Selection.Copy End If With Worksheets(1) Set rng = .Range("A4:Z100") Set rng1 = .Range("A4:AZ4") End With res = Application.Match("Ext", rng1, 0) If Not IsError(res) Then Set rng2 = rng1(1, res) rng.Sort Key1:=rng2, Order1:=xlAscending, _ Header:=xlYes, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom End If '***Start of Paste Code 'wb.Worksheets(1).Range("C5").Select Application.Goto reference:=wb.Worksheets(1).Range("C5") ActiveSheet.Paste Destination:=Worksheets(1).Range("C5") 'ActiveSheet.Paste Range("B5:AZ100").Select Range("AZ5").Activate With Selection .HorizontalAlignment = xlCenter .WrapText = False .Orientation = 0 .AddIndent = False .ShrinkToFit = False .MergeCells = False End With Range("A2:AZ3").Select MsgBox "Data Imported Successfully!", vbOKOnly If MsgBox("Would you like to Save Workbook?", vbYesNo) = vbNo Then Exit Sub End If ActiveWorkbook.Save End Sub -----Original Message----- Below is my code... First the user will always copy something on a spreadsheet and then they will click the button that has the below code in it(stored in personal.xls). What the code does is pastes the contents of the clipboard in cell C5 of sheet1 on workbook P:\Stats Manager.xls. If the workbook is open the code works perfect. If P:\Stats Manager.xls is not open, the code then opens the file but then I get the error "Paste method of Worksheet Class Failed.". I have to go back to the source workbook/worksheet and hit copy and then I run the code again and it works (because P:\Stats Manager.xls is open) Can anyone please help me? Thanx Sub ImportData() ' ' ImportData Macro ' Macro recorded 12/1/2003 by SEGeneric ' ' Dim wb As Workbook If MsgBox("Would you like to Import Data?", vbYesNo) = vbNo Then Exit Sub End If On Error Resume Next Set wb = Workbooks("Stats Manager.xls") On Error GoTo 0 If wb Is Nothing Then Set wb = Workbooks.Open("P:\Stats Manager.xls") With Worksheets(1) Set rng = .Range("A4:Z100") Set rng1 = .Range("A4:AZ4") End With res = Application.Match("Ext", rng1, 0) If Not IsError(res) Then Set rng2 = rng1(1, res) rng.Sort Key1:=rng2, Order1:=xlAscending, _ Header:=xlYes, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom End If '***Start of Paste Code 'wb.Worksheets(1).Range("C5").Select Application.Goto reference:=wb.Worksheets(1).Range("C5") ActiveSheet.Paste Destination:=Worksheets(1).Range("C5") 'ActiveSheet.Paste Range("B5:AZ100").Select Range("AZ5").Activate With Selection .HorizontalAlignment = xlCenter .WrapText = False .Orientation = 0 .AddIndent = False .ShrinkToFit = False .MergeCells = False End With Range("A2:AZ3").Select MsgBox "Data Imported Successfully!", vbOKOnly If MsgBox("Would you like to Save Workbook?", vbYesNo) = vbNo Then Exit Sub End If ActiveWorkbook.Save End Sub . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy and paste using vb code | Excel Discussion (Misc queries) | |||
How to paste html code to xls | Excel Discussion (Misc queries) | |||
(Cut - Paste in Excel )code in vc++ | New Users to Excel | |||
Where do I paste this Code? | Excel Programming | |||
Need Help with Code - Copy & Paste | Excel Programming |