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

 

[John Davis]

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

Not sure I follow you:

Sub buttoncopy()
Sheets("Sheet2").Range("B7:Q7").Copy Sheets("Sheet1").Range("B8")

End Sub

 

[tobyhutton1234]

Hi John,

Thanks for your reply.

I have attached a link to a workbook which will help explain in abit more detail of what i'm trying to do.

Regards

Toby

http://www25.brinkster.com/thutton/info.htm

 

[Trevor G]

Toby you need to add some code like this

Sub Button3_Click()
Sheets("sheet2").Select
Range("B7:q7").Copy
Sheets("Sheet1").Select
Range("B7").Select
Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.PasteSpecial xlPasteAll
End Sub

 

[tobyhutton1234]

Thanks Trevor,

Once you click the button the info from Sheet2 is pasted into Sheet1.

So B7:Q7 is copied into Range B:8 but once its clicked again its copied into B:8 again.

What I need it to do is move onto the next row each time the button is clicked.

Regards

 

[Trevor G]

Can you upload the code you have as the code I uploaded would work.

 

[tobyhutton1234]

Sub Button3_Click()
    Sheets("DftRowSet").Select
        Range("B7:Q7").Copy
    Sheets("New Record").Select
        Range("B8").Select
        
Do Until ActiveCell.Value = ""
    ActiveCell.Offset(1, 0).Select
Loop
    ActiveCell.PasteSpecial xlPasteAll
                
End Sub

 

[Trevor G]

Toby I have just copied the code as is shown and named a couple of sheets everything works fine. As I can't see the data you could try cahnging the .value to .text but I don't think that will make a difference.

 

[tobyhutton1234]

Trevor, that didnt seem to work.

tried endlessly last night but cant seem to get it loop to the next row once click again.

Hmmmmm

http://www25.brinkster.com/thutton/info.htm (Updated)

 

[John Davis]

Trevor, that didnt seem to work.

tried endlessly last night but cant seem to get it loop to the next row once click again.

Hmmmmm

http://www25.brinkster.com/thutton/info.htm (Updated)

How about this modification?

Sub Button3()
Dim lr As Long

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

    Sheets("DftRowSet").Select
        Range("B7:Q7").Copy
    Sheets("New Record").Select
        Range("B7:B" & lr + 1).Select
    ActiveSheet.Paste
End Sub