Select varying quantity of rows, press macro button to send selection to new workbook

Hello,

I'm tempted to ask for the entire solution but I'm stuck at the beginning and want to have a go at the rest myself first.

I'm not sure how to write the code that would allow for me to select any number of rows and with a press of the macro button have that info transferred into a new workbook. It must be with a variable declaration but I'm not sure what the command and syntax is to assign the pre-selected rows to that variable.

Your help is much appreciated.

TV

Hi TV

The code will not select the rows for you , but once you have selected some, they will transfer to another workbook.

I tried to do it with a variable declaration, but failed miserably, so I do hope you are not too disappointed.

They say that the first part of a problem is always the worst, so now you have got that out of the way, you should be able to finish quite quickly now.

Regards
Alastair

Hi aydeegee

That works really well. Thanks for taking the time for this. I appreciate it.

I was working on some code and made a bit of headway so I will post it also. It only transfers one row at a time to a lower portion of the same document but also generates a time stamp. The time stamp also prevents the same row from being sent twice.

Sub CopyMoveRow()
'
' Copy row and move to new doc
'
    ActiveCell.EntireRow.Select
    Selection.Copy
    If Cells(Selection.Row, 9) <> 0 Then
        MsgBox ("Already submitted.")
        End
    End If
    Cells(Selection.Row, 9).Value = Now()
    Rows("20:20").Select
    Selection.Insert Shift:=xlDown
    
End Sub

Your method adds to the end of the list in the destination document while I was thinking of inserting in Row 2 on the destination document. Is there a best practice for this if order of data does not matter?

Also, is it good practice to add a line as you have in case of error to goto...? I have seen this used before and I'm wondering if it's used to account for errors not foreseen?

Thanks again.

Cheers,

TV

Hi TV

I do not set myself up as arbiter of best practice - in so many cases the answer is "it depends"

Where you copy to is a matter of preference, and as long as you have a system to ensure that data is not overwritten, I cannot see that it matters where you put it.

Error trapping is a useful way to ensure that the VBA is robust. Had I not written the error trap, then if you had tried to run it without the Dest file open, the VBA would have stopped with an error code that would have meant nothing to most people. With the error trap, you know why the VBA has stopped.

Regards
Alastair