Macro help

[ChuckF]

I have created a macro, which in a nut shell, creates a new workbook,
and copies 3 worksheets (all in the same workbook) to the new workbook.

(See below for the macro) I am creating, saving to, and copying from
an Excel file which is on our shared network drive here at work.

When I run this macro on my computer, everything is beautiful. When I
go to someone else's computer and run it I get the following error.


Unable to set the top property of the window class. When I hit 'debug'

it is drawing attention to the 3rd line: .Top = 7
Can someone please explain why this stops here, and what I can do to
correct it, so that the macro runs on everyone's computer.


What worries me, is that the macro stops on the third line...how many
other errors (I wonder) will I find.


Sub test2()
'
' test2 Macro
' Macro recorded 4/14/2006 by Chuck Fisher
'


'
Workbooks.Add
With ActiveWindow
.Top = 7
.Left = 766
End With
With ActiveWindow
.Width = 761.25
.Height = 402
End With
ActiveWorkbook.SaveAs Filename:= _
"I:\Accounts\Active Accounts\Office Depot\Posted Monthly Margin

Report\Office Depot.xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Windows("OD Margin Working Worksheet.xls").Activate
Columns("A").Select
Range("A3").Activate
Selection.Copy
Windows("Office Depot.xls").Activate
Range("A1").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("B24").Select
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Land"
Range("A4").Select
Sheets("Sheet2").Select
Windows("OD Margin Working Worksheet.xls").Activate
Application.CutCopyMode = False
Range("A4").Select
Sheets("lot").Select
Columns("A").Select
Range("A3").Activate
Selection.Copy
Windows("Office Depot.xls").Activate
ActiveSheet.Paste
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "Lot"
Range("A4").Select
Windows("OD Margin Working Worksheet.xls").Activate
Application.CutCopyMode = False
Windows("Office Depot.xls").Activate
Sheets("Sheet3").Select
Windows("OD Margin Working Worksheet.xls").Activate
Sheets("Snow").Select
Columns("A").Select
Range("A3").Activate
Selection.Copy
Windows("Office Depot.xls").Activate
Range("A1").Select
ActiveSheet.Paste
Sheets("Sheet3").Select
Sheets("Sheet3").Name = "Snow"
Range("A4").Select
Application.CutCopyMode = False
Sheets("Land").Select
Windows("OD Margin Working Worksheet.xls").Activate
Range("A4").Select
End Sub

 

[Barb Reinhardt]

I don't know the answer to your question, but I'm wondering if all of the
users have the same drive mapped as the I drive.

 

[ChuckF]

Yes they do. I:\ is the "network drive" that we all share.

 

[GS]

Hi Chuck,

The window properties you are trying to set are only available if the user's
window is NOT maximized. My suggestion is to remove these lines from your
code unless they serve some significant purpose, which isn't apparent here.

HTH
Regards,
Garry

 

[ChuckF]

Garry,
Thank you so much for your help. I removed those lines and tested it,
and everything worked exactly the way I hoped it would.

I hope you have a great weekend.

 

[GS]

Chuck,

You're welcome. I'm sorry I didn't finish my post before you posted this
thread. I was preparing some simplified code to address your other concern.
Fact is, while using the macro recorder to create code, it's notoriously
inefficient at best. Here's two procedures that do what you want as I
understand your task. It assumes you are copying the sheets to a new wbk as
stated, even though your code only copies columns A.

Use this one if the source wbk contains more sheets than you want to copy.
You must manually group the sheets to copy. You do that by Ctrl+ select the
tab of each sheet.

Sub CopyMySheets1()
' Copies user-selected sheets to a new wbk,
' then saves the wbk to a specified folder,
' with a specified filename.

Dim sPath As String

sPath = "I:\Accounts\Active Accounts\Office Depot\Posted Monthly Margin
Report\"

'copy to new wbk
ActiveWindow.SelectedSheets.Copy

'the new wbk is now active so save as
ActiveWorkbook.SaveAs fileName:=sPath & "Office Depot.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

End Sub


Use this one if you want to copy all the sheets in the active wbk.

Sub CopyMySheets2()
' Copies all sheets in the active wbk to a new wbk,
' then saves the wbk to a specified folder,
' with a specified filename.

Dim sPath As String

sPath = "I:\Accounts\Active Accounts\Office Depot\Posted Monthly Margin
Report\"

'select all sheets
ActiveWorkbook.Sheets.Select

'copy to new wbk
ActiveWindow.SelectedSheets.Copy

'the new wbk is now active so save as
ActiveWorkbook.SaveAs fileName:=sPath & "Office Depot.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

End Sub

Enjoy!
Regrds,
Garry

 

[Tom Ogilvy]

Here is another way to do it.

Sub test2()
'
' test2 Macro
' Macro recorded 4/14/2006 by Chuck Fisher
'


Dim bkOld as Workbook, bkNew as Workbook
Dim sh as Worksheet
set bkOld = Workbooks("OD Margin Working Worksheet.xls")
set sh = bkOld.Worksheets("Land")
set bkNew = Workbooks.Add()
bkNew.SaveAs _
Filename:= _
"I:\Accounts\Active Accounts\Office" & _
" Depot\Posted Monthly Margin Report\" & _
"Office Depot.xls", _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
sh.Columns("A").copy _
Destination:=bkNew.worksheets(1).Range("A1")
bkNew.Worksheets(1).Name = "Land"
bkOld.Sheets("lot").Columns("A").Copy _
Destination:=bkNew.worksheets(2).Range("A1")
bkNew.Worksheets(2).Name = "Lot"
bkOld.Sheets("Snow").Columns("A").Copy _
Destination:=bkNew.Worksheets(3).Range("A1")
BkNew.Worksheets(3).Name = "Snow"
bkOld.Activate
End Sub