Windows error

[Neecy]

Hello-
I am having a debug error that comes up from the script listed below:

' Copy "Term" data to new workbook
Windows("New Hire" & "(" & Site & " " & Last_Name & ", " & First_Name &
" " & Hire_Date & ")" & ".xls").Activate
Sheets("Term Form").Activate
Range("A1:z100").Select
Selection.Copy
Range("a1").Select
Windows("Term" & Site & Last_Name & First_Name & ".xls").Activate
Sheets("Sheet1").Activate
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select


Application.CutCopyMode = False

ActiveWorkbook.Save

End Sub


This script is to write the selected details over to a new workbook on a new
worksheet. The error appears from the 1st line of the windows statement.
Could someone review and tell me when I am going wrong?

Thanks-

Neecy

 

[Patrick Molloy]

paste this


?"New Hire" & "(" & Site & " " & Last_Name & ", " & First_Name & " " &
Hire_Date & ")" & ".xls"
into the Immediate window & hit return
does it evaluate into what Excel would recognise at the windows name?

 

[Joel]

Sub test()

'Copy "Term" data to new workbook
HireBk = "New Hire" & "(" & Site & " " & Last_Name & ", " & First_Name & _
" " & Hire_Date & ""
TermBk = "Term" & Site & Last_Name & First_Name & ".xls"

With Workbooks(HireBk).Sheets("Term Form") _
.Range("A1:z100").Copy
End With

With Workbooks(TermBk).Sheets("Sheet1")

.Range("a1").PasteSpecial _
Paste:=xlPasteColumnWidths, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
.Range("a1").PasteSpecial _
Paste:=xlPasteAll, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
End With
Workbooks(TermBk).Save

End Sub

 

[Neecy]

Hi-
It appears that the changes listed below didn't work. I am unfamiliar with
some of the scripts with Windows syntax, therefore, it's probably better if I
give you the complete coding. I am modifying an old application and so far,
the only error I am receiving is related to windows script. All the changes
I have applied so far works with the exception of below:

Sub Save_Term_Workbook()

Application.ScreenUpdating = False

Dim Site
Dim Last_Name
Dim First_Name
Dim Hire_Date

Site = Sheets("Master Form").Range("AA8")
Last_Name = Sheets("Master Form").Range("D17")
First_Name = Sheets("Master Form").Range("D19")
Hire_Date = Sheets("Master Form").Range("A19")


' Add workbook
Workbooks.Add
' Save current spreadsheet as "Term" & "Site" & "LastName" & "First Name"
ActiveWorkbook.SaveAs Filename:="Term" & Site & Last_Name &
First_Name & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
'

' Format worksheet
Selection.Merge
Range("A1:O1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("g1:n2").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("G3:n3").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("g4:n4").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("A6:q6").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("a7:q7").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("A8:q8").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("g12:j12").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("L1212").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("G14:h14").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("I14:K14").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("N1414").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("F16:g16").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("K16:L16").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("j2323").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("h2525").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("J2727").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("h2929").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("g3131").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("j3333").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("j3737").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("h4848").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("d5151").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("i5555").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("g5757").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("h5959").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("h6060").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("h6161").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("G71:j71").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("N7171").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("B2:C2").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

' Copy "Term" data to new workbook
Windows("New Hire" & "(" & Site & " " & Last_Name & ", " & First_Name &
" " & Hire_Date & ")" & ".xls").Activate
Sheets("Term Form").Activate
Range("A1:z100").Select
Selection.Copy
Range("a1").Select
Windows("Term" & Site & Last_Name & First_Name & ".xls").Activate
Sheets("Sheet1").Activate
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select


Application.CutCopyMode = False

ActiveWorkbook.Save

End Sub

I am wondering if these formats are what is causing the error.

Thanks-

Neecy

 

[Patrick Molloy]

see my changes below

if you think the formats are causing the error, then rem them out
however, be sure that the names are what you expect them to be

Hi-
It appears that the changes listed below didn't work. I am unfamiliar
with
some of the scripts with Windows syntax, therefore, it's probably better
if I
give you the complete coding. I am modifying an old application and so
far,
the only error I am receiving is related to windows script. All the
changes
I have applied so far works with the exception of below:

Sub Save_Term_Workbook()

Application.ScreenUpdating = False

Dim Site
Dim Last_Name
Dim First_Name
Dim Hire_Date

DIM NewFileName
DIM wb 'NEW

Site = Sheets("Master Form").Range("AA8")
Last_Name = Sheets("Master Form").Range("D17")
First_Name = Sheets("Master Form").Range("D19")
Hire_Date = Sheets("Master Form").Range("A19")


' Add workbook

set wb = Workbooks.Add

' Save current spreadsheet as "Term" & "Site" & "LastName" & "First Name"

NewFileName:="Term" & Site & Last_Name & First_Name & ".xls"
wb.SaveAs Filename:= NewFileName, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

'

' Format worksheet
CODE DELETED FOR CLARITY

' Copy "Term" data to new workbook

Windows("New Hire" & "(" & Site & " " & Last_Name & ", " & First_Name &
" " & Hire_Date & ")" & ".xls").Sheets("Term Form").Range("A1:z100").Copy

' Range("a1").Select 'DELETE THIS LINE

' Windows(NewFileName).Ac 'not required
' Sheets("Sheet1").Activate 'not required
Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks _