Macro code error; machine dependent

SJC · 01-31-2006, 04:10 PM

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

Jim Cone · 01-31-2006, 05:50 PM

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

"SJC" <[email protected]> wrote in message ...
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

SJC · 01-31-2006, 05:55 PM

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

"Jim Cone" wrote:

> 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
>
>
> "SJC" <[email protected]> wrote in message ...
> 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
>

Jim Cone · 01-31-2006, 08:50 PM

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
'------------------

"SJC" <[email protected]> wrote in message
Yes, all of the machines in our office have the same printer drivers and
printers, yet some produce the error and some do not.

"Jim Cone" wrote:
> 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

> "SJC" <[email protected]> wrote in message ...
> 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

SJC · 02-01-2006, 12:35 PM

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.

"Jim Cone" wrote:

> 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
> '------------------
>
>
>
>
> "SJC" <[email protected]> wrote in message
> Yes, all of the machines in our office have the same printer drivers and
> printers, yet some produce the error and some do not.
>
>
>
>
> "Jim Cone" wrote:
> > 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
>
>
>
>
>
> > "SJC" <[email protected]> wrote in message ...
> > 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
>