Automatically data to another sheet by adding new rows [Solved/Closed]

Question

Previous Topic Next Topic

Automatically data to another sheet by adding new rows [Solved/Closed]

Report

NazCarr

Posts: 13
Registration date: Monday March 25, 2019
Status: Member
Last seen: January 26, 2021

- Mar 25, 2019 at 10:03 AM
TrowaD

Posts: 2757
Registration date: Sunday September 12, 2010
Status: Moderator
Last seen: June 8, 2021

- Mar 26, 2019 at 12:33 PM

Hello,
I have created a "request form" - the form is not designed in VBA - it is simply a spreadsheet using Column B, only, and each row has either a "label" or a field to enter data: For example:
<Row 4> Project Code:
<Row 5> <Enter detail here>
<Row 6> Reason for Change>
<Row 7> <Enter reason here>
<Row 8> Deployment Date
<Row 9> <Date is entered here>

This spreadsheet is called "Deployment Request" (Source of data) (A)

The second spreadsheet (Destination for data captured) is called Request Report (B)
This spreadsheet is a lfat spreadsheet with columns mirroring the "labels"
Column A: Project A
Column B: Reason for Change
Column C: Deployment Date

I have also inserted a Command Button from the Form Control and associated it with the macro.

I would like people to fill in the form with their details and click on the "Submit" button.
This then transfers the required Rows from A to B

For every new request, a new row is added on B.

I really hope I make sense.

I have been reading through your previous posts / questions and tried using this:
Sub Update()
Dim faRow, x As Integer

'x = ActiveCell.Row
Rows(x).Copy

faRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row

Range("B" & x).Copy Sheets("Sheet2").Range("A" & faRow)
Range("D" & x).Copy Sheets("Sheet2").Range("B" & faRow)
Range("I" & x).Copy Sheets("Sheet2").Range("C" & faRow)

Sheets("Sheet2").Range("D" & faRow).Value = ActiveSheet.Name

End Sub

Automatically data to another sheet by adding new rows
Macro to copy data from one sheet to another last row ✓ - Forum - Excel
Copy a row from one worksheet to another, based on criteria ✓ - Forum - Excel
Automatically transfer data from one sheet to another in excel - How-To - Excel
How To Auto Transfer Data from Master to Sub Sheets in Excel - How-To - Excel
Excel copy data from one sheet to another automatically ✓ - Forum - Excel

2 replies

Answer 2 / 2

Blocked Profile
Mar 25, 2019 at 10:33 AM

The operations you are asking about, is a database. Spreadsheets are for complex calculations, not storing data.

There is no coding involved with a database. Just entry form, and reports.

Report

NazCarr

Posts: 13
Registration date: Monday March 25, 2019
Status: Member
Last seen: January 26, 2021

Mar 25, 2019 at 10:50 AM

Thanks for getting back to me. I am aware that we are able to copy data from one sheet to another and I would like to use the same concept. Only difference is: I need to somehow tell Excel to insert the data into the next row....

If we are able to copy data from sections into another sheet, by the use of a command button, then I think there could be a way out for me?

Report

Blocked Profile
Updated on Mar 25, 2019 at 10:56 AM

Well I am jist saying, the learning curve for making forms and reports in Access, is way less sharp than VBA.

Row.select
Selection.copy

Better yet, record a macro. Edit the macro to be dynamic by placing variables instead of the hard code the macro writes!

Report

NazCarr

Posts: 13
Registration date: Monday March 25, 2019
Status: Member
Last seen: January 26, 2021

Mar 25, 2019 at 11:00 AM

I am limited to the tools I can use.... I can only use and make the best of what I have - unfortunately :)

I have to admit that I am learning as I go... so any help would be greatly appreciated.

Ask a question

Thanks for getting back to me. I am aware that we are able to copy data from one sheet to another and I would like to use the same concept. Only difference is: I need to somehow tell Excel to insert the data into the next row....

If we are able to copy data from sections into another sheet, by the use of a command button, then I think there could be a way out for me?
Well I am jist saying, the learning curve for making forms and reports in Access, is way less sharp than VBA.

Row.select
Selection.copy

Better yet, record a macro. Edit the macro to be dynamic by placing variables instead of the hard code the macro writes!
I am limited to the tools I can use.... I can only use and make the best of what I have - unfortunately :)

I have to admit that I am learning as I go... so any help would be greatly appreciated.

TrowaD · Accepted Answer · 2019-03-25T12:55:04-0400

Answer 1 / 2

TrowaD

Posts: 2757
Registration date: Sunday September 12, 2010
Status: Moderator
Last seen: June 8, 2021

462
Mar 25, 2019 at 12:55 PM

Hi Nazcarr,

The code below will only run when all fields contain data. Delete code lines 4-7 if you don't want this.
Also, column D on the second sheet will get stamped with todays date. Delete code line 14 if you don't want this.

Sub RunMe()
Dim lRow As Integer

If Range("B5") = vbNullString Or Range("B7") = vbNullString Or Range("B9") = vbNullString Then
    MsgBox "Please enter all fields before submitting", vbCritical, "Incomplete data"
    Exit Sub
End If

Range("B5,B7,B9").Copy

With Sheets("Request Report")
    lRow = .Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
    .Range("A" & lRow).PasteSpecial Transpose:=True
    .Range("D" & lRow).Value = Date
End With

Application.CutCopyMode = False
End Sub

Best regards,
Trowa

Report

NazCarr

Posts: 13
Registration date: Monday March 25, 2019
Status: Member
Last seen: January 26, 2021

Mar 26, 2019 at 04:12 AM

Hi Trowa,
Thank you so much for your help.

I had to remove the "full stop" before Range to make this work
lRow = .Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row

1. The data is being copied across
2. The form is recognising blank fields.

One pending issue - if you could kindly help me out...

Deployment Request - A
Report Request - B

When I add another request (even after closing down the file and re-opening the file) the previous request in B gets over-written - so there is always only one row in B.

Any chance we could accumulate the requests on the Request Report (B) - get Excel to add the new request to the next available row in Request Report?

Kind Regards,
Naz

Report

NazCarr

Posts: 13
Registration date: Monday March 25, 2019
Status: Member
Last seen: January 26, 2021

Mar 26, 2019 at 05:51 AM

I added + 1 at the end of

lRow = .Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row

So it's now
With Sheets("Request Report")
lRow = Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row + 1
.Range("A" & lRow).PasteSpecial Transpose:=True
.Range("D" & lRow).Value = Date
End With

It adds a second row

But third time round, it replaces the second row ....

Report

NazCarr

Posts: 13
Registration date: Monday March 25, 2019
Status: Member
Last seen: January 26, 2021

Mar 26, 2019 at 06:33 AM

Hi Trowa,
I managed to fix the problem .... :)

Sub RunMe()

If Range("B5") = vbNullString Or Range("B7") = vbNullString Or Range("B9") = vbNullString Then
MsgBox "Please enter all fields before submitting", vbCritical, "Incomplete data"
Exit Sub
End If

Range("B5,B7,B9").Copy

With ThisWorkbook.Sheets("Request Report")
nextRow = ThisWorkbook.Sheets("Request Report").Cells(Rows.Count, 1).End(xlUp).Row + 1
.Range("A" & nextRow).PasteSpecial Transpose:=True
.Range("D" & nextRow).Value = Date
End With

Application.CutCopyMode = False
End Sub

Using "With ThisWorkbook.Sheets"

It's all working now.

Thank you so much for all your help. You practically got me to a position where it was working and all I had to do was tweak it every so slightly.

A huge thank you.

Report

TrowaD

Posts: 2757
Registration date: Sunday September 12, 2010
Status: Moderator
Last seen: June 8, 2021

462
Mar 26, 2019 at 12:33 PM

You are welcome NazCarr, glad I could help.

Automatically data to another sheet by adding new rows [Solved/Closed]

2 replies

Your reply

Subscribe To Our Newsletter!