How to copy data to a different worksheet? Solved/Closed

Question

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


System Configuration: Windows / Firefox 58.0

vcoolio · Answer

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.

vcoolio · Answer

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.

vcoolio · Answer

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.

vcoolio · Answer

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.

vcoolio · Answer

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.

How to copy data to a different worksheet?

5 responses

Similar discussions

Subscribe To Our Newsletter!