VBA code to copy selected columns from rows that meet a condition to another sheet

Question

I've just started out with VBA code for Excel so apologies if this appears basic. I want to do the following...

Check Column J (J5 to J500) of a sheet called "Index" for the presence of value "Y". This is my condition. Then I want to only copy Columns C to I Only of any row that meets the condition to an existing Sheet and to Cells in a different position, i.e. If Index values C3 to I3 are copied I would like to paste them to A5 to G5 of the active sheet i'm in, say Sheet2.

If there is a change to the index sheet I would like the copied data to automatically, If possible. How could it work if new data is added to Index?

After a lot of searching here I found this. From this question I changed the code slightly to suit my requirements and this will copy entire rows that meet the condition to a sheet that I run the macro from, but I'm stumped for how to select certain columns only.

Sub CopyRowsAcross() 

Dim i As Integer 
Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("Index") 
Dim ws2 As Worksheet: Set ws2 = ActiveSheet 

For i = 2 To ws1.Range("B65536").End(xlUp).Row 
If ws1.Cells(i, 2) = "Y" Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 2).End(xlUp).Row + 1) 
Next i 

End Sub

Any Help is appreciated

John

EDIT: I have created a mock-up and its located at https://docs.google.com/file/d/0B0RttRif9NI0TGl0N1BZQWZfaFk/edit?usp=sharing

The A and B Columns are not required when copied - either is Column J - thats what I am using to check for the condition.

Thanks for all your help so far.

If you want to be able to run this more than once without first wiping all content from the destination sheet, then you will need to have at least one **unique** value which can be used to indentify each row, otherwise it's going to be impossible to match rows between the two sheets. — Tim Williams, Mar 13 '13 at 21:19
Thanks for your reply Tim. Thinking about It it might be better to wipe the data and recopy from the Index Sheet when I want to update, e.g. content of the index has changed or a new line has been added. I think that just means I need to manually update by rerunning the macro. I'd be ok with that. Would I thne still need unique references If I were to take that approach? — JohnM, Mar 13 '13 at 21:58

Atl LED · Answer 1 · 2013-03-14T00:03:35.867

0

That's borrowing some old code. In this you are checking for the last row used, if you know that you only want to go to 500, you can just use the integer:

Sub try2()

  Dim i, Y, x As Long 'you didn't mention what Y was, so it could also be a string.
  Dim ws1 As Worksheet: Set ws1 = ActiveWorkbook.Sheets("Index")
  Dim ws2 As Worksheet: Set ws2 = ActiveWorkbook.Sheets("Sheet2") 'active sheet can get  you into trouble
  Dim Ary1 As Range
  Dim ary2 As Range


   x = 5
   Y = "Y" 'for the sake of argument
         'For i = 2 To ws1.Range("B65536").End(xlUp).Row   This is if you are looking for the last row in MsOf2003 or earlier.  If you know that you are only looking to row 500, then hard code the intiger.
   For i = 2 To 500:
        'If ws1.Cells(i, 2) = "Y" You mentioned you were interested in column J, so we need to change the 2 to 10 (Column B to Column J)
         If ws1.Cells(i, 10) = Y Then
            ws1.Activate
            Set Ary1 = Range(Cells(i, 3), Cells(i, 9))
            ws2.Activate
            Set ary2 = Range(Cells(x, 1), Cells(x, 7)) 'avoid copying all together you don't need it
            ary2.Value = Ary1.Value
            x = x + 1
         End If
   Next i
  End Sub

I'm writing this on a phone not on a compiler, so there may be a syntax error in there and this should be seen as pseudo-VBA code. I can check later to see if you got it to work. You will have to watch out on where you put things if you don't want them to be overwritten.

edited Mar 14 '13 at 00:03

answered Mar 13 '13 at 21:30

Atl LED

656
2
8
31

Thanks Atl LED. I should have clarified that the Y is the actual cell value I am looking for as opposed to a variable. I might have coded that incorectly. Bearing that in mind would your code above be fundamentally different? I'm going to take it and give it a go anyway to see how it turns out and i'll post back. Thanks – JohnM Mar 13 '13 at 22:02
I tried this and It gave an error message when I ran the code: Run-time error '1004' Method 'Range' of object'_worksheet' failed. I have added a sample spreadsheet in the original question to make it easier to see what I am doing. – JohnM Mar 13 '13 at 22:43
@JohnM sorry, I'm still not where I can check the code, but did you change the worksheet name Sheet2 as in your example? You need to change the variables to what makes sense. – Atl LED Mar 13 '13 at 22:50
Yes I did change the code slightly to reflect Sheet2 as I updated the tab Name. I also changed Y to "Y" in the IF statement as I am looking for a Cell Value that is Y, if that makes sense. Thanks – JohnM Mar 13 '13 at 22:56
@JohnM Try that. The above worked for me. I'm not sure if there is a better solution without changing the focus. That would certainly be faster. – Atl LED Mar 14 '13 at 00:04
Thanks for your reply Atl LED. Can you confirm if the code in the file in the original question works for you? It does not work for me. I'm using Excel 2010 is that makes any difference. – JohnM Mar 14 '13 at 00:10
@JohnM Yes it does for me, did you notice that I changed it? I'm using '10 as well – Atl LED Mar 14 '13 at 00:16
@ Atl LED Thanks! It works :-) I hadn't noticed the changes. Could I ask you for one more thing please? Could you explain what each line of the For Loop is actually doing - especailly the ranges and cells areas. As a noob i'm a little stumped and I want to transfer the learnings from this to other sheets. Thanks, JohnM – JohnM Mar 14 '13 at 00:40

score 0 · Accepted Answer · answered Mar 14 '13 at 02:06

0

Here is the more elegant solution, more similar to my original post. The only difference is that the Cells reference is qualified to the correct sheet.

Sub try3()
Dim i, x As Long
Dim Y as String
Dim ws1 As Worksheet: Set ws1 = ActiveWorkbook.Sheets("Index")
Dim ws2 As Worksheet: Set ws2 = ActiveWorkbook.Sheets("Sheet2") 'active sheet can get  you into trouble

 x = 5
 Y = "Y"
 For i = 2 To 500:
    If ws1.Cells(i, 10) = Y Then
       Range(ws2.Cells(x, 1), ws2.Cells(x, 7)).Value = Range(ws1.Cells(i, 3), ws1.Cells(i, 9)).Value
      x = x + 1
    End If
 Next i
End Sub

answered Mar 14 '13 at 02:06

Atl LED

656
2
8
31

@JohnM Within the For statement, we are checking column J [10] to see if it = the variable Y. If that is true then target range value from sheet2 is set to the values from the source range on Index. X is putting sequential true instances on sequential rows (starting at row 5 per your question). – Atl LED Mar 14 '13 at 02:25
Thanks Atl LED. I have one final question if you don't mind. Some of the cells in the index will have formulas - I forgot to mention that small but very important detail in the original question... How would I change the code so that Cell formatting (i.e. Formulas, etc) is included? – JohnM Mar 14 '13 at 17:24
@JohnM I think you need to ask a new question/look around for that one. – Atl LED Mar 14 '13 at 21:33

VBA code to copy selected columns from rows that meet a condition to another sheet

2 Answers2

Linked