How to copy data to a different worksheet? [Solved]

hanshylkema 7 Posts Thursday February 1, 2018Registration date February 6, 2018 Last seen - Feb 1, 2018 at 12:22 PM - Latest reply: vcoolio 1169 Posts Thursday July 24, 2014Registration dateModeratorStatus June 10, 2018 Last seen
- Feb 7, 2018 at 06:57 AM
Hello,
I would like to copy data from a specific cell of a worksheet and paste into a specific cell in a different worksheet
Eg from cell b2 of a list (worksheet1) to g5 of worksheet 2, from cell c3 of worksheet 1 to k9 of worksheet 2, from cell b3 of worksheet 1 to g5 of worksheet 3 etc...please help


See more 

Your reply

11 replies

Best answer
vcoolio 1169 Posts Thursday July 24, 2014Registration dateModeratorStatus June 10, 2018 Last seen - Feb 1, 2018 at 06:20 PM
1
Thank you
Hello Hanshylkema,

How many of these individual cells are there with data that you wish to transfer to sheet2?
Could you list them please showing all the cells from sheet1 that need to have their data transferred and to which cells in sheet2.

Or, if its easier, upload a sample of your workbook to a free file sharing site such as ge.tt or Drop Box and then post the link to your file back here. Clearly indicate on the sample which cells are affected.

Cheerio,
vcoolio.

Thank you, vcoolio 1

Something to say? Add comment

CCM has helped 1830 users this month

hanshylkema 7 Posts Thursday February 1, 2018Registration date February 6, 2018 Last seen - Feb 2, 2018 at 04:48 AM
Hello Vcoolio, thanks for your reply, I have made an example sheet as per link. First sheet has the data where in the first row you have the cell where it should end in the data sheet, second the layout of the end result, third sheet has the sequence of the data. So the content of cell F3 of the list of data shuld end in cell C16 of the layout....etc I hope there is a way to get this sorted...? Thanks in advance
https://www.dropbox.com/s/pflrooekl52zlbe/Test%20sheet%2001%20CCM.xlsx?dl=0
hanshylkema 7 Posts Thursday February 1, 2018Registration date February 6, 2018 Last seen - Feb 2, 2018 at 04:49 AM
I forgot, I have 58 data to transfer from the list to the layout..thanks again
regards
Hanshylkema
Respond to vcoolio
Best answer
vcoolio 1169 Posts Thursday July 24, 2014Registration dateModeratorStatus June 10, 2018 Last seen - Updated by vcoolio on 7/02/18 at 06:59 AM
1
Thank you
Hello Hans,

Place the following codes into a standard module and assign the Transferdata sub to a button:-

Sub Transferdata()

Sheet2.[I2].Value = Sheet1.[A3].Value
Sheet2.[E5].Value = Sheet1.[C3].Value
Sheet2.[C7].Value = Sheet1.[D3].Value
Sheet2.[C8].Value = Sheet1.[E3].Value
Sheet2.[C16].Value = Sheet1.[F3].Value
Sheet2.[D16].Value = Sheet1.[G3].Value
Sheet2.[E16].Value = Sheet1.[H3].Value
Sheet2.[C17].Value = Sheet1.[L3].Value
Sheet2.[F16].Value = Sheet1.[I3].Value
Sheet2.[H16].Value = Sheet1.[J3].Value
Sheet2.[I16].Value = Sheet1.[K3].Value
Sheet2.[D17].Value = Sheet1.[M3].Value
Sheet2.[B3].Value = Sheet1.[N3].Value
Sheet2.[A21].Value = Sheet1.[P3].Value
Sheet2.[C21].Value = Sheet1.[Q3].Value
Sheet2.[D22].Value = Sheet1.[R3].Value
Sheet2.[F3].Value = Sheet1.[O3].Value
Sheet2.[A25].Value = Sheet1.[S3].Value
Sheet2.[C25].Value = Sheet1.[T3].Value
Sheet2.[D26].Value = Sheet1.[U3].Value
Sheet2.[B6].Value = Sheet1.[AC3].Value
Sheet2.[F13].Value = Sheet1.[AD3].Value
Sheet2.[K14].Value = Sheet1.[AE3].Value
Sheet2.[C4].Value = Sheet1.[AF3].Value
Sheet2.[E4].Value = Sheet1.[AG3].Value

SaveAstoDesktop

If Sheet2.[I2].Value = Sheet1.[A3].Value Then
Sheet1.[A3].EntireRow.Delete
End If

End Sub

Sub SaveAstoDesktop()

On Error Resume Next
    cOb = CreateObject("wscript.shell").specialfolders("Desktop") & "\" & "All Scheda NT"
    MkDir cOb
NewFN = cOb & "\" & "Scheda" & " " & Sheet1.[A3].Value & "xlsm"
Sheet2.SaveAs NewFN, 52
     
End Sub


The codes:-
- Assume that you will start each project from row 3 and work your way down in order (3,4,5 etc....).
- Transfer the data from the List of Data sheet (Sheet1) to the Scheda NT template. I have only included the cells that you have noted in row1 of the List of Data sheet. You should be able to see for yourself the method used should you wish to add more cells to the code.
- Create a folder on your desktop and store each individual Scheda file in this folder. I added this feature based on your following comment from post #5:-
Each "scheda NT" has to be saved with the "Nr scheda" Nr from column A

This is a permanent record of each Scheda so you can refer back to any of them in future should you wish to modify the specifications for any project.
- Will overwrite previous data with new data in the Scheda template for each project.
- Will delete each row from the List of Data sheet once the transfer and saving of data for each project is done.

I have attached your sample workbook with the code implemented so you can see how it works. Click on the "Transfer Data" button to see it work and then check your desktop for the new folder.
You'll note that I have removed the embedded image which, as you have said, can be added manually for each project.

Here is the link to the sample file:-

http://ge.tt/94sNgTo2

Please test the code in a copy of your workbook first.

I hope that this helps.

Cheerio,
vcoolio.

Thank you, vcoolio 1

Something to say? Add comment

CCM has helped 1830 users this month

Respond to vcoolio
vcoolio 1169 Posts Thursday July 24, 2014Registration dateModeratorStatus June 10, 2018 Last seen - Feb 2, 2018 at 06:19 AM
0
Thank you
Hello Hanshylkema,,

This is somewhat confusing.
I assume that we are dealing with Sheet1(List Of Data) and Sheet2(Scheda NT)- the Technical Production Schedule only.

If I understand correctly:-
- The cell references that you have in row1 of the List of Data sheet are where the corresponding column values (the values directly below the cell reference) are to be placed in the Technical Production Schedule.
- You have 58 of these values to transfer to the Technical Production Schedule.
- The Technical Production Schedule needs to be cleared once a project is completed as should the corresponding data in the List of Data sheet.

For ease of understanding, in the sample workbook, could you please manually place all the cell references in row 1 of the List of Data sheet in, say, red font. Please also manually show the end result (again, in red font) in the Technical Production Schedule.

You have many merged cells in the Technical Production Schedule and merged cells create havoc with VBA codes. You should be able to easily re-format the Technical Production Schedule without merged cells.

In the meantime please supply the sample with the red font as requested above.

Cheerio,
vcoolio.
hanshylkema 7 Posts Thursday February 1, 2018Registration date February 6, 2018 Last seen - Feb 5, 2018 at 03:08 AM
Hi Vcoolio,

Hope you had a good weekend,
I did as you suggested, basically the data from row A of "list of data" goes to cell I2 of "Scheda NT".
In worksheet 3 (contento x cella in sequenza) I added the row number. It is like merging data in reverse...Each "scheda NT" has to be saved with the "Nr scheda" Nr from column A. The "list of data" gets updated as new products are added....All "scheda NT" might get updated in case a component changes that certain "sheda NT" have in common.
Hope I managed to explain...
thanks
regards
Hans
https://www.dropbox.com/s/pflrooekl52zlbe/Test%20sheet%2001%20CCM.xlsx?dl=0
Respond to vcoolio
vcoolio 1169 Posts Thursday July 24, 2014Registration dateModeratorStatus June 10, 2018 Last seen - Feb 5, 2018 at 05:08 AM
0
Thank you
Hello Hans,

Yes. I had a good week-end thank you. I went with the family to the "Festivale" which is held around this time each year in my home town. Lots of food, wine, beer, cider etc. I hope you had a good week-end also.

Thanks for updating the sample. I think that I'm beginning to understand your processes now.

Before I take this any further, I just wish to clarify a few things:-

- The data in the "List of Data" sheet which you have marked in red font needs to be placed in the "Scheda NT" production schedule in the cell that you have indicated in row1 of the "List of Data" sheet. Is this data (the data in red font) the only data that you wish to transfer over to the "Scheda NT" production schedule?
- Will the remaining data be manually input?
- Each row in the "List of Data" sheet (starting from row3) is a different project.
- The embedded image will be different for each project.

 Each "scheda NT" has to be saved with the "Nr scheda" Nr from column A.

So, the "Scheda NT" is a template and you wish to create a new sheet for each project for future reference?

Please confirm the above or otherwise elaborate a little more.

Cheerio,
vcoolio.
hanshylkema 7 Posts Thursday February 1, 2018Registration date February 6, 2018 Last seen - Feb 6, 2018 at 06:04 AM
Hi Vcoolio,
Yes the weekend here was good, thanks.

All data from a row have to be put in the correct cell of the "Sheda NT"
One scheda NT is a datasheet of one specific product. So yes Scheda NT would be a template..
Today I have approx 900 scheda NT to complete.....
There should be the possibility though, to substitute data in a Scheda NT, if a raw material is substituted with another because of availability. This is not a frequent thing to happen..
The image is different for each product as well, but this can be put there manually...
thanks again..
regards

Hans
Respond to vcoolio
vcoolio 1169 Posts Thursday July 24, 2014Registration dateModeratorStatus June 10, 2018 Last seen - Feb 6, 2018 at 06:26 AM
0
Thank you
Hello Hans,

So, a complete row (Columns A - Z) of data from the "List of Data" sheet needs to be placed in the relevant cell in the template (Scheda NT)?

Could you please indicate all cells in row 1 of the "List of Data" sheet just like the few you have already placed there. This will help to reduce any confusion.

Cheerio,
vcoolio.
hanshylkema 7 Posts Thursday February 1, 2018Registration date February 6, 2018 Last seen - Feb 6, 2018 at 07:22 AM
hi Vcoolio,

I've put all data for this example product in red in the list and scheda NT.... other products may have more data,
To make it easier, you could say that in every cell with yellow background in the scheda NT, there should be a data from the list of data, if the cell in the list of data is empty ,the cell in scheda NT remains empty..
The scheda NT is printed and given to the production department to give all relevant info to produce the right product.....

regards

Hans
Respond to vcoolio