1

I am trying to copy a range from one sheet to another which is a result of a comparison loop. the loop first compares values in one of the cells of a row with something on the sheet and if its a match, it copies that entire range to another sheet. Here is my code:

        Worksheets("Sheet1").Cells(1, 14).Value = Driver_sel
        For k = 1 To Worksheets("Sheet1").Cells(Rows.Count, 2).End(xlUp).Row
           If ((Cells(k, 2))) = Cells(1, 14) Then
             Worksheets("Sheet1").Range(Cells(k, 1), Cells(k, 12)).Copy
             ActiveSheet.Cells(2, 14).PasteSpecial Transpose:=True
           End If
        Next k
Rohan Jain
  • 11
  • 1
  • Where do you use the second sheet? You should be explicit in the code. I'm assuming the destination sheet is the `ActiveSheet`, no? Also, why have the `((Cells(k,2)))` instead of just `Cells(k, 2)`? Also, what's your question...is there an error thrown, is it not doing what you expect, etc... – BruceWayne Jun 28 '17 at 20:21
  • Hi Bruce, sorry for not being clear in first go. Yes, the destination is activesheet which is ("Sheet2") ((cells(k,2))) should have been cells(k,2). I was playing around with something when I forgot to remove that. It is giving me "run-time error '1004' – Rohan Jain Jun 28 '17 at 20:24
  • Use [AUTOFILTER](https://stackoverflow.com/questions/11631363/how-to-copy-a-line-in-excel-using-a-specific-word-and-pasting-to-another-excel-s). If you want to still use loops then see [THIS](https://stackoverflow.com/questions/44774795/copying-the-matched-row-in-another-sheet) – Siddharth Rout Jun 28 '17 at 20:24
  • Do you want to look on `Sheet1` for the `If` statement? Or are you counting the rows in `Sheet1` (say it's 100), then on your other sheet, checking cells 1 to 100? – BruceWayne Jun 28 '17 at 20:31
  • @BruceWayne The `Sheet1` has multiple rows and columns. The `if` statement is checking one cell from this matrix in `Sheet1` and if that matches, I want to copy that entire row containing that cell to another `Sheet2` – Rohan Jain Jun 28 '17 at 20:39
  • Then you need to tell VBA you want it to go to `Sheet2`. Something like `Worksheets("Sheet2").Cells(2, 14).PasteSpecial Transpose:= True`, no? – BruceWayne Jun 28 '17 at 20:53
  • Yes, but `Sheet2` is already active in the function this loop is a part of. – Rohan Jain Jun 28 '17 at 20:56
  • So I found a solution, it is working partially though. Instead of copying just that row, it copies all the rows. Idk where am I going wrong `With Sheets("Sheet1") Worksheets("Sheet1").Cells(1, 14).Value = Driver_sel For j = 1 To Worksheets("Sheet1").Cells(Rows.Count, 2).End(xlUp).Row If Cells(j, 2).Value = Cells(1, 14) Then .Range(.Cells(j, 1), .Cells(j, 12)).Copy ActiveSheet.Cells(2, 14).PasteSpecial Transpose:=True End If Next j End With End If` – Rohan Jain Jun 28 '17 at 21:04
  • Am I doing the comparison right way? The cells I am comparing don't have integers but string values. – Rohan Jain Jun 28 '17 at 21:17

0 Answers0