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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

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