Is it possible to make an excel macro to work with all sheets?

[Guest]

Hello, I would like to make a macro in excel that I can use for any
spreadsheet. We constantly get reports and I would like to make a macro that
performs the necessary tasks for each one. However, whenever I make a macro,
it seems like it can only be used for that certain report. Is there a way I
can make one that I can just use for any spreadsheet?



Here's some more information, the sheets contains names, addresses, phone
numbers and other information in their own respective columns. I want to take
only the phone numbers and place them into a text file and at the same time
have a dialog box that prompts for the user's phone number and adds it to the
end of the text file. Finally, Outlook should open up with said text file
attatched.

However, the problem isn't creating the macro. It's the fact that I cannot
seem to find a way so that I can use the macro for multiple reports, as we
get many reports. Whenever I made a macro, it seemed like I could only use it
for the spreadsheet that happened to be opened when I needed to reach the
script editor.



I'm using Excel 2003. I want to use this macro for different
worksheets/workbooks too.

Thanks in advance.

 

[Guest]

The short answer is yes. You can make a macro that will work on all sheets.
However, if the macro is designed to perform complex actions for evaluating,
doing math, copying and pasting then there are many things to consider. The
sheet format and content would need to be the same for each sheet. The data
types would all have to be the same for each command transaction, etc.

But simple macros to find and paste certain data or to check for blank cells
or anything else that is not dependent on cetain data types being in certain
locations can be written for all sheets.

 

[Brian Withun]

To get a macro which persists and is available to all spreadsheets you
will need to create the initial spreadsheet module and then save its
spreadsheet as an XLA or excel add-in file. Then you will need to
enable that add-in using Tools>>AddIns...

It is my understanding that you will then be able to call upon that
code from any spreadsheet.

I hope that gets you started in the right direction.


Brian Herbert Withun

 

[Guest]

Thanks, but now I have a follow-up question.

All the sheets will have the same format, so I'm not worried about
incompatibility. How would I go about making thiis genral macro? Do I start
it up differently? Do I save it to a different location? What exactly do I
need to do since all my other macros seem to only work for a specific
worksheet.

 

[Guest]

Maybe if you posted one of the macros you have created, someone could help
make it generic for you.

 

[Guest]

Here's a macro that turns data from an excel spreadsheet and changes it into
SQL queries and prints it into a text file


Public Sub database()

Dim case_id As String

Dim district As String
Dim division As String
Dim Address As String
Dim date_start As String
Dim Comment As String
Dim csr_employee_number As String
Dim error_id As String
Dim hours_lost As String
Dim i As Integer
Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.CreateTextFile("c:\documents and
settings\user\desktop\testfile.txt", True)
For i = 1 To 635
case_id = Cells(i, 1)
district = Cells(i, 2)
division = Cells(i, 3)
Address = Cells(i, 4)
date_start = Cells(i, 5)
Comment = Cells(i, 6)
csr_employee_number = Cells(i, 7)
error_id = Cells(i, 8)
hours_lost = "1"
If (error_id = "Missing M&S Plate") Then
error_id = "1"
ElseIf (error_id = "Missing C&DO Plate") Then
error_id = "2"
ElseIf (error_id = "ESR Job") Then
error_id = "3"
ElseIf (error_id = "Missing Load Letter") Then
error_id = "4"
ElseIf (error_id = "Missing Field Sketch") Then
error_id = "5"
ElseIf (error_id = "Inconsistent Field Sketch With SIR") Then
error_id = "6"
ElseIf (error_id = "Field Sketch Missing") Then
error_id = "7"
ElseIf (error_id = "Sent Email No Response") Then
error_id = "8"
ElseIf (error_id = "Missing Plot Plan") Then
error_id = "9"
ElseIf (error_id = "RQST") Then
error_id = "10"
ElseIf (error_id = "Returned For Clarification") Then
error_id = "11"
ElseIf (error_id = "Inadequate POE Dimensions") Then
error_id = "12"
ElseIf (error_id = "Early Submission") Then
error_id = "13"
ElseIf (error_id = "Other") Then
error_id = "14"
ElseIf (error_id = "No existing load") Then
error_id = "15"
ElseIf (error_id = "Incorrect Class") Then
error_id = "16"
ElseIf (error_id = "Missing Square Footage") Then
error_id = "17"
ElseIf (error_id = "M&S Not On Page 1") Then
error_id = "18"
ElseIf (error_id = "Incorrect M&S Plate") Then
error_id = "19"
ElseIf (error_id = "Incorrect Name/Address") Then
error_id = "20"
ElseIf (error_id = "Load Info in Remarks") Then
error_id = "21"
End If
Dim e As String
e = "Insert INTO tbl_csr_errors VALUES('" + case_id + "', '" +
district + "', '" + division + "', '" + Address + "', '" + date_start + "',
'" + Comment + "', '" + csr_employee_number + "', '" + error_id + "', '1')"
a.WriteLine (e)
Next
a.Close
End Sub

 

[Guest]

Is there alwasy 635 rows? I'm guessing not, so there's one place to start
making it generic: Add something like this between the Dim i and Set fs =
Create... statements
Dim rCount As Long
rCount=Range("A" & Rows.Count).End(xlUp).Row

that will give you a count of the rows in use on the current active sheet
based on the last one in column A with anything in it (although some between
1 and where ever the pointer ends up could be empty).

Then change your For i statement to
For i = 1 to rCount

The next question is going to be whether or not you always want to use the
same file (testfile.xls) for the results - and then, if yes, do you want to
append data to it or start fresh and write only the data found during this
session?

 

[Guest]

A new file everytime is fine, so I don't need to change much (except for the
FOR statement, thanks for that!). But that's not my problem.

The macro was already generic. I just want it so that I can access the macro
from any report.
http://office.microsoft.com/en-us/excel/HA010872961033.aspx asically answers
my problem, but I cannot place the file in the folder it mentioned due to the
limited permissions I am given on my work computer. Is there another way to
do this?

 

[Guest]

OK, you can't create a file to place into the XLSTART folder. You can do the
same thing EXACTLY except just save the workbook (personal.xls or named
Mymacros.xls as I show below, doesn't matter) in your regular folder. It
will work pretty much the same except that you don't get the automatic
loading of the file that you would if it were in the XLSTART folder.

#1) really easy: save a workbook with nothing but that macro (and any others
you might like to keep handy) in your regular My Documents folder. Anytime
you need them, just open that file along with the other files you're working
with. Lets say you call it MyMacros.xls (but personal.xls would be fine
also). You open another workbook, HardWork.xls. With it open, in the same
instance of Excel use File | Open to open up MyMacros.xls. Choose the
HardWork.xls file again and work in it as usual. When you need a macro from
your MyMacros.xls file, just use Tools | Macro | Macros (from the other book,
like the HardWork.xls file) and identify the one you need. The ones in
MyMacros.xls will be listed using both the workbook name and the macro name.

 

[nomail1983]

You can do the
same thing EXACTLY except just save the workbook (personal.xls or named
Mymacros.xls as I show below, doesn't matter) in your regular folder. It
will work pretty much the same except that you don't get the automatic
loading of the file that you would if it were in the XLSTART folder.

I am so glad I found this gem with a Google search. Even though it
might seem obvious to you, it was not obvious to me until you
mentioned it. I like this approach much better than setting up
personal.xls. It allows me to adapt macros to new repetitive
situations without having to worry about maintaining backward
compatibility.

Thank you very much for taking the time to explain this step by step.
It turned my tedious error-prone task into a breeze.


----- complete original posting -----

 

[Guest]

Glad someone found it useful, especially someone who needed things to work in
just that fashion.

Enjoy.