How to fix 'Run-time error '1004' PasteSpecial

Question

I have a file (called original) that has partially information for each row. Each row has a file name column (from where information is to be captured from).

For each row I'd like to open up the file in the file name column, and grab information from certain rows.

In the file it is only one column, with rows "Supplier Number : _____", the location of this row is variable, so I'd like to iterate through each row in the file to copy this cell value and paste it into the original file in the corresponding row.

This is what I have so far:

Const FOLDER_PATH = "C:\Users\[user]\Downloads\"

Sub iterateThroughAll()
    ScreenUpdating = False
    Dim wks As Worksheet
    Set wks = ActiveSheet
    Dim source As String
    Dim target As String
    Dim update As String
    Dim rowT As Integer

    rowT = 2
    rowTT = 1

    Dim rowRange As Range
    Dim colRange As Range
    Dim rowRangeT As Range

    Dim LastCol As Long
    Dim LastRow As Long
    Dim LastRowT As Long
    LastRow = wks.Cells(wks.Rows.Count, "A").End(xlUp).Row

    Set rowRange = wks.Range("A2:A" & LastRow)

    'Loop through each row
    For Each rrow In rowRange
        source = FOLDER_PATH & wks.Cells(i, 18).Value 'the name of the file we want to grab info from in this Column, always populated

        'if the cell is empty, search through the file for "Supplier Number : "
        If IsEmpty(wks.Cells(rowT, 19)) Then
            Set wb = Workbooks.Open(source)
            wb.Activate
            LastRowT = Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
            Set rowRangeT = wks.Range("A1:A" & LastRowT)
            For Each i In rowRangeT
                If InStr(i.Cells.Offset(rowTT), "Supplier") > 0 Then
                     Range("A" & rowTT).Select
                     Selection.Copy
                     Windows("Get Supplier Number.xlsm").Activate
                     Range("A" & rowT).Select
                     wks.Paste
                Else
                    rowTT = rowTT + 1
                End If
            Next i

            wb.Close


    Next rrow
    ScreenUpdating = True
End Sub

I get the pastespecial error 1004.

What is expected is that for each row in "Get Supplier Number.xlsm", the row's A column is updated with the information

Thank you for helping!

You haven't mentioned which line gives the error - that would help greatly. Include `Option Explicit` at the top of the module - you may have to fix any undeclared values along the way. I found it hard to follow the logic in the code - but the big hint to me was that I couldn't work out where you are pasting the information (whereabouts in `wks` is the information going?). Oh, and this strange line `Windows("Get Supplier Number.xlsm").Activate` which seems to be randomly stuffed into the code for no logical reason. — AJD, Sep 07 '19 at 05:59
Get rid of `.Activate` and `.Select`. You may want to see [THIS](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Work with the objects directly. Also issue the pastespecial right after the copy command and you will be ok... — Siddharth Rout, Sep 07 '19 at 06:27

mits · Answer 1 · 2019-09-07T10:52:11.863

First of all you should get rid of Activate and Select methods. You don't have to use them and they give nothing to your code. Using them is not a good approach.

To avoid them you should use specific references. Which you are doing so, until a specific point. Inside the for loop, after setting the wb, replace everything with the following:

With wb.Worksheets(1)
    LastRowT = .Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
    Set rowRangeT = .Range("A1:A" & LastRowT)
    For Each i In rowRangeT
        If InStr(i.Cells.Offset(rowTT), "Supplier") > 0 Then
             .Range("A" & rowTT).Copy wks.Range("A" & rowT)
        Else
            rowTT = rowTT + 1
        End If
    Next i
    wb.Close
End With

I think this should do the job for you.

PS: If you need just the value of the cell in the opened workbook, then you could replace the Copy line with a simple equality:

wks.Range("A" & rowT) = .Range("A" & rowTT)

Thank you for the help, but now when I replace everything below the wb, i get a next without for error (https://imgur.com/a/A9fLgwu). Even though there's a next rrow at the end of my for loop — Vincent Le, Sep 07 '19 at 12:48
You don't have an `End If` statement for your first if. I didn't notice this. — mits, Sep 07 '19 at 15:25

How to fix 'Run-time error '1004' PasteSpecial

1 Answers1