Plz help me with my simple macro !

[Jchicoine]

Hi ! I'm not very good with macros but here the macro I did with the help of the recorder and I would like someone to help me in order to do what I really want !

I have some employees here and I would like to create a macro which will give me the possibility to add an employee during the year. All my employees are in the column A. So I would like a macro which will choose the sheets I want, find the first empty row in the column A, insert another entire empty row below that row and copy the informations in the row of the last employee from the list on the first empty row in column A.

Thanks a lot in advance !

Here's the code I came up :

Sub addemployee()
Sheets(Array("January", "February", "March")).Select
Sheets("January").Activate
Range("A1").End(xlDown).Offset(1, 0).Select
Selection.Insert Shift:=xlDown
Rows("3:3").Select
Selection.AutoFill Destination:=Rows("3:4"), Type:=xlFillDefault
Rows("3:4").Select
Range("A4").Select
Selection.ClearContents
End Sub

 

[Andrew Poulsom]

Not tested, but I think this should work:

Sub addemployee() 
   Sheets(Array("January", "February", "March")).Select 
   Sheets("January").Activate 
   Range("A1").End(xlDown).EntireRow.Select 
   Selection.Copy 
   Selection.Offset(1, 0).Resize(,1).Select 
   Selection.Paste 
   Selection.ClearContents 
End Sub

 

[Jchicoine]

Thanks again for the info !

So far so good it works but I have a bug in the last line. Do you know why ?

Sheets(Array("January")).Select
Sheets("January").Activate
Range("A1").End(xlDown).Offset(1, 0).EntireRow.Select
Selection.Insert Shift:=xlDown
Selection.End(xlUp).EntireRow.Select
Selection.Copy
Selection.Offset(1, 0).Resize(, 1).Select
Selection.Paste

Thanks again sir !

 

[Andrew Poulsom]

I always forget that Paste is a method of the Worksheet object and not of the Range object. Anyway, I have tested this:

Sub Test()
Sheets(Array("January")).Select
Sheets("January").Activate
Range("A1").End(xlDown).EntireRow.Select
Selection.Copy Selection.Offset(1, 0).Resize(, 1)
End Sub

There is no need to insert a row beneath the last used cell, because it is already there. And the Copy method accepts a Destination argument, so I have used that instead of the Paste method.

 

[Jchicoine]

OK ! I'll try it this week end !

Thanks again !