ExcelBanter - 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

.