Copy a Row and Paste It

[dadrew]

Hi, I need to copy a row from a worksheet and paste it into the next available row on another worksheet in the same workbook. There are a only going to be a maximum of 100 records and the user can do the whole lot at the same time. I need this function so that late comers can be added.

I was going to put a button on every row i.e 100 buttons but Im not sure if thats the way to go.

Help and advise required please.

[TMS]

I'd suggest you just need a routine that will copy the currently selected row and paste that to the second sheet.

Attach that to a button.

[dadrew]

Could you post an example please?

[TMS]

Activecell.Entirerow.Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1,0)

Regards, TMS

[john456852]

Copy.xlsm
here is attachment

[dadrew]

Hi, sorry for taking so long to reply. This is going the right way, I have attached a spreadsheet to help explain what I am after.

I want to select a row, then click the update button, if 'Y' is in Set 1 then it gets put into the first available row (rememebering there are three header rows) in Sheet Set 1, if 'Y' is in Set 2 then it gets put into the first available row in Sheet Set 2, if 'Y' is found to be in Set 1 and Set 2 then it goes into the first available slot on Sheet Set 1 and Sheet Set 2Only the first three colums need to be copied!

[dadrew]

Could really use a bit of help on this if possible, many thanks.

[TMS]

If it takes you two days to respond to an initial reply, why would you expect an immediate response when you do come back?

Attach this to your button:

Option Explicit

Sub CopyRow()

Dim acRow As Long
Dim acCol As Long
Dim acSheet As String
Dim awf As WorksheetFunction: Set awf = WorksheetFunction

acRow = ActiveCell.Row
acCol = awf.Match("Y", Range(Cells(acRow, 1), Cells(acRow, Columns.Count)), 0)
acSheet = Cells(1, acCol)
Range(Cells(acRow, 1), Cells(acRow, 3)).Copy _
    Sheets(acSheet).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

End Sub

Regards, TMS

[dadrew]

I know I set this as solved but I have a related question. the Spreadsheet that I posted was clearly a very simplified version. Could you explain at what point in the code it knows that it should paste the Row 11 (thats Name 10) for example into into sheet 'Set 2'.

Im just trying to understand.

Many thanks

[TMS]

acRow is used to store the Active Cell's row number.

This is used in the next line to determine which column has "Y" in it.

And that, in turn is used to find the sheet number in the top row.

Finally, acRow and acSheet are used in the copy statement.

There's no error handling so it may well fail if it doesn't find a "Y" or the sheet doesn't exist.

Thanks for the rep.

Regards, TMS

[dadrew]

So does that mean that the Sheet Name must be the same as the Colomn Name i.e. Set 1, Set 2 etc?

[TMS]

Yes ... as in your example. How else would you work out which sheet to copy the row to?

If you have some fixed relationship between the column header and the sheet name, you could incorporate that. Or you could have a look up table with column names and sheet names.

I'm not sure why you're asking the question. Do your column headers not match the sheet names? If that's the case, how would you hope to determine which sheet is required to be updated?

Regards, TMS

[dadrew]

Yes they do match, the only difference is, is that the column headers are on Row 5 of the first sheet, I was just trying to understand which number I should change to make it understand that Row 5 held the Colomn headers!

I should add that I have figured this bit out.

What if I had Y in more than one set, this is a potential problem for me.

[TMS]

What if I had Y in more than one set, this is a potential problem for me.

That's a different question and, yes it is. Rather than using MATCH to find the relevant header, you'd need to loop through the columns for the active row and repeat the copy for each occurrence of the flag (Y).

Not that difficult to do, just not the question you asked or provided sample data for

[dadrew]

Many thanks for your help, please see my new thread!