Automatically transfer data from one sheet to another

sunny49er Posts 5 Registration date Thursday October 25, 2012 Status Member Last seen October 30, 2012 - Oct 25, 2012 at 03:36 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Oct 6, 2014 at 11:00 AM

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.

3 responses

SANBOOLY Posts 9 Registration date Wednesday March 26, 2014 Status Member Last seen August 21, 2015
Updated on Mar 24, 2017 at 11:41 AM

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 :

Amr Hamdy
kobe1121 Posts 11 Registration date Wednesday October 10, 2012 Status Member Last seen October 29, 2012
Oct 29, 2012 at 04:26 AM
Would you please provide a sample file so me and the others can better understand your question?
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Mar 27, 2014 at 12:35 PM
Hi Amr Hamdy,

I'm here to help you but I find it hard to understand you.

1) You want to change the cells being copied? or where they are copied to?

2) So sheet1 is called "Shima" and sheet2 is called "Total". And now you want to place the source sheets name "Shima" to the destination sheet "Total". Where would you like to put that data, column D on the first available row?
If so then place the following line before End Sub:
Sheets("Total").Range("D" & faRow).Value = ActiveSheet.Name

3) No problem. Place the following code line before End Sub:
Union(Range("B" & x), Range("D" & x), Range("I" & x)).ClearContents

Let me know if something is unclear.

Best regards,