Need help with Range

[rbagri]

Hi
Help me to solve the range messup. I want to copy multiple column range from one sheet to another sheet. I tried with

Sheet1.Select
ThisWorkbook.Sheets("Sheet1").Range("D33 50", "E33 :E50", "F33 :F50", "J33 :J50", "K33 :K50", "L33 :L50", "R33 :R50", "S33 :S50").Select
Selection.Copy
Sheet3.Select
ThisWorkbook.Sheets("Sheet3").Range("B" & nr, "B" & nr + 17, "C" & nr, "C" & nr + 17, "D" & nr, "D" & nr + 17, "E" & nr, "E" & nr + 17, "F" & nr, "F" & nr + 17, "G" & nr, "G" & nr + 17, "H" & nr, "H" & nr + 17, "I" & nr, "I" & nr + 17) _
.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Your help is appriciated in advance
Thanks

[Simon Lloyd]

You don't say where you want it copying to as nr isn't shown as an integer here, you would be better off supplying a workbook with your query and a better explanation.

[Bob Phillips]

[vba]

With ThisWorkbook

.Sheets("Sheet1").Range("D33:F50").Copy
.Sheets("Sheet3").Range("B" & nr).PasteSpecial Paste:=xlValues
.Sheets("Sheet1").Range("J33:L50").Copy
.Sheets("Sheet3").Range("E" & nr).PasteSpecial Paste:=xlValues
.Sheets("Sheet1").Range("R33:S50").Copy
.Sheets("Sheet3").Range("H" & nr).PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
End With
[/vba]

[Simon Lloyd]

Bob, full marks on sorting that mudlde out, however, the OP also wants to paste to nr+17, i wasnt sure whether it was supposed to be ("B" & nr & ":" & "B" & nr+17) as a range, well for me at least it wasn't clear.

[Bob Phillips]

Bob, full marks on sorting that mudlde out, however, the OP also wants to paste to nr+17, i wasnt sure whether it was supposed to be ("B" & nr & ":" & "B" & nr+17) as a range, well for me at least it wasn't clear.

He does, but as the source is 18 rows, the paste should cater for that.

[rbagri]

Thanks guys