Question:
Excel sheet with formulas to VBA?
1970-01-01 00:00:00 UTC
Excel sheet with formulas to VBA?
Three answers:
?
2016-10-13 06:15:08 UTC
rather of copying and pasting attempt making use of With Workbooks("Workbook1.xls") .Sheet("Sheet1") .UsedRange Workbooks("Workbook2.xls") .Sheet("Sheet1") .selection( .handle) .formulation = .formulation end With (after removing the areas I inserted to dodge an computerized elipsis.)
pete l
2012-02-17 08:33:41 UTC
No, there is no 'magic button' (unfortunately) so you would have to write it all from scratch, VBA doesn't use formulas or cell references in the same way.
Yamakage
2012-02-17 09:51:16 UTC
First, I think the solution you suggest with hiding Sheet2 and protecting the formulas is the best and easiest solution to implement.



Technically, you can write a VBA program that would take the values in Sheet1 and execute specific formulas on those values and write them to Sheet 1 without requiring a second sheet. The downside of this approach is that a) you need to know VB, and b) the output values will only populate when you run the macro.



It ultimately depends on what you are intending to do. Are you trying to protect this Excel workbook because it is going to a client, or do you just not want coworkers messing with the formulas? One potential option if you simply want to share the information on Sheet1 and is that you could "RECORD" a macro which copies the data from Sheet1 and pastes it into a new Excel workbook as values (so that it will paste the actual value and not the formula). If you are not familiar with this process there are some easy tutorials online.



Basically once you have initialized the Record Macro feature in Excel then you can 1) copy all the values in Sheet1 (I'd recommend using the Select All feature and then Copy), 2) open up a new Excel workbook, and 3) then paste the values (using "paste special" and the values option) into the new Sheet, and, if you have special formatting, you could immediately perform a second paste special using the Formats option.



Basically, as long as you have "RECORD" selected, then every action you take will be automatically recorded into a Macro. Just remember to hit STOP when you are done. Then whenever you wanted to share the data from Sheet1, you could simply run the Macro, and it will automatically create a new Workbook with all the data from Sheet1 in it for you. Then you could do with it what you will. Of course you could even go a step farther and have it automatically save the Excel workbook to a specific location on your harddrive. The record feature doesn't generate clean or well written code, but it is ok for basic tasks.


This content was originally posted on Y! Answers, a Q&A website that shut down in 2021.
Continue reading on narkive:
Loading...