Add copy a new row

[tobyhutton1234]

Hi there,

I'm having a little trouble and looking for some assistance.

Basically on the top of my worksheet I have a button 'Add Record'. Once clicked I want the spreadsheet to copy the contents of lets say B7:Q7 (including borders and cell colours.) from one worksheet (Sheet 2) to another (Sheet 1).

Once the button is clicked it should be placed at B8:Q8 (from Sheet 2 to Sheet 1) and so on.

Does anyone how to go about doing this?

Thanks

 

[tobyhutton1234]

What the code is doing is, is copying and pasting 3 line 3 B7, B6 and B5

Once the button is clicked again, after the line have copied the Debug launches and 'ActiveSheet.Paste' is highlighted yellow.

 

[John Davis]

What the code is doing is, is copying and pasting 3 line 3 B7, B6 and B5

Once the button is clicked again, after the line have copied the Debug launches and 'ActiveSheet.Paste' is highlighted yellow.

Maybe:

Dim lr As Long

lr = Cells(Rows.Count, 2).End(xlUp).Row

    Sheets("DftRowSet").Select
        Range("B7:Q7").Copy
    Sheets("New Record").Select
        Range("B8").Offset(lr + 1).Select
    ActiveSheet.Paste

 

[tobyhutton1234]

No that doesnt seem to be doing it.

It copys into B7 from (DftRowSet) as normal then it's copying from B7 (New Record) to Range B13 (New Record) and then just copies as normal to B7

 

[John Davis]

No that doesnt seem to be doing it.

It copys into B7 from (DftRowSet) as normal then it's copying from B7 (New Record) to Range B13 (New Record) and then just copies as normal to B7

Sorry, I'm still trying to come up with something. However:

If Sheets("New Record") has a value in B6 then

the code below should do it?

Else

I'll keep trying

End if

Dim lr As Long

lr = Cells(Rows.Count, 2).End(xlUp).Row

    Sheets("Sheet1").Select
        Range("B7:Q7").Copy
    Sheets("Sheet2").Select
        Range("B" & lr + 1).Select
    ActiveSheet.Paste

 

[John Davis]

Sorry, I'm still trying to come up with something. However:

If Sheets("New Record") has a value in B6 then

the code below should do it?

Else

I'll keep trying

End if

Dim lr As Long

lr = Cells(Rows.Count, 2).End(xlUp).Row

    Sheets("Sheet1").Select
        Range("B7:Q7").Copy
    Sheets("Sheet2").Select
        Range("B" & lr + 1).Select
    ActiveSheet.Paste

Uh Oh. I missed your sample - in the code above you need to change Sheet1 to Sheet2 and vice versa.

Also, I notice your first code works as well. It works only if you add data and then click the button.