Transpose to the next available line

**gherzberg** · 08-23-2012, 12:13 PM

I have to create an automatic push from one workbook as a column to another workbook as a row. The second book is our overall blotter so the data needs to go to the bottom of the sheet. I have tried several different methods to get it to paste to the next available row, but always come up with errors. anyone have any ideas.

Please Login or Register  to view this content.

**MarvinP** · 08-23-2012, 12:21 PM

Hi gherzberg,

After you copy your column into the windows buffer and open and select your other workbook ("Blotter.xlsm") you need to learn this type of code.

Dim LastRow as double
LastRow = Cells(Rows.Count, "A").End(xlUp).Row + 1
Cells(LastRow, "A").Select

The above code will select the first blank cell in Column A. Then do your paste transpose.

Hope this helps.

For other ways see http://www.vbautomation.110mb.com/VB..._worksheet.htm

**gherzberg** · 08-23-2012, 01:15 PM

Thank you for your help, and please forgive my ignorance, but I inserted the code where you explained, and the code is showing as "this object doesn't support this property or method." any suggestion?

Please Login or Register  to view this content.

**Cutter** · 08-23-2012, 02:43 PM

@ gherzberg

Welcome to the forum.

Please notice that code tags have been added to your post(s). The forum rules require them so please keep that in mind and add them yourself whenever showing code in any of your future posts. To see instructions for applying them, click on the Forum Rules button at top of the page and read Rule #3.
Thanks.

**MarvinP** · 08-23-2012, 03:04 PM

OK -

Open both workbooks and record a macro from the first to do what you want to do.
Stop recording the macro after you paste transpose.
Open the module that recorded the macro and look at it.
In the section that selects the cell to paste into... That is where you want to put my example code.

We'll make a programmer out of you ....