Help with recording data from one sheet to another

Kiwi den

Board Regular
Joined
Feb 17, 2014
Messages
140
Office Version
  1. 365
Platform
  1. Windows
I have a spreadsheet that our salesmen fill in
Sheet 1 From A1 through to K1, this sheet is used to record stock taken.
What I would like is on sheet2 to keep a record for each transaction from sheet 1
This would be in cells A1 – K1, and would list down in the next available row for each new transaction
Not sure is a formula would do this or thru VBA

EG;
Sheet 1
A1= Blue, B1 = KF3PB1, C1 = 04/12/2020, D1 = Ken, E1 = Courier, etc
Then Sheet 2
A1= Sheet 1 A1, B1= Sheet 1 B1, C1 = Sheet 1 C1 etc
Then if sheet 1 is used again then the next available row (A2 – K2) would be filled, leaving the info in Row A

Your help or suggestions would be much appreciated
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,402
Office Version
  1. 2013
Platform
  1. Windows
Would you please tell me the two sheet names.
Like sheet named Alpha if the enter sheet
And sheet named "Bravo" is the sheet to copy too.
Using Sheet1 and Sheet2 is normally not a good way.
And the script will be activated when you enter a value in Column E
Will that work? Assuming you have already entered all your data in column A B C and D
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,402
Office Version
  1. 2013
Platform
  1. Windows
I misread:
Last cell to fill in would be K
So script will run when you enter value in Column K
I'm assuming A to K would now be filled in.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,402
Office Version
  1. 2013
Platform
  1. Windows
Have not heard back from you so put this script in the sheet where you plan to enter the values into. Sheet name is not important. The script will run when you enter any value into column K
But in my script the sheet name of the sheet we want to copy cells into is named "Bravo"
Modify to your needs.

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  12/13/2020  10:16:29 PM  EST
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub

    If Target.Column = 11 Then
        Dim Lastrow As Long
        Lastrow = Sheets("Bravo").Cells(Rows.Count, "K").End(xlUp).Row + 1
        Cells(Target.Row, 1).Resize(, 11).Copy Sheets("Bravo").Cells(Lastrow, 1)
End If
End Sub
 
Solution

Kiwi den

Board Regular
Joined
Feb 17, 2014
Messages
140
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Sorry, time differences, Thanks I will give it a go
 

Watch MrExcel Video

Forum statistics

Threads
1,127,049
Messages
5,622,400
Members
415,894
Latest member
silverhaze

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top