Automatically transfer data from one sheet to another [Solved/Closed]

sunny49er 5 Posts Thursday October 25, 2012Registration date October 30, 2012 Last seen - Oct 25, 2012 at 03:36 PM - Latest reply: TrowaD 2395 Posts Sunday September 12, 2010Registration date July 17, 2018 Last seen
- Oct 6, 2014 at 11:00 AM
Hello,

I have a workbook with 2 worksheets. Sheet 1 has about 400 line items. I would like to be able to select certain line items on Sheet 1 and have them automatically transfer to Sheet 2. Sheet 2 will be used as a estimate sheet.

Sheet 1 columns: Line Item Number, Description, Unit
Sheet two has a the name of the job, date, location, owner in row format; it also has columns for Description, Quantity, Unit, Cost and Total. The total column will have a formula.

I'm fairly new to these forums and excel itself. Any assistance is greatly appreciated.



See more 

22 replies

Best answer
kobe1121 11 Posts Wednesday October 10, 2012Registration date October 29, 2012 Last seen - Oct 29, 2012 at 04:26 AM
77
Thank you
Would you please provide a sample file so me and the others can better understand your question?

Thank you, kobe1121 77

Something to say? Add comment

CCM has helped 1701 users this month

sunny49er 5 Posts Thursday October 25, 2012Registration date October 30, 2012 Last seen - Oct 30, 2012 at 10:35 AM
kobie1121

Thank you for your reply. I have attached the link for the file I am describing. I'd like to be able to select line items from worksheet 1 (description and cost columns) and have them automatically copy over to worksheet 2. I hope this makes sense.

Thank you.

http://speedy.sh/Y6gpG/10-25-12-Line-Item-TN.xls
SANBOOLY 9 Posts Wednesday March 26, 2014Registration date August 21, 2015 Last seen - Updated by Jeff on 24/03/17 at 11:41 AM
76
Thank you
Dears,

I got this code

Sub Update()
Dim faRow, x As Integer

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

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

End Sub

and it's working perfect after I change the # of Selected cells in row to be 3.
But the Problem is (or you can say the Issue) that I need to:

1- Copy Cells from Range in sheet1 to Paste them in sheet 2 in Row. instead of selected cells in Row.

2- Take the Name of the Sheet1 to use it when I paste in the Sheet2 as additional Cell. (Like Shima as name of sheet1 to be in cell in sheet2)

3- Clear/Delete Contents of the Copied Row in the Sheet1 after Paste in sheet2.

Please Advise with the Result in Easy way. (because my English is not Perfect)

More info here : https://support.office.com/en-us/article/Move-or-copy-worksheets-or-worksheet-data-47207967-bbb2-4e95-9b5c-3c174aa69328?ui=en-US&rs=en-US&ad=US

BR,
Amr Hamdy
SANBOOLY 9 Posts Wednesday March 26, 2014Registration date August 21, 2015 Last seen - Apr 2, 2014 at 12:29 PM
@Trowad
thanks bro. everything is working except #1
what I mean in point #1 is :
how to change the Selection for Count of Cells to:
- Select the Row at all.
or Row for range from A to H (or any end Column)

Thanks for Helping.
TrowaD 2395 Posts Sunday September 12, 2010Registration date July 17, 2018 Last seen - Apr 7, 2014 at 10:39 AM
Hi Amr Hamdy,

To copy entire row use:
Rows(x).copy

To copy row from A to H use:
Range(Cells(x,"A"),Cells(x,"H")).copy

To copy row from A to last column used (based on the first row) use:
Dim lCol As Integer
lCol = Cells(1, Columns.Count).End(xlToLeft).Column
Range(Cells(x, "A"), Cells(x, lCol)).Copy


Best regards,
Trowa
Hi,
I work in a call centre. When my team updates the comments in excel like sale done or voicemail.. I want the email address and the comments to be automatically updated in a master file..please provide me with help...
TrowaD 2395 Posts Sunday September 12, 2010Registration date July 17, 2018 Last seen - Jul 14, 2014 at 09:49 AM
Hi Din,

Try this:
In your master file select the destination cell and type =. Then go to you source file and click on the source cell and hit enter.

Best regards,
Trowa
TrowaD 2395 Posts Sunday September 12, 2010Registration date July 17, 2018 Last seen - Sep 11, 2014 at 11:39 AM
Hi Dwise8,

Would you be so kind to create a separate thread for your query?

Thanks,
Trowa
TrowaD 2395 Posts Sunday September 12, 2010Registration date July 17, 2018 Last seen - Jan 6, 2014 at 10:38 AM
7
Thank you
Hi Murali,

Not entirely sure I know what you want to achieve.

Let's say sheet1
Column A = Description
Column B = Price
Column C = Code number

The following code copies Column A:C to the first available row in Sheet2 when the code number is entered:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("C:C")) Is Nothing Then Exit Sub
Range("A" & Target.Row, "C" & Target.Row).Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End Sub

OR

You want to retrieve the description and price from Sheet1 when you enter the code number in Sheet2. To do that use VLOOKUP.

Let me know if further assistance is desired.

Best regards,
Trowa

TrowaD 2395 Posts Sunday September 12, 2010Registration date July 17, 2018 Last seen - Oct 29, 2012 at 11:25 AM
6
Thank you
Hi Sunny,

You want to manually select rows to automatically transfer them.
Sounds like a copy/paste action to me.

Please explain in more detail when providing sample file (post link after uploading to www.speedyshare.com)

Best regards,
Trowa
Can I get your phone number to set this up?

I am a beginner and need assistance ASAP for work.

Regards
TrowaD 2395 Posts Sunday September 12, 2010Registration date July 17, 2018 Last seen - Jun 12, 2014 at 10:17 AM
You are better off posting your question if you want a speedy response.
hai trowad


my name is noushad
hai Trowad

have doubt in microsoft excel

i have many items in sheet 1. example
my items are

book
pen
pencil
calculator
text book
speaker
mobile cover
e.t.c

i will purchase these items trice in a month

my question is that, detail of a particular item (for eg. book, pen e.tc) should come in sheet 2 automatically while entering in sheet 1.

faithfully waiting for your answer

thank you
TrowaD 2395 Posts Sunday September 12, 2010Registration date July 17, 2018 Last seen - Oct 6, 2014 at 11:00 AM
Hi Noushad,

If you have your items in column A and details in column B of sheet 1 then you can retrieve those details in sheet 2 by using the VLOOKUP formula.

If that is not the case then please specify the location of your source data (items, details) and where you want to place them on sheet 2.

Explain what you want to achieve in greater detail.

Best regards,
Trowa