Run-time error 1004- how to get rid of this?



My macro consists of copying certain data from one worksheet to another. Pretty simple, yet I keep getting this message "Run-time error '1004': Method 'Range' of object'_Worksheet' failed"


This is the code, yet the BOLD is where it highlights:


    'Locate last empty row in Master File
    FinalRow = Source.Range("B8").End(xlDown).Row
    'Loop for updating data
    For x = 9 To FinalRow

            'Update Role
            Source.Range("B" & x).Copy
            Target.Range("F" & NextRow).PasteSpecial xlPasteValues


Please help, I know it might be something very simple.




Question Info

Last updated January 31, 2019 Views 3,764 Applies to:

I looked at what you have. Your are refering to sheets by their index which is generally not advised. If sheets are added or deleted or moved around then your code will reference the wrong sheets. The indexes you are using are referencing the wrong sheets. Master(1) is referenceing Sheet 3 which is hidden. You were writing data all along to this hidden sheet. You wanted to write to sheet (2) which is labeled Target. 

I switched back to xlDown based on your setup. Note that if your end user leaved a blank line this will stop at the blank and not grab the data after the blank. I would be inclined to re-think how you are doing this...


Sub Compile()

    Dim Form As Workbook, Master As Workbook
    Dim Source As Worksheet, Target As Worksheet
    Dim x As Long, FinalRow As Long, NextRow As Long
    Dim FN As String
    Set Master = ThisWorkbook 'Thisworkbook is the workbook that is running the code
    Set Target = Master.Sheets("Target") 'Changed from index to tab name
    FN = Application.GetOpenFilename(, , , , False)
    If FN = "False" Then Exit Sub '***CHANGE FROM FileName****************
    Set Form = Workbooks.Open(Filename:=FN)
    Set Source = Form.Sheets(1) 
    FinalRow = Source.Cells(8, "B").End(xlDown).Row 'Changed back but you should review
    NextRow = Target.Cells(Rows.Count, "A").End(xlUp).Row + 1

    'Loop for updating data
    For x = 9 To FinalRow
        'Update Role

'Cells is a little cleaner than building a range with a contcatenation
        Target.Cells(NextRow, "F").Value = Source.Cells(x, "B").Value

        Target.Range("E" & NextRow).Value = Source.Range("Approver").Value
        'Update Dept/Div Number
        Target.Range("C" & NextRow).Value = Source.Range("F" & x).Value
        'Update Dept/Div Names
        Target.Range("D" & NextRow).Value = Source.Range("D" & x).Value
        'Update Job Number
        Target.Range("G" & NextRow).Value = Source.Range("G" & x).Value
        'Update Manager Name
        Target.Range("E" & NextRow).Value = Source.Range("Approver").Value
        'Update Date
        Target.Range("H" & NextRow).Value = Source.Range("Date").Value
        'Update Business Unit
        Target.Range("A" & NextRow).Value = Source.Range("BU").Value
        'Update Region
        Target.Range("B" & NextRow).Value = Source.Range("Region").Value
        'Update Comments
        Target.Range("I" & NextRow).Value = Source.Range("H" & x).Value
        NextRow = NextRow + 1 '*****Added this****************
    Next x
End Sub

If this answers your question, please mark as answer.

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.