I need a macro to run on all sheets in a workbook, not just the first.

[PJMcDonald]

I have a workbook with currently about 30 worksheets in, although this may vary over time. I have recorded a macro to change a formula to a value which works fine on the first sheet but doesn't work on subsequent sheets. I've assigned the macro to a button i have on all sheets but the macro only works on the first sheet. My basic knowledge of VBA doesn't run to this yet. Thanks.

[HaHoBe]

Hi, PJMcDonald,

you should show the code of your macro.

Ciao,
Holger

[:) Sixthsense :)]

Please post your code so that we can fix it

[PJMcDonald]

Hi, Thanks for your replies. Here is the code.

Sub formulatovalue()
'
' formulatovalue Macro

'Application.Workbook.("rollinghandicap2013.xlsx")
Range("V23").Select
Selection.AutoFill Destination:=Range("V23:V24"), Type:=xlFillDefault
Range("$V23:$V24").Select
Range("$V23").Select
Range("$V23").Copy
Range("$V23").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

[HaHoBe]

Hi, PJMcDonald,

try

Please Login or Register  to view this content.

Could you please wrap Code-Tags around the procedure? Thanks.

Ciao,
Holger

[:) Sixthsense :)]

Try this...

Please Login or Register  to view this content.

[PJMcDonald]

Thanks for your replies,

One thing I forgot to include was that the active cell references (V23:V24) will move down the page weekly or longer so that the next instance of data being entered will be V25 which will require V24 to change to a value and so on.

Thanks again.

[:) Sixthsense :)]

May be this..

change this

Please Login or Register  to view this content.

To

Please Login or Register  to view this content.

[PJMcDonald]

Hi, This is my code as it now stands:

[Sub formulatovalue2()
'
' formulatovalue Macro

'Application.Workbook.("rollinghandicap2013.xlsx")
With ActiveSheet
.Cells(.Cells(Rows.Count, "V").End(xlUp).Row + 1, "V").FillDown
.Range("V23:V24").Value = .Range("V23:V24").Value
End With
End Sub
'
'End Sub]

It doesn't work at all.

[HaHoBe]

Hi, PJMcDonald,

maybe try

Please Login or Register  to view this content.

Ciao,
Holger

[PJMcDonald]

Hi, HaHoBe,

It still doesn't work. I assign the button to the new macro (No.3) but clicking it does nothing. I'm beginning to think that changing the formula to a value manually would be easier.

[HaHoBe]

Hi, PJMcDonald,

as I donīt know what kind of button you choose Iīll attach a workbook with a Forms-Button where the code is doing fine.

Ciao,
Holger

[PJMcDonald]

Hi,

This appears to be a forms button which is what I used. When opening this file Excel told me there was insufficient resources to open in full. But (or therefore) assigning the button to the macro didn't change the result - no change.

PJ.

[HaHoBe]

Hi, PJ,

the workbook I loaded up contains one sheet with 2 values and 1 formula (=Row()*Column()), one Forms-Button and one module holding the code - hard to believe that Excel wonīt be able to open a file of 14,8 KB.

Attached workbook as *.xls.

Ciao,
Holger

[PJMcDonald]

Hi, It's now Saturday morning and this button now works fine. Perhaps my pc needed a reboot yesterday. Still no joy with the formula to value macro 'though. This is the macro as it currently appears.

[Sub formulatovalue3()
'
' formulatovalue Macro

'Application.Workbook.("rollinghandicap2013.xlsx")
With ActiveSheet
With .Cells(.Cells(Rows.Count, "V").End(xlUp).Row, "V")
.AutoFill Destination:=.Resize(2, 1), Type:=xlFillDefault
.Value = .Value
End With
End With
End Sub]

PJ

[HaHoBe]

Hi, PJ,

do you have the trouble with the sample workbooks or with your own workbook?

Regarding the code: the Tags to use are [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] . The other way to do is Go Advanced for ansswering, highlight your code and click the # icon at the top of your post window.

Ciao,
Holger

[PJMcDonald]

Hi,

I only have trouble with my workbook. When I started this thread the macro that I recorded worked on the first sheet but not any of the subsequent worksheets. Now the macro (as amended) doesn't work at all.
Regards.
PJ

[HaHoBe]

Hi, PJ,

maybe you could attach the workbook for us to have a look at it. If thatīs not possible may copy all the contents into a new workbook and try the code in the new workbook thatīs all I may suggest as by now.

Ciao,
Holger

[PJMcDonald]

Hi, The workbook is quite large and has some private info on it so I wont upload it. I'll try to copy it to a new workbook as you suggest. Thanks for your interest.
PJ