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.

 

[pgavin13]

I get an error at

Sheets("S&S").Cells(1, 13).Paste

 

[doofusboy]

What.............is............the..................error?

 

[pgavin13]

Oh...haha...sorry.

I hadn't moved the Then statement to the next line so the need for the end wasn't applicable. Once I moved the lines, it worked perfect. I have the program doing what I want now, but I have a feeling there is an easier way. would you mind taking a look and telling me if I can consolidate some of this code?


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").Select
Range("A13").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("S&S Build").Cells(k, 8).Copy
Sheets("S&S").Select
Range("B13").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("S&S Build").Cells(k, 10).Copy
Sheets("S&S").Select
Range("C13").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("S&S Build").Cells(k, 13).Copy
Sheets("S&S").Select
Range("D13").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("S&S Build").Cells(k, 14).Copy
Sheets("S&S").Select
Range("E13").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("S&S Build").Cells(k, 20).Copy
Sheets("S&S").Select
Range("F13").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("S&S Build").Cells(k, 22).Copy
Sheets("S&S").Select
Range("G13").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("S&S Build").Cells(k, 23).Copy
Sheets("S&S").Select
Range("H13").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("S&S Build").Cells(k, 24).Copy
Sheets("S&S").Select
Range("I13").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("S&S Build").Cells(k, 25).Copy
Sheets("S&S").Select
Range("J13").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
Next k

 

[doofusboy]

Forget the 'selects':

[CODSheets("S&S Build").Cells(k, 3).Copy
Sheets("S&S").Select
Range("A13").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
E][/CODE]

This should do the same thing:

Sheets("S&S Build").Cells(k, 3).Copy
Sheets("S&S").Range("A13").PasteSpecial Paste:=xlPasteValues

 

[pgavin13]

Thank you so much for all the help.

It seems to be working as planned.