Run time error 1004 - PasteSpecial of Range class failed

**Niciana** · 01-23-2012, 01:42 PM

Hi this is my first ever post... quite new to this coding lark

coding up Excel 2007 and I'm having problems with this error. Firs time it works, second time it fails, third time it works, fourth time it fails... see the pattern! Trying to copy from one workbook to another then move to next clear line and paste next value

Can find a way round it anyone help PLEASE

Private Sub OpenOneFile()
Dim fn As Variant
Dim LR As Long
Dim n As Long

n1 = 0
n = 0

fn = Application.GetOpenFilename("Excel-files,*.xlsx", _
1, "Select One File To Open", , False)
'If TypeName(fn) = "Boolean" Then Exit Sub

' the user didn't select a file
Debug.Print "Selected file: " & fn
Workbooks.Open fn

'Sync Windows
Windows.CompareSideBySideWith "MASTER.xlsm"
Windows.SyncScrollingSideBySide = False

'----------------------------------------------------------------------------------
Windows("RFI_Template.xlsx").Activate
Range("M6").Copy
Windows("MASTER.xlsm").Activate
Sheets("RFI Log").Select
'Unprotect
ActiveSheet.Unprotect Password:="window"
n1 = Range("H3").Value
n = 6 + n1
Cells(n, 2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False (FAILS HERE EVERY 2nd ATTEMPT)
'----------------------------------------------------------------------------------
Windows("RFI_Template.xlsx").Activate
Range("M7").Copy
Windows("MASTER.xlsm").Activate
Cells(n, 3).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
'----------------------------------------------------------------------------------
Windows("RFI_Template.xlsx").Activate
Range("m10").Copy
Windows("MASTER.xlsm").Activate
Cells(n, 4).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
'----------------------------------------------------------------------------------
Windows("RFI_Template.xlsx").Activate
Range("d12").Copy
Windows("MASTER.xlsm").Activate
Cells(n, 6).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
'----------------------------------------------------------------------------------
Windows("RFI_Template.xlsx").Activate
Range("b16").Copy
Windows("MASTER.xlsm").Activate
Cells(n, 8).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
'----------------------------------------------------------------------------------
Range("F2").Copy
Cells(n, 7).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
'----------------------------------------------------------------------------------
n1 = 0
n = 0
'Range("p1") = 0

'Close RFI_Template window
Windows("RFI_Template.xlsx").Activate
ActiveWorkbook.Close False
Windows("MASTER.xlsm").Activate
Range("e6").Select

'Protect Sheet
Windows("MASTER.xlsm").Activate
Sheets("RFI Log").Select
ActiveSheet.Protect Password:="window", DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True

End Sub

**arlu1201** · 01-23-2012, 02:37 PM

Welcome to the forum.

Please attach the file that you use along with the above code so its easy to troubleshoot.

**Niciana** · 01-23-2012, 04:39 PM

High and thanks for the help I think I have attached the file the folder goes on the desktop and has the template the the code uses. The master Nav Tab has the button to activate the copy and paste...

Thanks again

ps you will need to put the RFI Template into a folder called Master RFI and leave the Master on the desktop. Use the Master Nav Tab to activate

**arlu1201** · 02-09-2012, 11:45 AM

I guess it would be better if you explain what you are trying to achieve through your code. You have used a lot of select & activate statements which can be eradicated. I can provide you an alternate code if you let me know what you want the code to do.