How do I move to the right to next empty cell?

Hi,
I am new to the forum and could really use some help. I have Excel 2010.

What I am trying to do is create a macro that will allow me to copy from one sheet, a set of information, and paste special transpose to another sheet.

Because I am transposing from column to row, I have a lot of columns that eventually have to be copied and transpose to rows. Each column of info is about 12 cells that will transpose to 12 "row" cells.

Here's where I get stuck: The next column of info to copy will start two cells over - same row - from the end of the first transposed row of info copied.

I am such a novice with VB that I am missing a piece of the "formula" that will move the cursor to the right to the "new" cell that will take the new transposed copy.

What I have so far before it goes wrong is:
Sheets("data").Select
Range("a6:a18").Select
Selection.Copy
Sheets("master").Select
ActiveCell.Select
Selection.PasteSpecial Paste=x1PasteAll,blah, blah, blah, finishing the paste special instructions.

now what? this is where i can't seem to get the next instruction correct. I enter:
ActiveWindow.smallscroll toright:=14.......this being the value to get to the next empty cell that I need to enter the new set of info on the same row, but it's not working.

Instead it just continues to copy in the same set of row cells. It doesn't move to the empty cell.

Because this is an ever-growing spreadsheet, I use "ActiveCell.Select" as my starting point. I make sure that I am at this cell when I get ready to start the macro.

Also, I need to go down one and toleft about 84 spots to get to the next row cell to start the copy/paste special again. This one is really confusing me.


Thanks!

Hello aaa1962 and welcome to the forum.


Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here