macro to standard module

[Guest]

I have a .xlt with 2 macros in it.
The .xlt is copied in code from Access to a .xls and then data is
transferred from Access to the .xls
Both of the macros are activated on click of seperate command buttons.
The command buttons are in Sheet1 (Code) the sheet name is "Summary"
Macro 1 runs fine but I am getting an error on the 2nd as below.
Private Sub CommandButton2_Click()
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long

With Worksheets("Summary")
FirstRow = 2
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
End With

For iRow = FirstRow To LastRow
Next iRow

Range("A" & iRow).Select
ActiveCell.Value = "Assets"
ActiveCell.Font.Bold = True
ActiveCell.Font.Size = 12
Sheets("Assets").Select ERROR HERE Run-time error 1004 select method of
range class failed

I have discovered thanks to usergroup that this needs to be in a standard
module.
Can someone tell me how I do this and then how do I call it.
Thank you

 

[Guest]

Do you have a sheet Named Assets?

 

[Guest]

Not in the .xlt but I don't run the macro in the xlt.
It is in the .xls though as it is created by Access.
My other macro also refers to these new sheets in the same way without a
problem albeit it only copies cells across.

 

[Dave Peterson]

First, if this code is associated with a commandbutton from the control toolbox
toolbar, then the code does belong behind the worksheet.

And if your code that adds that worksheet named Assets has not run, then you
won't have that worksheet to select.

Option Explicit
Private Sub CommandButton2_Click()

Dim NextRow As Long
Dim wks As Worksheet

'since the code is behind the worksheet (summary) that owns
'the code, you can use the Me keyword to refer to that sheet
With Me
NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
With .Range("A" & NextRow)
.Value = "Assets"
.Font.Bold = True
.Font.Size = 12
End With
End With

'check to see if a worksheet named Assets really exists
Set wks = Nothing
On Error Resume Next
Set wks = Me.Parent.Worksheets("assets")
On Error GoTo 0

If wks Is Nothing Then
MsgBox "No worksheet named Assets in this workbook!"
Else
wks.Select
End If
End Sub


Any chance that your code was supposed to make a worksheet named Assets first???

 

[Guest]

Hi Dave,
Worksheet Assets is definately ok.
I have now copied the macro to a Module and if I run that Module from VBA it
works fine. All I need to know is how the user runs this module easily
without delving into VB. A button somewhere that calls the module? How do I
do this?
Simon

 

[Dave Peterson]

If this is a commandbutton from the control toolbox toolbar, then don't put the
code in a general module.

If you really, really want to put the code in a general module, you'll still
have to have the commandbutton2_click event call that procedure--so why
bother?????

If you change to a button from the forms toolbar, you can keep the code in a
general module and assign the forms button to that procedure in that general
module.

Be prepared to have to reassign that macro each time the workbook is opened.
Else the macro will still point at the code in the .xlt file.

I think you'd be better off using a commandbutton and the code behind the
worksheet in this situation.

 

[Guest]

Thanks for your help Dave.
I have now solved it by putting both macros in the on Open event and its fine.

 

[Dave Peterson]

Both?????

Thanks for your help Dave.
I have now solved it by putting both macros in the on Open event and its fine.

 

[Guest]

Yes. Why not?

Both?????

 

[Dave Peterson]

I didn't even see one complete macro in your previous posts. So the word "both"
surprised me.

And if you were really trying to execute the code you pasted by clicking on a
button (you did use "CommandButton2_Click" as the procedure name), I wouldn't
have guessed that anything in the workbook_open event would help.

But if you got it working, then that's good.

Yes. Why not?