#1
|
|||
|
|||
Help Needed with Macro to Change Formulas to Text Using Ranges
Hi all,
The attached file has various tabs. The tabs are: Master 025 050 056 100 Under tab 025, 050, 056, and 100, from cells "A4:A9", I have a formula in there. I need a macro or code that will change the formula to text value. I tried two different options/codes but they don't work. See below. Code:
Sub Master_File() Workbooks("2017-SSE-Budget-ExcelSummary1.xlsm").Sheets("Master").Range("A4:A100").Value = Workbooks("2017-SSE-Budget-ExcelSummary1.xlsm").Sheets("025").Range("A4:A100").Text Workbooks("2017-SSE-Budget-ExcelSummary1.xlsm").Sheets("Master").Range("A4:A100").Value = Workbooks("2017-SSE-Budget-ExcelSummary1.xlsm").Sheets("050").Range("A4:A100").Text Workbooks("2017-SSE-Budget-ExcelSummary1.xlsm").Sheets("Master").Range("A4:A100").Value = Workbooks("2017-SSE-Budget-ExcelSummary1.xlsm").Sheets("056").Range("A4:A100").Text Workbooks("2017-SSE-Budget-ExcelSummary1.xlsm").Sheets("Master").Range("A4:A100").Value = Workbooks("2017-SSE-Budget-ExcelSummary1.xlsm").Sheets("100").Range("A4:A100").Text End Sub Sub Master_File1() Worksheets("025").Range("A4:A100").Value = Worksheets("025").Range("A4:A100").Text Worksheets("050").Range("A4:A100").Value = Worksheets("050").Range("A4:A100").Text Worksheets("056").Range("A4:A100").Value = Worksheets("056").Range("A4:A100").Text Worksheets("100").Range("A4:A100").Value = Worksheets("100").Range("A4:A100").Text End Sub A similar code was provided to me before by Macropod, and it was to copy from one cell (with a formula) to another cell (only text values), which I was able to use it successfully. I'm sure I'm doing something wrong with this code. I'm attaching a copy of the file for your review, or in case is needed. Hope you can help me. Thank you in advance for your support! Cheers! |
#2
|
|||
|
|||
I found the following code in the following link:
http://www.rondebruin.nl/win/s9/win015.htm This code will allow me to change the formulas from cells A4 through A100 to values. Now, that I have this, I will try to find a macro that will do it for all the tabs automatically. It only works in individual tabs. Well, at least, it's a start. Code:
Sub Range_Example_1() With Range("A5:A100") .Cells.Copy .Cells.PasteSpecial xlPasteValues .Cells(1).Select End With Application.CutCopyMode = False End Sub |
#3
|
|||
|
|||
Well, after playing with the following code, I managed to get it done. If someone can update it or make it more efficient, it would be great!
Code:
Sub All_Info() Call Tab_To_Be_Activated_025 Call Range_Example_025 Call Tab_To_Be_Activated_050 Call Range_Example_050 Call Tab_To_Be_Activated_056 Call Range_Example_056 Call Tab_To_Be_Activated_100 Call Range_Example_100 End Sub Sub Tab_To_Be_Activated_025() Sheets("025").Activate End Sub Sub Range_Example_025() With Range("A4:A100") .Cells.Copy .Cells.PasteSpecial xlPasteValues .Cells(1).Select End With Application.CutCopyMode = False End Sub Sub Tab_To_Be_Activated_050() Sheets("050").Activate End Sub Sub Range_Example_050() With Range("A4:A100") .Cells.Copy .Cells.PasteSpecial xlPasteValues .Cells(1).Select End With Application.CutCopyMode = False End Sub Sub Tab_To_Be_Activated_056() Sheets("056").Activate End Sub Sub Range_Example_056() With Range("A4:A100") .Cells.Copy .Cells.PasteSpecial xlPasteValues .Cells(1).Select End With Application.CutCopyMode = False End Sub Sub Tab_To_Be_Activated_100() Sheets("100").Activate End Sub Sub Range_Example_100() With Range("A4:A100") .Cells.Copy .Cells.PasteSpecial xlPasteValues .Cells(1).Select End With Application.CutCopyMode = False End Sub |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Take String of numbers, expand ranges, sort, then compress back into ranges | AustinBrister | Word VBA | 19 | 08-22-2016 05:18 PM |
Write a macro to change formulas | Xyz_999 | Excel | 2 | 04-20-2016 10:40 PM |
Write a macro to change formulas | Xyz_999 | Excel | 1 | 04-20-2016 09:51 PM |
Macro Needed To Convert Text in Word to Plain Text and Back to Word | rsrasc | Word VBA | 5 | 12-18-2015 07:13 AM |
Help needed: Table Formulas with AND | Scrapyard | Word Tables | 3 | 01-26-2014 12:36 AM |