Help Needed with Macro to Change Formulas to Text Using Ranges

rsrasc · #1 11-28-2016, 09:26 AM

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!

rsrasc · #2 11-29-2016, 01:41 PM

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

rsrasc · #3 11-29-2016, 02:31 PM

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

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