Simpler code to Copy Paste every time row 2 to last empty row

[dinesh shah]

Dear Friends,
Greetings!

While entering data every time I am required to copy contents of Row 2 and paste them in the last empty
row in the same sheet. As I am still learning VBA coding to simplify my works, I have recorded the following
Macro which works. But I think it can be made more simpler by reducing the number of lines. Can someone
help me by showing how?

Sub PostT1toT1()

Sheets("T1").Select
Range("2:2").Select
Selection.Copy
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").EntireRow.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False

End Sub

Thanks in advance,

Dinesh Shah

 

[puru.sve]

Sub PostT1toT1()    With Sheets("T1")
        lr = .Range("A6500").End(xlUp).Row + 1
        .Range("2:2").Copy .Range("A" & lr)
        Application.CutCopyMode = False
    End With
End Sub

 

[dinesh shah]

Sub PostT1toT1()    With Sheets("T1")
        lr = .Range("A6500").End(xlUp).Row + 1
        .Range("2:2").Copy .Range("A" & lr)
        Application.CutCopyMode = False
    End With
End Sub

Dear Mr.Purushottam

Thanks for your prompt reply.

But in the above Macro, paste special is missing.

Can you help me?

Regard,

 

[puru.sve]

You dont need paste special .. there are two way -

Sub PostT1toT1()
'1nd way 
With Sheets("sheeT1")
        lr = .Range("A6500").End(xlUp).Row + 1
        .Range("1:1").Copy .Range("A" & lr)
        Application.CutCopyMode = False
    End With
End Sub




'2nd way 
Sub PostT1toT1()
With Sheets("sheeT1")
        lr = .Range("A6500").End(xlUp).Row + 1
        .Range("1:1").Copy
        .Range("A" & lr).PasteSpecial xlPasteAll
        Application.CutCopyMode = False
    End With
End Sub

 

[dinesh shah]

Dear Mr.Purushottam

Thanks a lot. It works.
Paste Special was required because Row 2 contains formulas.
And when I copy paste I need only the values.

Thanks once again.

 

[MARK858]

Just want to point out a couple of things with the code posted .Range("A6500") will only find the last row up until row 6500 which I assume was intended to be the last row in Excel up to Excel 2003 which was 65536 not 6500 (I think puru.sve probably wanted to add a 0 to make it 65000).

Even 65536 isn't much good after 2007 as Excel 2007 and later has 1048576 rows and so you normally use rows.count these days which caters for both scenarios.

As the OP was trying to paste as values and so did need either pastespecial xlvalues or a value to value copy (copy isn't the right word here but will do) below are a couple of alternatives

PasteSpecial code

Sub PostT1toT1()
    Dim lr As Long
    With Sheets("T1")
        lr = .Range("A" & Rows.Count).End(xlUp).Row + 1
        .Range("1:1").Copy
        .Range("A" & lr).PasteSpecial xlValues
        Application.CutCopyMode = False
    End With
End Sub

Value to Value code

Sub PostT1toT1B()
    With Sheets("T1").Range("1:1")
        .Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(.Rows.Count, .Columns.Count).Value = .Value

    End With
End Sub

 

[MARK858]

That'll teach me copying part of someone else's code...
change Range("1:1") in both of the codes I posted to Range("2:2")

 

[puru.sve]

Hi Mark,

Thanks for clarifying each points to Dinesh...

He is beginner I guess, so I made it simpler... so that once he used to then can explore things on his own way...

Btw Again thanks..

 

[dinesh shah]

That'll teach me copying part of someone else's code...
change Range("1:1") in both of the codes I posted to Range("2:2")

Dear Mr.Mark

Thanks for your help. As a beginner I'm surprised to see there are so many methods in automating routine activities in Excel using VBA code.

With timely help from persons like you and Mr.Purushottam in automation I am now enjoying the monotonous tasks.

Thanks once again to both of you.

Dinesh Shah