To start I first locate the row in the first sheet through MsgBox.
.Range(.cells(r, 2), .Cells(r, 2))
Next, I want to copy that row starting at column 5 through 18,
then I want to paste to another sheet in pairs along a diagonal.
For example, col 5 and 6 would paste to row 5 column b and c; col 7 and 8 would post to row 6 column d and e.
I can do this in a very naive way that is not efficient. I could copy col 5 and 6 then paste and repeated the process until done.
I was trying to set up a loop but I could not get it to work correctly. Any help would be greatly appreciated.
Here is a clip of my naive VBA code.
.Range(.Cells(r, 5), .Cells(r, 6)).Copy wsSR.Range("B5:C5")
.Range(.Cells(r, 7), .Cells(r, 8)).Copy wsSR.Range("D6:E6")
.Range(.Cells(r, 9), .Cells(r, 10)).Copy wsSR.Range("F7:G7")
.Range(.Cells(r, 11), .Cells(r, 12)).Copy wsSR.Range("H8:I8")
.Range(.Cells(r, 13), .Cells(r, 14)).Copy wsSR.Range("J9:K9")
.Range(.Cells(r, 15), .Cells(r, 16)).Copy wsSR.Range("L10:M10")
.Range(.Cells(r, 17), .Cells(r, 18)).Copy wsSR.Range("N11:O11")
There must be a way to grab the information once from one worksheet,
then paste into another without going back and forth each time which is slow as molasses.
.Range(.cells(r, 2), .Cells(r, 2))
Next, I want to copy that row starting at column 5 through 18,
then I want to paste to another sheet in pairs along a diagonal.
For example, col 5 and 6 would paste to row 5 column b and c; col 7 and 8 would post to row 6 column d and e.
I can do this in a very naive way that is not efficient. I could copy col 5 and 6 then paste and repeated the process until done.
I was trying to set up a loop but I could not get it to work correctly. Any help would be greatly appreciated.
Here is a clip of my naive VBA code.
.Range(.Cells(r, 5), .Cells(r, 6)).Copy wsSR.Range("B5:C5")
.Range(.Cells(r, 7), .Cells(r, 8)).Copy wsSR.Range("D6:E6")
.Range(.Cells(r, 9), .Cells(r, 10)).Copy wsSR.Range("F7:G7")
.Range(.Cells(r, 11), .Cells(r, 12)).Copy wsSR.Range("H8:I8")
.Range(.Cells(r, 13), .Cells(r, 14)).Copy wsSR.Range("J9:K9")
.Range(.Cells(r, 15), .Cells(r, 16)).Copy wsSR.Range("L10:M10")
.Range(.Cells(r, 17), .Cells(r, 18)).Copy wsSR.Range("N11:O11")
There must be a way to grab the information once from one worksheet,
then paste into another without going back and forth each time which is slow as molasses.