Macros

[AlwaysQuestions]

I have recorded a macro to open up a service report I use all the time. I am
able to open it only when I open Excel for the first time. After I close
that worksheet, I am not able to use the macro again.

There were some instructions I found about where to save but that did not
help.

What do I need to do?

Any help would be greatly appreciated!

 

[Gord Dibben]

You most likely picked store in "this workbook" when recording the macro.

When that workbook is closed, your macro will not be available for other
workbooks.

Can you find the recorded macro?

Where is it?

We don't know what the instructions you found consist of.

Could you post them?


Gord Dibben MS Excel MVP

 

[AlwaysQuestions]

Here is the macro that I recorded:

Sub ServiceReports2008_09()
'
' ServiceReports2008_09 Macro
'
' Keyboard Shortcut: Ctrl+e
'
Workbooks.Open Filename:= _
"C:\Users\jaurmd\Documents\2008-09 Business Classified Service
Report - 04 11 08.xlsx"
End Sub


Also, I followed the following instructions:

In the Store Macro in list, I chose Personal Macro Workbook as instructions
I printed from the microsoft website states that Excel creates a hidden
personal macro workbook (personal.xlsb) so that I can use all the time.

I found the macro in c:\user\username\appdata\local\microsoft\excel\xlstart
and am able to start excel from there and use the macro. However, once I
close the workbook, the macro will not work anymore.

Thanks!!

 

[Gord Dibben]

Personal.xlsb should open hidden when Excel starts and not close until you
exit Excel.

Is this not happening?

Sounds like Personal.xlsb is stored in the correct folder.


Gord

 

[AlwaysQuestions]

Hello Gord!

When I open Excel for the first time, the personal.xlsb worksheet opens up.
With this worksheet open, I can use my macros. Once I close it, which I do
as soon as I have opened another worksheet or saved one, then the macro will
not function.

Any suggestions?

Thanks a lot!

 

[Gord Dibben]

Why do you close Personal.xlsb?

You need it to remain open while Excel is running.

Hide it so's it remains available in the background.

View>Hide then close Excel.

You will be asked to save changes to Personal.xlsb..............click "Yes"

Re-open Excel.

BTW..............Personal.xlsb is a "workbook" not a "worksheet".


Gord

 

[AlwaysQuestions]

Perfect!! What a simple solution - wish I had known this a long, long time
ago but had had no time to do research.

Many, many thanks, Gord!!!!!

 

[Gord Dibben]

Good to hear you're sorted out.

Thanks for the feedback.


Gord

 

[ericaamousseau]

Gord, I have a macro that was working beautifuly with a shortcut that as well
was working very well. The spreadsheets I made were for other people and for
some reason now the shortcut is not working at all! Any idea why? I am
rather new to macros, but I am a quick leaner - thanks
The macro in VBE is to get rid of all rows that have the word lab in a
certain column at all. even labuselab. (below) the shortcut is ctr(a)

Sub Find_LAB()
Dim rng As Range
Dim what As String
what = "LAB"
Do
Set rng = ActiveSheet.UsedRange.Find(what)
If rng Is Nothing Then
Exit Do
Else
Rows(rng.Row).Delete
End If
Loop
End Sub


So what am I doign wrong? thansk so much!