How to copy data from Sheet 2 into Sheet 1 last row without data

[ramdevs]

Hello all,

I have some data in Sheet 2 on a spreadsheet from Row A2 to F2. I am trying to copy this with a macro and paste this into sheet 1 after identifying the row on that sheet without data.

I am a baby when it comes to VBA, however trying to learn this since it is so badly needed in my new job.


Below find the macro I have been messing around with. I am getting Debug error in Last Row line.


Thanks,

Ram


Sub Macro1()

'

' Macro1 Macro

Range("A2:F2").Select

Selection.Copy

LastRow = Worksheets("Sheet 1").Range("A65536").End(xlUp).Row

ActiveSheet.Paste


End Sub

 

[sdsurzh]

Hi Ram,


Please try the below code:


Sub Macro1()

'

' Macro1 Macro

Worksheets("Sheet2").Select

Range("A2:F2").Select

Selection.Copy

Worksheets("Sheet1").Select

Range("A65536").Select

Selection.End(xlUp).Select

Selection.Offset(1, 0).Select

ActiveSheet.Paste

End Sub


Thanks,

Suresh Kumar S

 

[ramdevs]

Suresh U r a star!

Thanks,

Ram

 

[Luke M]

Note that you can skip all that selecting and just do the entire macro in one line.

[pre]

Sub Macro1()
Worksheets("Sheet2").Range("A2:F2").Copy Worksheets("Sheet1").Range("A65536").End(xlUp).Offset(1, 0)
End Sub

[/pre]
Should make things a little faster, and you won't see the cursor jumping around.

 

[shrivallabha]

Hi Suresh and Ramdevs,


You can get rid of .Select and Selection parts which are generated by macro recorder.


e.g. following code can be written as:

[pre]

Worksheets("Sheet2").Select
Range("A2:F2").Select
Selection.Copy
to

[code]Worksheets("Sheet2").Range("A2:F2").Copy

If you apply this to whole code then it will look like:

Sub Macro1()
' Macro1 Macro
Sheets("Sheet2&#34.Range("A2:F2&#34.Copy Destination:=Worksheets("Sheet1&#34.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End Sub[/code][/pre]
Advantage:= Improved readability and faster execution!


Edit:= Luke's advice is also on the same lines.

 

[sdsurzh]

Hi Ram,


Thanks for you feedback and I am in learning curve only. You can use Luke & shrivallabha code which is advanced level.


To Luke & shrivallabha,


I have been trained the macro code like step by step (i.e,Selection) and now I have learnt the single line (skipping Selection). Henceforth I will try to follow your method.


Thanks,

Suresh Kumar S