Send one cell a value from another..

Question

I Have a spreadsheet that I got from one of my suppliers, that I need to provide them inventory numbers weekly.

the spreadsheet has three tabs..

1 - the original one from the supplier

2- the data that I get weekly from our system

3- extensive use of vlookups and index and it does some calculations to come up with the needed numbers.

In the original spreadsheets I have placed the command =Value(sheet3!RC) in the corresponding cells to get the value from sheet 3.

My supplier is complaining that the original spreadsheet needs to have numbers not a formula in their cell (they do not seem to care about the extra tabs )

I get around the problem by telling my clerk to copy the original spreadsheet and use "Paste values" into a new spreadsheet and send a copy to the supplier, the problem is despite written instructions and a lot of pictures, this does not happen correctly most weeks and I need to restore the original spreadsheet.

QUESTION:

• Does excel have a way to "push" a value to s specific cell so I can just send the original spreadsheet?
• I am not a VB person but how hard would be to automate this so VB could do the copy and then paste value to a new spreadsheet?

Thanks for any tips or tricks

Answer 1

Here's an example code:

Sub CopyValues()

    Dim sourceSheet As Worksheet

    Dim targetSheet As Worksheet

    Dim sourceRange As Range

    Dim targetRange As Range

    Set sourceSheet = ThisWorkbook.Sheets("Sheet3")

    Set targetSheet = ThisWorkbook.Sheets("Sheet1")

    Set sourceRange = sourceSheet.Range("A1") 'Change this to the cell you want to copy from

    Set targetRange = targetSheet.Range("A1") 'Change this to the corresponding cell in Sheet1

    targetRange.Value = sourceRange.Value

End Sub

To use this macro, open the original spreadsheet and press Alt+F11 to open the VBA editor. Insert a new module and paste the code above. Then, save the file as a macro-enabled workbook (.xlsm) and close the VBA editor.

To run the macro, go back to the original spreadsheet and press Alt+F8 to open the macro dialog. Select the "CopyValues" macro and click "Run". This will copy the value from Sheet3 and paste it as a value in the corresponding cell in Sheet1.