Automatically transfer data from one sheet to another

Solved/Closed

Excel

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

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.

Transfer data from one excel worksheet to another automatically based on criteria
Transfer data from one excel worksheet to another automatically - Guide
Free fire transfer - Guide
If cell contains (multiple text criteria) then return (corresponding text criteria) ✓ - Excel Forum
Based on the values in cells b77:b81, what function can automatically return the value in cell c77? ✓ - Excel Forum
Vba code to copy data from one sheet to another based on criteria ✓ - Excel Forum

3 responses

Answer 1 / 3

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

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.microsoft.com/en-us/office/move-or-copy-worksheets-or-worksheet-data-47207967-bbb2-4e95-9b5c-3c174aa69328?ui=en-us&rs=en-us&ad=us

BR,
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,
Trowa

Answer 3 / 3

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Oct 29, 2012 at 11:25 AM

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

sunny49er Posts 5 Registration date Thursday October 25, 2012 Status Member Last seen October 30, 2012
Oct 30, 2012 at 10:36 AM

Hi TrowaD

I have provided the link per your request. Thank you for your assistance. I have given a little more information on my response to kobie1121. I hope this helps.

http://speedy.sh/Y6gpG/10-25-12-Line-Item-TN.xls

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Oct 30, 2012 at 11:07 AM

Hi Sunny,

So when you select a row from sheet1 you want to copy:
Line Item No.
Description
Cost

Then paste this information on sheet2.

Well, I can find Description and Cost on sheet2 but where do you want to put the Line Item No.?

Best regards,
Trowa

sunny49er Posts 5 Registration date Thursday October 25, 2012 Status Member Last seen October 30, 2012
Oct 30, 2012 at 11:09 AM

TrowaD

I want to be able to select the description and cost only and have it automatically copy over. What I will be doing is creating an estimate on sheet 2.

thank you for your prompt response.

Sunny49er

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Oct 30, 2012 at 11:24 AM

Hi Sunny,

Manually copy/paste comes very close to using this macro, timewise.

But her you go:

Sub MoveData()
Dim faRow, x As Integer

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

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

End Sub

Select a cell from Sheet1.
Run macro (creating a shortcut will speed things up.).
Description and cost will copied to next sheet.

Best regards,
Trowa

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
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

Similar discussions

Automatically update master worksheet from other worksheets

copy a row from one worksheet to another, based on criteria

Copying data from one Excel sheet to another.

How to copy a data from one sheet to another, when specific criteria is met

Code to move rows into another sheet based on certain values

If a cell contains any text then add a value

Similar discussions

Subscribe To Our Newsletter!