Need VBA to select next row (2002)

Topic

New Reply

I’m selecting a vertical block of data in a worksheet, CTRL+C, and then click another tab in the sheet. I then select the first available row (2nd row the first time, 3rd row the next time, etc.) and run a macro I recorded that does a Paste Special, Values, Transpose. I’d like to reduce my steps.
Is it possible, after I’ve selected the block (it’s not always uniform) to have the macro do the copy of the selection, then switch to the other sheet, select the next available row and do the paste special/values/transpose?

I didn’t know how to record this process and don’t know enough VBA to write the code for it.

Thanks in advance.

Reply | Quote

Replies

Is your selected block always one column wide?

Reply | Quote

Yes, it is. I did some searching around the internet and came up with this option that seems to work.

Selection.Copy
Sheets(“horizontal”).Select
Range(“A65536”).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True

Any thoughts or is this just fine in your book?

Thanks,

Kevin

Reply | Quote

The only discrepancy is that this will leave Row 1 blank.

Glad that you found an acceptable answer.

Reply | Quote

I have column headings in row 1, so that is not a problem.

Reply | Quote

You must also ensure that you have something in the first cell of the range you are copying.

Reply | Quote

Selecting and Copy/PasteSpecial is not needed, unless you want to go to the sheet horizontal.

With Selection
Sheets('horizontal").Range("A65536").End(xlup).Offset(1, 0).Resize(1,.Rows.Count).Value = Application.Transpose(.Value)
End With

Reply | Quote

Would you mind explaining what this line does? I am always lost at the Offset.

.Offset(1, 0).Resize(1,.Rows.Count).Value = Application.Transpose(.Value)

Thanks

regards,

Reply | Quote

Mike Rickson (welcome to Woody’s Lounge, Mike!) isn’t online now, so I’ll attempt an explanation

Sheets(‘Horizontal”).Range(“A65536″).End(xlUp) is the last non-blank cell in column A on sheet Horizontal.
.Offset(1, 0) is the cell shifted 1 row down and 0 to the right, i.e. the cell below the last non-blank cell.
.Resize(1, .Rows.Count) refers to a range with that cell as upper left corner, 1 row high and with as many columns as the current selection has rows. Remember, this line is within

With Selection

End With

so .Rows.Count is the number of rows of the selection. In short, Sheets(‘Horizontal”).Range(“A65536”).End(xlUp).Offset(1, 0).Resize(1, .Rows.Count) refers to the range starting below the last non-blank cell in column A on sheet Horizontal that is the size of the selection but transposed (rows and columns exchanged).
The code then transposes the values of the cells in the selection and assigns them to this new range. All this is done without changing the selection, whatever the user had selected remains selected.

Reply | Quote

I’m glad it worked for you.

Reply | Quote

Hans,

Excellent explaination.

thanks

cheers,

Reply | Quote