mapping certain values from one worksheet to another using loop and array

Question

I'm trying to map certain values from a data list to another worksheet if certain conditions are met. I have done this in the past using arrays but now I am getting a runtime error and I can't debug. The mapping works like this (assuming condition is met): column A to A, B to B, AK to C, AL to D and AM to E.

Sub newcontracts()
    Dim source As Variant
    Dim destination As Variant
    Dim j As Integer
    Dim x As Integer
    Dim LastRow As Long

    source = Array("A", "B", "AK", "AL", "AM")
    desitnation = Array("a", "b", "c", "d", "e")

    LastRow = ThisWorkbook.Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row
    LastRow3 = ThisWorkbook.Sheets("New Contracts").Cells(Rows.Count, 1).End(xlUp).Row

    Worksheets("New Contracts").Range("A2:i10").ClearContents

    With Worksheets(2)
        For x = 11312 To LastRow
            If (IsEmpty(Cells(x, 39)) = False Or Cells(x, 39) <> 0) And Cells(x, 40) = "no" Then
                For j = 0 To 4
                    .Range(source(j) & x).Copy Sheets("New Contracts").Range(destination(j) & Rows.Count).End(xlUp)(1)
                Next j
            End If
        Next x
    End With
End Sub

Thanks a million!

"*I am getting a runtime error and I can't debug.*" (1) What error? (2) Where (which code line)? (3) In `ThisWorkbook.Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row` you need to specify the sheet for `Rows.Count` too, like `ThisWorkbook.Sheets(2).Cells(ThisWorkbook.Sheets(2).Rows.Count, 1).End(xlUp).Row` otherwise it assumes ActiveSheet which fails either for this line or for the following line where you have the same issue. **Every** `Rows` needs a specific sheet specified (there are a few in your code!) same for **all** `Cells`. — Pᴇʜ, Sep 19 '17 at 12:03
(4) [**Always** use `Long` instead of `Integer`](https://stackoverflow.com/a/26409520/3219613) Excel has more rows than Integer can handle! — Pᴇʜ, Sep 19 '17 at 12:08
Also notice that `Sheets(2)` may not be the same worksheet as `Worksheets(2)` nor the same as `Sheets2`. The `Sheets` collection additionally contains charts not only workbooks. So make sure which one to use. Most times people really meant to use `Worksheets` if they write `Sheets`. Make sure you know that difference! Using both mixed up will make you run into issues if you use eg. charts. — Pᴇʜ, Sep 19 '17 at 12:12

score 0 · Answer 1 · answered Sep 21 '17 at 11:50

So this is the new iteration of the code that seems to be working. @Peh, i'm not sure I have to specify every sheet. As I declare what sheet before but your points are noted. Anyway...here is the code...I will try Ralphs version soon...thanks again everyone for your input

Sub newcontracts()

Dim source As Variant
Dim destination As Variant
Dim j As Integer
Dim x As Integer
Dim LastRow As Long
Dim LastRow3 As Long

source = Array("A", "B", "AK", "AL", "AM")
destination = Array("a", "b", "c", "d", "e")


LastRow3 = ThisWorkbook.Sheets("New Contracts").Cells(Rows.Count, 1).End(xlUp).Row

Worksheets("New Contracts").Range("A2:i10").ClearContents

With Worksheets(2)

LastRow = .Cells(Rows.Count, 1).End(xlUp).Row

For x = 25 To LastRow

If .Rows(x).EntireRow.Hidden = False Then

If (IsEmpty(.Cells(x, 39)) = False Or .Cells(x, 39) <> 0) And .Cells(x, 40) = "no" Then
  For j = 0 To 4
.Range(source(j) & x).Copy Sheets("New Contracts").Range(destination(j) & Rows.Count).End(xlUp)(2)
Next j
End If
End If
Next x




End With

End Sub

mapping certain values from one worksheet to another using loop and array

1 Answers1