PDA

View Full Version : Need help with Range



rbagri
06-21-2009, 10:54 AM
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 :D50", "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
06-21-2009, 11:12 AM
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
06-21-2009, 03:01 PM
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

Simon Lloyd
06-21-2009, 05:03 PM
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
06-22-2009, 12:28 AM
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
06-22-2009, 04:41 AM
Thanks guys