Copy and Paste Multiple Cells

[pgavin13]

I could really use some assistance to complete this macro.

So far, I have a code that will review the value of a cell and insert a row on a following sheet.

Sub AddRows()
Dim i As Long
Dim j As Long
Dim k As Long
Dim l As Long
For i = 1 To 200
If Sheets("S&S Build").Cells(i, 28) = 4 Then Sheets("S&S").Cells(23, 1).EntireRow.Insert
Next i
For j = 1 To 200
If Sheets("S&S Build").Cells(j, 28) = 3 Then Sheets("S&S").Cells(18, 1).EntireRow.Insert
Next j
For k = 1 To 200
If Sheets("S&S Build").Cells(k, 28) = 2 Then Sheets("S&S").Cells(13, 1).EntireRow.Insert
Next k
For l = 1 To 200
If Sheets("S&S Build").Cells(l, 28) = 1 Then Sheets("S&S").Cells(8, 1).EntireRow.Insert
Next l
Sheets("S&S").Select
End Sub

This works well to give me a new row on "S&S", but now I need to fill the first 10 columns of that row with cells from "S&S Build" sheet. The cells I need are in columns 3, 8, 10, 13, 14, 20, 22, 23, 24, and 25. The row should correspond to the value of the variable that was looking up information in the previous code. i.e. if a 2 is located in cell (5, 28), then the code should select the cells, (5, 3), (5, 8), (5, 10) and so on.

I think my first issue is that I don't know how to do multiple events after "Then" is issued. Is there some sort of "And" command.

Also, I don't know how to select multiple cells. I've got the range thing figured out, as well as a union of ranges, but I don't know how to do a union of cells. I hope that makes sense.

Any help would be appreciated.

Thanks.

 

[doofusboy]

I don't know how to do multiple events after "Then" is issued.

For i = 1 To 200
If Sheets("S&S Build").Cells(i, 28) = 4 Then 
     ' just start commands on next line
     Sheets("S&S").Cells(23, 1).EntireRow.Insert
     ' add line for another command right here
Next i

 

[pgavin13]

I've tried that without success. The issue must then be with the selection of multiple cells. Any ideas for that?

 

[doofusboy]

.

 

[pgavin13]

Is there a way to select multiple cells?

 

[doofusboy]

My appologies....forgot one VER imortant line [in red]:

For i = 1 To 200
If Sheets("S&S Build").Cells(i, 28) = 4 Then 
     ' just start commands on next line
     Sheets("S&S").Cells(23, 1).EntireRow.Insert
     ' add line for another command right here
[COLOR=red][B]End If[/B][/COLOR]
Next i

 

[pgavin13]

That gave me an "End If without a block" error.

 

[doofusboy]

Please post exact code causing you that error.

 

[pgavin13]

Sub AddRows()
Dim i As Long
Dim j As Long
Dim k As Long
Dim l As Long
For i = 1 To 200
If Sheets("S&S Build").Cells(i, 28) = 4 Then Sheets("S&S").Cells(23, 1).EntireRow.Insert
End If
Next i
For j = 1 To 200
If Sheets("S&S Build").Cells(j, 28) = 3 Then Sheets("S&S").Cells(18, 1).EntireRow.Insert
End If
Next j
For k = 1 To 200
If Sheets("S&S Build").Cells(k, 28) = 2 Then Sheets("S&S").Cells(13, 1).EntireRow.Insert
Sheets("S&S Build").Cells(k, 3).Copy
Sheets("S&S").Cells(1, 13).Paste
End If
Next k
For l = 1 To 200
If Sheets("S&S Build").Cells(l, 28) = 1 Then Sheets("S&S").Cells(8, 1).EntireRow.Insert
End If
Next l
Sheets("S&S").Select
End Sub

 

[doofusboy]

If Sheets("S&S Build").Cells(k, 28) = 2 Then Sheets("S&S").Cells(13, 1).EntireRow.Insert
Sheets("S&S Build").Cells(k, 3).Copy
Sheets("S&S").Cells(1, 13).Paste
End If

should be:

If Sheets("S&S Build").Cells(k, 28) = 2 Then 
Sheets("S&S").Cells(13, 1).EntireRow.Insert
Sheets("S&S Build").Cells(k, 3).Copy
Sheets("S&S").Cells(1, 13).Paste
End If