0

I'm trying to create a vba that copy the cell that match my condition and paste it to another sheet but my problem is it copy all rows with that matched with what I am looking for. I only need to copy the cell that matched. Here is my code 

Sub format()

a = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To a
If Worksheets("Sheet1").Cells(i, 1).Value Like "*application_id*" Then
    Worksheets("Sheet1").Cell(i).Copy
    Worksheets("Sheet2").Activate
    b = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
    Worksheets("Sheet2").Cells(b + 1, 1).Select
    ActiveSheet.Paste
    Worksheets("Sheet1").Activate
End If
Next

End Sub
Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Bryan
  • 9
  • 5
  • `Worksheets("Sheet1").Rows(i).Copy` You are copying a row. So how will it copy just the cell? – Siddharth Rout Aug 07 '18 at 05:46
  • Yes, but when I changed the Rows(i) to Cell(i) it shows error 1004 – Bryan Aug 07 '18 at 05:49
  • I can give you the answer but here are couple of links for you. How to [Refer to Cells by Using Index Numbers](https://msdn.microsoft.com/en-us/library/Aa221540), How to [Avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Siddharth Rout Aug 07 '18 at 06:23
  • Once you patiently go through the above links you will be able to spot the error and in fatc can come up with a better code than what you have above ;) Give it a try and if you get stuck simply post here. – Siddharth Rout Aug 07 '18 at 06:24
  • I tried searching many times, it's just that I couldn't understand some codes maybe because I'm new in vba – Bryan Aug 07 '18 at 06:27
  • I also tried Cells(i).Value.Copy but shows runtime error – Bryan Aug 07 '18 at 06:33
  • Well you do not need to search anymore :) I already gave you 2 links above. Go trhough them – Siddharth Rout Aug 07 '18 at 06:48
  • Yes I already read that It's just that I will not able to apply it in my codes since I didn't understand how to do/used it(just a newbie trying to learn more). – Bryan Aug 07 '18 at 06:52
  • Which part from the first link which says `You can use the Cells property to refer to a single cell by using row and column index numbers...` you did not understand? – Siddharth Rout Aug 07 '18 at 06:54
  • Sorry I didn't understand how to apply it in my codes. My codes is only based on the tutorials I've watched – Bryan Aug 07 '18 at 06:58
  • That link also has examples :) – Siddharth Rout Aug 07 '18 at 07:01
  • I'm voting to close this question as off-topic because OP wants a solution in a platter. Lack of effort to even patiently go through what the MSDN link and the SO link says. – Siddharth Rout Aug 07 '18 at 07:02
  • I'm trying it's just that I'm stuck, anyway thanks for the link – Bryan Aug 07 '18 at 07:13

1 Answers1

1

For the beginning you should avoid all those activate stuff. I gets a little confusing

  • I think your problem lies in: Worksheets("Sheet1").Rows(i).Copy

Sub format()

a = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To a
If Worksheets("Sheet1").Rows(i, lColumn).Value Like "*application_id*" Then
    Temp = Sheets("Sheet1").Cells(i,lColumn).value
    b = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
    Sheets("Sheet2").Cells(b + 1, 1) = Temp
End If
Next i

End Sub

Mentos
  • 1,142
  • 2
  • 10
  • 14