Please help! I cannot understand why I might be getting the runtime 1004 error: PasteSpecial Method of Range Class Failed for the red highlighted line.
What I am trying to do:
When a user completely fills out the form they click a button to copy the information to another workbook. I want the copied information to be sorted in descending order, thus it inserts a blank row at the top, copies the first line of info, pastes the values to that blank first row, then repeats.
Dim x As Long
Dim BBS As Workbook
Dim wbOpen As Workbook
If WorksheetFunction.CountBlank(ActiveSheet.Range("B8:I12")) > 0 Then
MsgBox "Incomplete!", vbOKOnly
GoTo ExitOut:
Else
Set wbOpen = Workbooks.Open("C:\Documents and Settings\ebickel\Desktop\BBS_Records.xlsx")
Set BBS = ThisWorkbook
For x = 8 To 12
Workbooks("BBS_Records.xlsx").Worksheets("Record").Rows(1).Insert shift:=xlShiftDown
BBS.Activate
BBS.Worksheets("Observations").Range(Cells(x, 1), Cells(x, 9)).Copy
wbOpen.Worksheets("Record").Range("A1").PasteSpecial Operation:=xlPasteValues
Application.CutCopyMode = False
Next x
End If
Bookmarks