CODEWITHSUNDEEP
excelvba
adam
adam

Reputation: 289

Excel VBA - Copy cells between sheets if cell value satisfies criteria

This code works, but it is taking the dstRow.Row and using that as the row it is copying from. I don't want that. I want to use srcMatchRow = cell.Row

What am I missing?

Sub CopyCellsBetweenSheets()
  Dim srcFile As String
  Dim dstFile As Workbook
  Dim srcSheet As Worksheet
  Dim dstSheet As Worksheet
  Dim cell As Range
  Dim i As Integer
  Dim lastRow As Integer
  Set dstFile = Application.Workbooks.Open("C:\dev\t\each.xlsm")
  Set srcSheet = ThisWorkbook.Worksheets("Final")
  Set dstSheet = dstFile.Worksheets("RoomA")
  srcLastRow = srcSheet.Range("A" & Rows.Count).End(xlUp).Row
  dstRow = 11  'start copy on row 11
  For Each cell In srcSheet.Range("A1:A" & srcLastRow)
    If cell.Value >= 8 And cell.Value <= 10 Then
      srcMatchRow = cell.Row
      srcSheet.Range("A" & srcMatchRow & ":L" & srcMatchRow).Copy
      dstSheet.Range("A" & dstRow & ":L" & dstRow).PasteSpecial
      dstRow = dstRow + 1
    End If
  Next
End Sub

The specific example should get cols A:L for rows that have a column A value of 8, 9, or 10 in srcFile. Then copy those respective columns to rows 11, 12, 13 in dstFile.

Instead, it finds the matching column A cell is on row 20, and then ignores that and instead grabs whatever is on row 11, regardless of whether it satisfies the condition.

To generalize, I want to get columns from a row in another workbook that matches criteria and saves to rows beginning at dstRow; dstRow incrementing by 1 for each additional match.

Upvotes: 0

Views: 458

Answers (2)

adam
adam

Reputation: 289

This was a case of not checking the source data.

The cells had been reordered in worksheet "Final" with formulas so I had been copying a formula. The solution is:

    dstSheet.Range("A" & dstRow & ":L" & dstRow).PasteSpecial Paste:=xlPasteValues

Upvotes: 0

Lance Roberts
Lance Roberts

Reputation: 22842

You need to clarify the value of cell, since it's actually a range, probably like this:

For Each cell In srcSheet.Range("A1:A" & srcLastRow).Rows

Upvotes: 1

Related Questions