Transfer macro to new user

[Guest]

Hello:

Running Excel 2003. Am very new to Visual Basic, but have recorded a simple
page formatting macro to include margins, header, footer, font size, style,
etc. This was saved to Personal Macro Workbook, and used twice weekly to
format (4) downloads into one file for distribution to sales team.

Responsibility for report transferred to fellow team member, (who is
unfamiliar with macros & their recording.) Without re-recording, would like
to send macro from my Personal Macro Workbook, to hers, and unsure how to
accomplish this. Any and all suggestions welcome!

TIA,
Sandi

 

[Die_Another_Day]

The easiest way is to copy your personal.xls to her pc. It resides at:
C:\Documents and Settings\UserName\Application
Data\Microsoft\Excel\XLSTART

Otherwise press Alt+F11 to open the VBE editor, then find your macro
and copy everything from "Sub YourMacro" to "End Sub" into an email and
send that to your coworker. Have her open the editor and paste that
code into her personal.xls.

Charles

 

[Guest]

Well, actually I had thought that far ahead, and she already had the code in
an e-mail. However, when I save it to her personal.xls file, I still get an
error code "400" when running the macro. Is it perhaps where I am saving it?
I'm presuming it should be in modules. Thanks for your help!

Sandi

 

[Die_Another_Day]

Please paste the code, change sensitive information if you need. Also
please tell me which line it is failing on and could you give me the
error description?

Charles

 

[Guest]

Charles,

No sensitive info contained - Only a page formatting macro, since want the
same page appearance on (4) download files, twice weekly. Code follows:

Sub Report_Pages()
'
' Report_Pages Macro
' Macro recorded 5/18/2006 by C11163 To expedite Page Setup in NAD Reports
'
' Keyboard Shortcut: Ctrl+Shift+Y
'
Cells.Select
With Selection
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection.Font
.Name = "Arial Narrow"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
End With
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = "&F"
.CenterHeader = ""
.RightHeader = "&A"
.LeftFooter = "&""Small Fonts,Regular""&6Page &P of &N"
.CenterFooter = _
"&""Small Fonts,Regular""&6Prepared by: S. Rush - Canon USA
Confidential"
.RightFooter = "&""Small Fonts,Regular""&6&D"
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
End With
End Sub



Whether selecting the shortcut icon that I assigned the macro to, or using
the Tools-Macro-Run dropdown, it gives me the error message "400", with no
explanation. When I try to "step into" the macro, it simply highlights in
yellow "Sub Report_Pages()". Any ideas?

TIA,
Sandi

 

[Die_Another_Day]

Sandi, I pasted the code you sent me and, with the exception of not
having a "Print Quality" option, every thing worked fine for me. I
created a new Module in Personal.xls and pasted the code there. Sorry
but I don't have any more ideas at the moment. You are use a module,
not a "Class Module" right?

 

[Die_Another_Day]

Sandi, I pasted the code you sent me and, with the exception of not
having a "Print Quality" option, every thing worked fine for me. I
created a new Module in Personal.xls and pasted the code there. Sorry
but I don't have any more ideas at the moment. You are use a module,
not a "Class Module" right?

 

[Guest]

Charles:

Thank you, thank you, a thousand thanks!

I re-did the "module-paste" portion, re-saved personal.xls, and now it works
for her!
We are truly indebted!

Sometimes it just takes someone pointing out the obvious error of one's
ways. ;-)
Have a great day!
Sandi