Macro code error; machine dependent

G

Guest

I have created an excel spreadsheet for multiple users which enables them to
record data monthly. I created a macro which then produces another excel
file containing a report of this data, so that the user does not have to send
me their entire spreadsheet, which is an enormous file. Being a beginner at
VBA, I recorded this macro and tested it thoroughly, and it worked fine.

Now that the spreadsheet is being used by over 200 users, the macro is
producing an error for some users and works fine for other users. I have
again tested the macro in our office, and it works on some machines and not
for others. The error that is produced is "Runtime error 1004: Unable to
set the Paper Size property of the Page Setup class." This spreadsheet was
created in Excel 2000. All machines tested have Excel 2000 and Windows XP.

So, is there an option or setting in Excel that is contriubting to this
error? I have users working with this spreadsheet already in multiple US
states, so I am hoping to find a solution that would be easy for them to fix
without redistributing the entire file. These users would also not be
candidates for fixing the code themselves.

I have included the code below. Thanks so much for any insight.

Sub January2006()
'
' January2006 Macro
' Macro recorded 3/23/2005
'

'
ActiveSheet.Range("A1:L13").Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=False
ActiveCell.Offset(7, 0).Rows("1:1").EntireRow.RowHeight = 69
Selection.Interior.ColorIndex = xlNone
ActiveWindow.DisplayGridlines = False
ActiveWorkbook.DisplayDrawingObjects = xlHide
ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.ColumnWidth = 15.57
ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.ColumnWidth = 21
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.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 = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With

Dim strPath As String
Dim strFileName As String
Range("C2:F2").Select
strPath = Application.DefaultFilePath & "\"
strFileName = Range("C2").Value & "_January 2006 Report"

ActiveWorkbook.SaveAs strPath & strFileName, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
ActiveWindow.Close
Sheets("Intro--Start Here").Select
MsgBox ("The January 2006 file has been saved to your My Documents folder.
Please email file to XXX")

End Sub
 
J

Jim Cone

SJC,

Have you checked out if the machines throwing the error have printers
that can handle letter size paper?

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


I have created an excel spreadsheet for multiple users which enables them to
record data monthly. I created a macro which then produces another excel
file containing a report of this data, so that the user does not have to send
me their entire spreadsheet, which is an enormous file. Being a beginner at
VBA, I recorded this macro and tested it thoroughly, and it worked fine.

Now that the spreadsheet is being used by over 200 users, the macro is
producing an error for some users and works fine for other users. I have
again tested the macro in our office, and it works on some machines and not
for others. The error that is produced is "Runtime error 1004: Unable to
set the Paper Size property of the Page Setup class." This spreadsheet was
created in Excel 2000. All machines tested have Excel 2000 and Windows XP.

So, is there an option or setting in Excel that is contriubting to this
error? I have users working with this spreadsheet already in multiple US
states, so I am hoping to find a solution that would be easy for them to fix
without redistributing the entire file. These users would also not be
candidates for fixing the code themselves.

I have included the code below. Thanks so much for any insight.

Sub January2006()
'
' January2006 Macro
' Macro recorded 3/23/2005
'

'
ActiveSheet.Range("A1:L13").Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=False
ActiveCell.Offset(7, 0).Rows("1:1").EntireRow.RowHeight = 69
Selection.Interior.ColorIndex = xlNone
ActiveWindow.DisplayGridlines = False
ActiveWorkbook.DisplayDrawingObjects = xlHide
ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.ColumnWidth = 15.57
ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.ColumnWidth = 21
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.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 = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With

Dim strPath As String
Dim strFileName As String
Range("C2:F2").Select
strPath = Application.DefaultFilePath & "\"
strFileName = Range("C2").Value & "_January 2006 Report"

ActiveWorkbook.SaveAs strPath & strFileName, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
ActiveWindow.Close
Sheets("Intro--Start Here").Select
MsgBox ("The January 2006 file has been saved to your My Documents folder.
Please email file to XXX")

End Sub
 
G

Guest

Yes, all of the machines in our office have the same printer drivers and
printers, yet some produce the error and some do not.
 
J

Jim Cone

SJC,
Well you got me. However I did condense the code a little and commented
out the paper size code line. It might be worth a try it to see if it works on one
of the problem machines.
Jim Cone
San Francisco, USA

'-----------------------------
Sub January2006()
' January2006 Macro
' Macro recorded 3/23/2005
ActiveSheet.Range("A1:L13").Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlAll
ActiveCell.Offset(7, 0).EntireRow.RowHeight = 69
Selection.Interior.ColorIndex = xlNone
ActiveWindow.DisplayGridlines = False
ActiveWorkbook.DisplayDrawingObjects = xlHide
ActiveCell.Offset(0, 1).EntireColumn.ColumnWidth = 21
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
.PrintArea = ""
.LeftMargin = 54
.RightMargin = 54
.TopMargin = 72
.BottomMargin = 72
.HeaderMargin = 36
.FooterMargin = 36
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
' .PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With

Dim strPath As String
Dim strFileName As String
Range("C2:F2").Select
strPath = Application.DefaultFilePath & "\"
strFileName = Range("C2").Value & "_January 2006 Report"

ActiveWorkbook.SaveAs strPath & strFileName
ActiveWindow.Close
Sheets("Intro--Start Here").Select
MsgBox ("The January 2006 file has been saved to your My Documents folder. " _
& vbCr & "Please email file to XXX")
End Sub
'------------------




Yes, all of the machines in our office have the same printer drivers and
printers, yet some produce the error and some do not.
 
G

Guest

Hi Jim, thanks for all of your help. Yes, it certainly does help to correct
the code a bit. Unfortunately though, these spreadsheets have already been
sent out to the field, so it is difficult to either do a mass resend or walk
them through fixing the code one by one. I was hoping that maybe there was
some issue within excel that would be an easy fix. Guess not, thanks once
again.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro help - Investor sheet 2
Macros in Excel 2
Setting excel to landscape and setting print area from access 1
Method 'ActivePrinter' object_applicated failed 2
Page Setup Macro runs slow 3
Printing-don't need half this code but which half?:-) 5
Recorded Macro to Copy Format 5
PrintQuality setting - is -3 a valid setting? 3

Top