Needinghlp
New Member
- Joined
- Feb 27, 2013
- Messages
- 25
Hi VBA experts,
I have some code which is working, however I'm wondering if there is a more efficient code I could use. I have 2 worksheets (sheet 1 and sheet 2). Column J on sheet 2 has values of Yes or No. If column J has a yes I would like to copy the corresponding value in column A on sheet 2 to column b on sheet 1. I would also like to copy the corresponding values in columns C, D & E on sheet 1 to columns C, D & E on sheet 1. The code I'm using is below. Is there a way I can combine the columns I'm copying from in 1 line of code (and similarly combine the columns I'm pasting to in one row of code) i.e. Worksheets("Sheet2").Cells(i, 1, 3, 4, 5).Copy. This doesn't work - but hopefully it gives an idea of what I'm asking.
Private Sub CommandButton1_Click()
A = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
For i = 3 To A
If Worksheets("Sheet2").Cells(i, 10).Value = "Yes" Then
Worksheets("Sheet2").Cells(i, 1).Copy
Worksheets("Sheet1").Cells(i, 2).Select
ActiveSheet.Paste
Worksheets("Sheet2").Cells(i, 3).Copy
Worksheets("Sheet1").Cells(i, 3).Select
ActiveSheet.Paste
Worksheets("Sheet2").Cells(i, 4).Copy
Worksheets("Sheet1").Cells(i, 4).Select
ActiveSheet.Paste
End If
Next
End Sub
I have some code which is working, however I'm wondering if there is a more efficient code I could use. I have 2 worksheets (sheet 1 and sheet 2). Column J on sheet 2 has values of Yes or No. If column J has a yes I would like to copy the corresponding value in column A on sheet 2 to column b on sheet 1. I would also like to copy the corresponding values in columns C, D & E on sheet 1 to columns C, D & E on sheet 1. The code I'm using is below. Is there a way I can combine the columns I'm copying from in 1 line of code (and similarly combine the columns I'm pasting to in one row of code) i.e. Worksheets("Sheet2").Cells(i, 1, 3, 4, 5).Copy. This doesn't work - but hopefully it gives an idea of what I'm asking.
Private Sub CommandButton1_Click()
A = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
For i = 3 To A
If Worksheets("Sheet2").Cells(i, 10).Value = "Yes" Then
Worksheets("Sheet2").Cells(i, 1).Copy
Worksheets("Sheet1").Cells(i, 2).Select
ActiveSheet.Paste
Worksheets("Sheet2").Cells(i, 3).Copy
Worksheets("Sheet1").Cells(i, 3).Select
ActiveSheet.Paste
Worksheets("Sheet2").Cells(i, 4).Copy
Worksheets("Sheet1").Cells(i, 4).Select
ActiveSheet.Paste
End If
Next
End Sub