Run-time error'1004 using PasteSpecial

[Casey]

Great Gurus of VBA,
I use the following Code to copy information from dozens of identica
worksheets to a master log. The information is transfered from on
worksheet at at time to the master log. The code has been working fin
until I added protection to the Master Log and so had to add code o
the sheets to unlock the Master Log prior to pasting the data and the
re-locking the master Log. Below is my code. Sorry for all the notes
but I still learning.

The code stops on the line:

Selection.PasteSpecial Paste:xlPlasteValues.........................

With the following Error:

Run-time error '1004'
PasteSpecial method of Range class failed

You guys have helped me so much, I hate to ask for something else, bu
could you explain the error I've made as well as show me how to fi
it.

CODE:
Private Sub SendRFItoLog_Click()

'
' ExportFiguresFromRFI_to_RFILOG Macro
' Macro recorded 1/7/2004 by Casey Wilkins
'

'
Application.ScreenUpdating = False
Range("B1:G1").Copy
Sheets("Master RFI Log").Select
Sheets("Master RFI Log").Unprotect ("geekk")
Sheets("Master RFI Log").Range("A5").Select
'The Activate next blank down makes sure
'the information goes into
'a blank row.
Call ActivateNextBlankDown

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
'The cost event sorter puts all the information
'in order based on RFI #
Call CostEvent_Sorter
'This code makes the selection of the entire sheet go away
'and selects cell C8
Sheets("Master RFI Log").Range("B6").Select
Sheets("Master RFI Log").Protect ("geekk")
DrawingObjects:=True, Contents:=True, Scenarios:=True

Application.ScreenUpdating = True

End Su

 

[Dave Peterson]

It's kind of difficult to guess what happened since portions of your code are
missing.

But in general, you don't need to select to work with ranges/worksheets.

And if you're copy|pastespecial|values, you can just assign the values.

Here's one version that you might be able to alter to do what you need.

Option Explicit
Private Sub SendRFItoLog_Click()

Dim RngToCopy As Range
Dim CellToPaste As Range

Set RngToCopy = ActiveSheet.Range("b1:g1")

With Worksheets("Master RFI Log")
.Unprotect "geekk"
Set CellToPaste = .Range("a5").End(xlDown).Offset(1, 0)
CellToPaste.Resize(RngToCopy.Rows.Count, _
RngToCopy.Columns.Count).Value _
= RngToCopy.Value

Call CostEvent_Sorter

.Protect "geekk"

End With
Application.ScreenUpdating = True

End Sub

Sub CostEvent_Sorter()
With Worksheets("master rfi log")
With .Range("a1:G" & .Cells(.Rows.Count, "A").End(xlUp).Row)
.Sort key1:=.Columns(1)
End With
End With
End Sub

I guessed at what your "ActivateNextBlankDown" sub did.
(.End(xldown).offset(1,0) made since to me.)

And you don't need to select a range to sort it.

I didn't know what the range was, so I guessed A1:G(lastusedcellincolumnA).

And I didn't know what to use for the sort (xlascending/xldescending and what
column(s) you used as your key).