Copy Active Row VBA

[jonnywakey]

Hi I am wondering if anyone could help me with generating VBA for the following.

I have a spreadsheet which has countless entries in it, what I want to be able to do is when a Cmd button is pressed on a userform it copies the data from the active row and pastes it to another excel spreadsheet on a networked drive.

The sheet which holds the data is called "C.I.F. Register", I currently have programmed a cmd button at the end of each row to do the above but as hundreds of entries will be made (via userform) the vba would take ages.

The cmd button code that I currently use is:

Sub gencif()
'
' gencif Macro
' Macro recorded 17/03/2011 by John Wakefield
'
'
Sheets("C.I.F REGISTER").Select
ActiveWindow.SmallScroll Down:=-9
Range("A5:X5").Select
Selection.Copy
Workbooks.Open Filename:= _
"W:\DSW\DSW Hub\DSW Health & Safety\16 - TMF CIF Form.xls"
Sheets("Paste Sheet").Select
Range("A2").Select
ActiveSheet.Paste
Sheets("CIF FORM").Select

ActiveWorkbook.SaveAs "W:\DSW\DSW Hub\DSW TMF CIF\" & Range("F6").Value & ".xls"
End Sub

Thanks in advance for any assistance.

Regards

John

 

[Jeffrey Smith]

Please put your code surrounded by CODE statements. Use the # icon.

Are you sure you want to open a workbook, paste a line of data to the Cell A2, save it, and then start all over again?

Seems like you would just open it once, run the macro on as many rows as you need, and then save it yourself. And you that you would like the macro to paste it at the end of a column of data, not to Cell A2 every time.

Jeff