Pasting in the Next Blank Row

[BobPierre]

Hi Guys.
I want to copy the last row with data and paste in the last empty row using macro but I am finding it difficult.
This is what I have been trying since but it is not working.
Sub test()
Dim ws As Worksheet
Set source = Sheets("Total")


Dim LC As Long
With ThisWorkbook.Sheets("Total")
LC = Cells(1, Columns.Count).End(xlToLeft).Column
Columns(LC).Copy
Cells(1, LC + 1).PasteSpecial Paste:=xlPasteValues
End With


End Sub

My data looks like this:

A B
[TABLE="width: 447"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]

[/TD]
[TD]West[/TD]
[TD="align: right"]58[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]East[/TD]
[TD="align: right"]68[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]North[/TD]
[TD="align: right"]4[/TD]
[/TR]
</tbody>[/TABLE]


Secondly, I want it to be creating a sheet, insert timestamp as the sheet's name and then paste my data in the sheet each time I run it.

Thank you all in advance

 

[footoo]

In what way is it not working?

Sub test()
Dim source As Worksheet, LC As Long
Set source = Sheets("Total")
With source
    LC = .Cells(1, Columns.Count).End(xlToLeft).Column
    .Columns(LC).Copy
    .Cells(1, LC + 1).PasteSpecial Paste:=xlPasteValues
End With
End Sub

Or more simply :

Sub test()
With Sheets("Total").Cells(1, Columns.Count).End(xlToLeft).EntireColumn
    .Offset(0, 1).Value = .Value
End With
End Sub

Not clear what you want here :

Secondly, I want it to be creating a sheet, insert timestamp as the sheet's name and then paste my data in the sheet each time I run it.

 

[BobPierre]

Thank you, Footo. What u did is copying from column B and pasting it in column C and so on.
But that is not what I want it to be copying from Row 3 and pasting it in the next empty row below it, and so on.



For the second question, I have a Pivot table that I want to be running and each time I run it, I want it to be creating a new sheet, insert timestamp as the sheet's name and then paste my data in the sheet each time I run it.

Thank you once again.

 

[footoo]

[COLOR=#333333]What u did is copying from column B and pasting it in column C and so on.[/COLOR]

That's what the code you posted indicated you wanted to do.

[COLOR=#333333]But that is not what I want it to be copying from Row 3 and pasting it in the next empty row below it, and so on.[/COLOR]

Do you mean that you just want to duplicate the last row immediately below it? (What does "and so on" mean?)
If so, and there is nothing below the last data row in column A, then :

With Cells(Rows.Count, "A").End(xlUp)(2).EntireRow
    .Value = .Offset(-1, 0).Value
End With

If that's not what you want, post some before and after data.

For the second question, I have a Pivot table that I want to be running and each time I run it, I want it to be creating a new sheet, insert timestamp as the sheet's name and then paste my data in the sheet each time I run it.

Do you mean you want a macro to refresh the pivot table and create a new sheet?
What format for the sheet name (e.g. yyyy.mm.dd, HH.MM.SS, etc.)?
Paste what data (all? including the pivot table?) and from what sheet?

 

[BobPierre]

Do you mean you want a macro to refresh the pivot table and create a new sheet? Yes
What format for the sheet name (e.g. yyyy.mm.dd, HH.MM.SS, etc.)? hh:mm.

Paste what data (all? including the pivot table?) ALL
and from what sheet? Sheet 1 [/QUOTE]


Thank you

 

[footoo]

Sub BP()
Dim pt As PivotTable, ws As Worksheet
Set ws = Sheets("Sheet1")
For Each pt In ws
    pt.RefreshTable
Next pt
ws.Copy after:=ws
ActiveSheet.Name = Format(Now, "hh.mm")
End Sub

Note : Cannot have a colon ( : ) as part of a worksheet name.