simple select two columns to last row and transpose to next cell

[Dave01]

Hi,

this seems pretty simple but for some reason I cant get it to work as copy two rows of data to last row and pasting is easy enough, but this time I need to transpose an keep getting an error saying the paste area isnt the same.

Ideally there has to be up to last row, because the length of the data will change, so I cant just do range 1 to 1000 etc.

its simple enough, select data in A1: B & last row, copy and transpose to C1


close code is

Dim LC As Long
LC = Cells(1, Columns.Count).End(xlToLeft).Column
Columns(LC).Copy
Cells(1, LC + 1).PasteSpecial Paste:=xlPasteValues, SkipBlanks:=True, Transpose:=True


And

Last_Row = ActiveSheet.Cells(Rows.Count, "b").End(xlUp).Row
Range("A1:B" & Last_Row).End(xlUp).Copy
Range("C1:C" & Last_Row).PasteSpecial Paste:=xlValues, Transpose:=True



but either it doesnt transpose, or causes an error paste value not the same.


Grateful to anyone who can unjam me .

Many thanks

David.

Link to spreadsheet

Dropbox

www.dropbox.com

 

[Sequoyah]

Hi David,
try this code:

Sub test20210521()

Dim DirArray As Variant
Dim Destination As Range
    Set Destination = Sheets(1).Range("C1")

    DirArray = Sheets(1).Range("A1:B" & Range("A" & Rows.Count).End(xlUp).Row).Value

    Destination.Resize(UBound(DirArray, 2), UBound(DirArray, 1)).Value = Application.Transpose(DirArray)

End Sub

 

[Dave01]

Hi David,
try this code:

Sub test20210521()

Dim DirArray As Variant
Dim Destination As Range
    Set Destination = Sheets(1).Range("C1")

    DirArray = Sheets(1).Range("A1:B" & Range("A" & Rows.Count).End(xlUp).Row).Value

    Destination.Resize(UBound(DirArray, 2), UBound(DirArray, 1)).Value = Application.Transpose(DirArray)

End Sub

Hi Sequoyah,

Perfect, thank you, I knew it was something along those those lines, I just couldnt get the count correct.​

the results where exactly as I wanted, I do have another question if you dont mind, and it would save me time if you could help, as to complete the spreadsheet I need to fill right based on the number of cells from C1 to the end of the last data in the columns, in this case its column CC.

I have got a program somewhere where where I have done this before, but its a case of going through it and stripping it out.

I imagine it is a case like Column count C1 to Col.count
then fill right.

I know its cheeky, but thanks for resolving my original issue.

Dropbox

www.dropbox.com

Dave.

 

[Sequoyah]

Hi Dave01,
thanks for the feedback. I can't open your workbook as it contains links to external sources. Maybe the following code can do the trick, otherwise it is better to open a new thread:

Sub test()

Dim lastcolumn As Long

lastcolumn = Cells(2, Columns.Count).End(xlToLeft).Column

Range("C3").AutoFill Destination:=Range(Cells(3, 3), Cells(3, lastcolumn))

End Sub

 

[Dave01]

Perfect, Thank you,

I changed the code slightly so it fills accross and and all down to populate every cell that needs to be cell, but withought your help I would have still been scratching my help.


Sheets("Payment Forecast").Activate
Range("C3").Activate
Dim lastcolumn, lastRow As Long
ActiveCell.FormulaR1C1 = "=SUMIFS('Raw Data'!C8,'Raw Data'!C14,RC1,'Raw Data'!C9,RC2,'Raw Data'!C6,'Payment Forecast'!R1C)"
lastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Range("c3").AutoFill Destination:=Range("C3:C" & lastRow)
lastcolumn = Cells(2, Columns.Count).End(xlToLeft).Column
Range("C3:C" & lastRow).AutoFill Range("C3", Cells(lastRow, lastcolumn))