Macro not working on all versions of Excel

R

RWiles

New Member
Joined
Apr 23, 2003
Messages
22
In the simple macro below, I use a button to copy some formulas and save as values. This works great in 2000. My problem is, the page is copied from a hidden page, and when I run in '97 the copied button is renamed and therefore has no code behind it. Is there a way to make this work on both versions?
:rolleyes:
Private Sub BudgCreate_Click()
Range("H10").Select
ActiveCell.FormulaR1C1 = "=R[-3]C[-4]"
Range("H11").Select
ActiveCell.FormulaR1C1 = "=R[1]C[-4]"
Range("H12").Select
ActiveCell.FormulaR1C1 = "=R[1]C[-5]"
Range("H10:H12").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("I16").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-5]/12"
Range("I16").Select
Selection.Copy
Range("I17:I51").Select
ActiveSheet.Paste
Range("I16:I51").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select

End Sub
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
VBA is still a bit of a black art to me but one thing I've learnt is that you don't need to select - the code you have is generated by the recorder probably so this is just an aside. You can make the code more efficient with changes to avoid selecting, for example

Range("A1:B4").Select
Selection.Copy

change to

Range("A1:B4").Copy

Doesn't answer your question though I know...
 
Upvote 0
My problem is, the page is copied from a hidden page, and when I run in '97 the copied button is renamed and therefore has no code behind it. Is there a way to make this work on both versions?
Click to expand...
From this, it's not really clear why your macro isn't working. And why is the button renamed in '97?

As an addition to Iridium's post, you can clean your code a bit more by only using Application.CutCopyMode = False once, at the end.

Hope that helps,

Smitty
 
Upvote 0
Thanks for the code suggestions, I was working on just getting the button to work before cleaning it up, I know that it is pretty bad right now.

I wish that I knew why the button is being renamed, after '97 copies the sheet the button becomes CommandButton1_Click. Is there anyway that I can get around this?
 
Upvote 0
RWiles said:
Thanks for the code suggestions, I was working on just getting the button to work before cleaning it up, I know that it is pretty bad right now.

I wish that I knew why the button is being renamed, after '97 copies the sheet the button becomes CommandButton1_Click. Is there anyway that I can get around this?
Click to expand...

It appears you are using an ActiveX control button [Control Toolbox] ?
How are you copyiing the Hidden sheet over ??

BTW, you can condense/clean code to

Code: 
Private Sub BudgCreate_Click()
Range("H10").FormulaR1C1 = "=R[-3]C[-4]"
Range("H11").FormulaR1C1 = "=R[1]C[-4]"
Range("H12").FormulaR1C1 = "=R[1]C[-5]"

With Range("H10:H12")
    .Copy
    .PasteSpecial Paste:=xlValues
End With

With Range("I16")
    .FormulaR1C1 = "=RC[-5]/12"
    .Copy
End With

With Range("I17:I51")
    .PasteSpecial xlPasteAll
    .Copy
    .PasteSpecial xlPasteValues
End With

Application.CutCopyMode = False
Range("A1").Select

End Sub
 
Upvote 0
I am unhiding, copying and then re-hiding the sheet. I could actually leave the sheet visable, just trying to eliminate temptation for our field users. The entry that it creates will actually only be used by high level users so I am thinking about just noting that it will not work on '97, they should all have 2000 anyway.

Thanks for the tip on styles, I will definetely use them.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
Macro to update list to last row on another sheet copy source from multiple sheets
Replies
0
Views
321
Azamamir
A
P
  • Solved
Recorded Macro Ammendments
Replies
3
Views
354
Joe4
Joe4
L
  • Solved
VBA remove leading zeros
2
Replies
14
Views
1K
Joe4
Joe4
S
  • Solved
VBA does not continue to copy after blank cell
Replies
8
Views
544
jolivanes
jolivanes
G
  • Question
How can i extract the correct leave time on this code?
Replies
1
Views
374
rlv01
R

Forum statistics

Threads
1,215,514
Messages
6,125,265
Members
449,219
Latest member
daynle

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top