Needinghlp
New Member
- Joined
- Feb 27, 2013
- Messages
- 25
Hi VBA experts,
I have an issue I just can't resolve. I have a worksheet (sheet2) which contains values of Yes, No or Unknown in column J. If there is a value of Yes or Unknown, I want to copy certain cells from that worksheet to certain cells in another worksheet (sheet1). On sheet1, column A contains static numbering 1 to 1000 but the rest of the columns will be blank.
Below is the code I'm using which for the most part is working - but... when information is copied into sheet 1, I end up with empty rows between rows of data. Does anyone know how I can fix this code so that I don't get the empty rows.
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" Or Worksheets("Sheet2").Cells(i, 10).Value = "Unknown" Then
Worksheets("Sheet2").Cells(i, 1).Copy
Worksheets("Sheet1").Activate
b = Worksheets("Sheet1").Cells(Rows.Count, 2).End(xlUp).Row
Worksheets("Sheet1").Cells(i, 2).Select
Selection.PasteSpecial Paste:=xlPasteValues
Worksheets("Sheet2").Cells(i, 3).Copy
Worksheets("Sheet1").Activate
b = Worksheets("Sheet1").Cells(Rows.Count, 2).End(xlUp).Row
Worksheets("Sheet1").Cells(i, 3).Select
Selection.PasteSpecial Paste:=xlPasteValues
Worksheets("Sheet2").Cells(i, 4).Copy
Worksheets("Sheet1").Activate
b = Worksheets("Sheet1").Cells(Rows.Count, 2).End(xlUp).Row
Worksheets("Sheet1").Cells(i, 4).Select
Selection.PasteSpecial Paste:=xlPasteValues
Worksheets("Sheet2").Cells(i, 5).Copy
Worksheets("Sheet1").Activate
b = Worksheets("Sheet1").Cells(Rows.Count, 2).End(xlUp).Row
Worksheets("Sheet1").Cells(i, 5).Select
Selection.PasteSpecial Paste:=xlPasteValues
Worksheets("Sheet2").Cells(i, 22).Copy
Worksheets("Sheet1").Activate
b = Worksheets("Sheet1").Cells(Rows.Count, 2).End(xlUp).Row
Worksheets("Sheet1").Cells(i, 14).Select
Selection.PasteSpecial Paste:=xlPasteValues
End If
Next
End Sub
I have an issue I just can't resolve. I have a worksheet (sheet2) which contains values of Yes, No or Unknown in column J. If there is a value of Yes or Unknown, I want to copy certain cells from that worksheet to certain cells in another worksheet (sheet1). On sheet1, column A contains static numbering 1 to 1000 but the rest of the columns will be blank.
Below is the code I'm using which for the most part is working - but... when information is copied into sheet 1, I end up with empty rows between rows of data. Does anyone know how I can fix this code so that I don't get the empty rows.
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" Or Worksheets("Sheet2").Cells(i, 10).Value = "Unknown" Then
Worksheets("Sheet2").Cells(i, 1).Copy
Worksheets("Sheet1").Activate
b = Worksheets("Sheet1").Cells(Rows.Count, 2).End(xlUp).Row
Worksheets("Sheet1").Cells(i, 2).Select
Selection.PasteSpecial Paste:=xlPasteValues
Worksheets("Sheet2").Cells(i, 3).Copy
Worksheets("Sheet1").Activate
b = Worksheets("Sheet1").Cells(Rows.Count, 2).End(xlUp).Row
Worksheets("Sheet1").Cells(i, 3).Select
Selection.PasteSpecial Paste:=xlPasteValues
Worksheets("Sheet2").Cells(i, 4).Copy
Worksheets("Sheet1").Activate
b = Worksheets("Sheet1").Cells(Rows.Count, 2).End(xlUp).Row
Worksheets("Sheet1").Cells(i, 4).Select
Selection.PasteSpecial Paste:=xlPasteValues
Worksheets("Sheet2").Cells(i, 5).Copy
Worksheets("Sheet1").Activate
b = Worksheets("Sheet1").Cells(Rows.Count, 2).End(xlUp).Row
Worksheets("Sheet1").Cells(i, 5).Select
Selection.PasteSpecial Paste:=xlPasteValues
Worksheets("Sheet2").Cells(i, 22).Copy
Worksheets("Sheet1").Activate
b = Worksheets("Sheet1").Cells(Rows.Count, 2).End(xlUp).Row
Worksheets("Sheet1").Cells(i, 14).Select
Selection.PasteSpecial Paste:=xlPasteValues
End If
Next
End Sub