VBA to copy data into Columns

[ScottEmberson]

Hi All

I currently have the code below that copy's data down in rows one after another, but I really want this to now go via columns now and not rows.

Can anyone help?

Thanks

    Sheets("Pivot_Table_Non_Closed_Area").Range("D12:D21").Copy    'Pastes the data from the sheet above in the next avaliable row.
    Sheets("Tracking_Table_Non_Closed_Area").Cells(Row.Count, "C").End(xlToLeft).Offset(1). _
    PasteSpecial Paste:=xlPasteValues, _
    Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Sheets("TC_Count_Tracking_Table").Select
    n = Cells(Column.Count, "B").End(xlUp).Row
    Range(n & "1") = Date
    Range(n & "B") = Time

 

[CalcSux78]

I am a bit confused on where you are placing the item copied. In your code, n appears to return an integer of the last cell, but it appears like you're using it to identify the column of where you are placing the data.

This is what I would use to identify R (last used row) or C (last used Column):

R = Cells(Rows.Count, 3).End(xlUp).Row  'find last row in 3rd column (C)
    'Note:can replace 3 with "C" and yield same results
    
C = Cells(1, Columns.Count).End(xlToLeft).Column 'find last column in row1

As far as taking a column of data and copying to a row on a different worksheet, try out WorksheetFunction.Transpose:

Dim wsTarg As Worksheet
Dim wsDest As Worksheet
Set wsTarg = Sheets("Pivot_Table_Non_Closed_Area")
Set wsDest = Sheets("Tracking_Table_Non_Closed_Area")
With wsDest
    R = .Cells(.Rows.Count, "A").End(xlUp).Row  'first blank row in col A
    .Range("A" & R & ":J" & R) = WorksheetFunction.Transpose(wsTarg.Range("D12:D21"))
End With

 

[ScottEmberson]

Hi Sorry I didn't make it clearer, I want to copy a column of data on worksheet 1 into another worksheet, but every time the macro copy's it I want to to move to the next column along. Is that still possible? I have tried your code you suggested put it just places it into rows rather than columns and it doesn't go down a new row/column each time the code is run.

Data to Copy = E09 to E109
Sheet to have coped data entered into would be start in D9 and continue up the columns

Thanks again

 

[CalcSux78]

That makes more sense! Try:

Dim wsTarg As Worksheet
Dim wsDest As Worksheet
Dim C As Integer
Set wsTarg = Sheets("Pivot_Table_Non_Closed_Area")
Set wsDest = Sheets("Tracking_Table_Non_Closed_Area")
With wsDest
    'C is first blank column in Row 1
    C = .Cells(1, .Columns.Count).End(xlToLeft).Column + 1
    wsTarg.Range("D12:D21").Copy .Cells(1, C) 'send to row1, first empty column
End With

 

[ScottEmberson]

Thank you for this code, sorry for the late response. Just one thing I can not seem to get it to print the date and time at the same time the data pasted onto the new sheet, as my current code is.