Loop through each row in one worksheet and copy to another worksheet's named ranges

Question

I've been trying to work through this but have not been successful so far. The code works, but in unintended ways unfortunately.

The MACRO workbook is the master workbook with all data fields on the "Data" sheet. The LEAN workbook contains certain data points that I'd like to copy and paste into named ranges on the "Data" sheet within the MACRO workbook to the last row available.

For ex:

Macro Workbook has columns:

Eng #  Eng Name   Date Started     Date Completed    KPI1   KPI2  

100    ABC        12/01/2018       12/10/2018        50    100

Lean Workbook has columns:

Eng #   Date Started   Date Completed   KPI#   Score
101     12/02/2018      12/03/2018      1       50
101     12/02/2018      12/03/2018      2       10
102     11/01/2018      11/20/2018      1       100
102     11/01/2018      11/20/2018      4       100

The goal is two-fold:

Grab data from the Lean workbook's specific columns and paste into the Macro workbook's "Data" sheet to the last row available
Grab the KPI # from the Lean Workbook (which are in rows) and past the score to the named ranges/columns that match the KPI # in the "Data" sheet.

My attempt at the code below:

Public Sub GrabLeanFileData()

Dim wbmacro As Workbook
Dim wblean As Workbook

Set wbmacro = Workbooks.Item("MacroFile.xlsm")
Set wblean = Workbooks.Item("Subcontractor CA - KPI's Lean.csv")

Dim wsmacro As Worksheet
Dim wslean As Worksheet

Set wsmacro = wbmacro.Worksheets.Item("Data")
Set wslean = wblean.Worksheets.Item("Subcontractor CA - KPI's Lean")

Dim leanrange As Range
Set leanrange = wslean.Range("A2:A15000")

wblean.Activate

Dim cell As Range

i = 1

For Each cell In leanrange

    If leanrange.Range("A2") <> "E1002" Then
        Range("A2").Offset(i, 0).Value = wsmacro.Range("Enum").Value
        Range("B2").Offset(i, 0).Value = wsmacro.Range("ds").Value
        Range("C2").Offset(i, 0).Value = wsmacro.Range("dc").Value
        Range("D2").Offset(i, 0).Value = wsmacro.Range("kpi1").Value


        i = i + 1
    End If

Next cell

End Sub

I wanted to skip the test Eng # of 1002 so i put that condition in.

"The code works, but in unintended ways unfortunately." - What do you mean by this? Are you getting an error? What's your expected result, and what's the current result? — dwirony
Also, you're iterating through cell in your For Each loop but you never use it in your loop. — dwirony
Does it change anything if you explicitly name the range in your for loop as wslean.Range("A2").Offset(i,0)? Just a guess. Without knowing what your code is outputting, it's hard to identify the problem. — C Dieguez
without making any changes, it seems to to take the column headings from the MACRO sheet and paste it into the LEAN workbook starting from cell A2. It seems to be functioning in reverse :( — VBAWARD
So is wsmacro.Range("enum") your column header? If so, I think you've just got the syntax reversed. Does wsmacro.Range("enum") = wslean.Range("A2").Offset(i,0) solve the problem? — C Dieguez

C Dieguez C Dieguez · Accepted Answer · 2018-12-11T20:08:28

What I gather you want to do is to take the value from each cell in leanrange and the adjacent columns and insert it into your table in the macro workbook under the appropriate header.

I think there are two problems here:

the syntax in your for loop is reversed
you're offsetting ineffectively

Here's what I would suggest: since you're using a for each loop, use the cell object to loop through all the cells in the range instead of using offset. Then, you need to offset the row in your macro workbook, otherwise you're just pasting to the same row over and over again. That's why your headers are getting overwritten. You can use a second loop to offset cell so that you can loop through each column in leanrange. Finally, since it looks like your ranges "Enum", "ds", "dc" etc. are all adjacent columns, I've simplified things by using j to also offset your columns in the macro workbook.

Dim i As Integer
Dim j As Integer
Dim cell As Range

i = 1
For Each cell In leanrange
    If leanrange.Range("A2") <> "E1002" Then
        For j = 0 To 3
            wsmacro.Range("Enum").Offset(i, j) = cell.Offset(0, j).Value
        Next j
        i = i + 1
    End If
Next cell

Loop through each row in one worksheet and copy to another worksheet's named ranges

1 Answers