Pastespecial of range class failed

[ralower2]

Hi All,

Can anyone tell me why I am receiving the Pastespecial of range class failed error when trying to paste a copied range of rows from one workbook to another? My code is below:


Sub GetPricingData()


Application.ScreenUpdating = False
Dim StartDate, EndDate As Date


Set MySheet = ThisWorkbook.Worksheets("Daily Pricing")


StartDate = Sheets("Daily Pricing").Range("B1").Value
EndDate = Sheets("Daily Pricing").Range("B2").Value


With MySheet
Lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
LastCol = .Range("A" & .Columns.Count).End(xlToLeft).Column
Set MyRange = .Range(.Cells(5, 1), .Cells(Lastrow, LastCol))
End With


With MySheet
.AutoFilterMode = False
If .FilterMode = True Then
.ShowAllData
End If
End With


With MyRange
.AutoFilter Field:=1, Criteria1:=">=" & StartDate, Operator:=xlAnd, Criteria2:="<=" & EndDate
End With


ChDir "H:\Risk and Reporting"
Workbooks.Open Filename:="H:\Risk and Reporting\Pricing Main.xlsm"
Sheets("All Pricing").Select




With ActiveSheet
Lastrow2 = .Range("E" & .Rows.Count).End(xlUp).Row
LastCol2 = .Range("E" & .Columns.Count).End(xlToLeft).Column
Set MyRange2 = .Range(.Cells(5, 5), .Cells(Lastrow2, LastCol2))
End With


With ActiveSheet
.AutoFilterMode = False
If .FilterMode = True Then
.ShowAllData
End If
End With


With MyRange2
.AutoFilter Field:=5, Criteria1:=">=" & StartDate, Operator:=xlAnd, Criteria2:="<=" & EndDate
Range("E6:BBW" & Lastrow2).SpecialCells(xlCellTypeVisible).Copy
End With


Worksheets("All Pricing").AutoFilterMode = False
ActiveWindow.Close


Windows("Pricing.xlsm").Activate
Sheets("Daily Pricing").Select


Range("A6:BBW" & Lastrow).SpecialCells(xlCellTypeVisible).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False




End Sub

Appreciate any help provided!