Windows error

N

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
 
P

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?
 
J

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
 
N

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("L12:p12").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("N14:p14").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("j23:p23").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("h25:p25").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("J27:p27").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("h29:p29").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("g31:p31").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("j33:p33").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("j37:p37").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("h48:p48").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("d51:p51").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("i55:p55").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("g57:p57").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("h59:p59").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("h60:p60").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("h61:p61").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("N71:p71").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
 
P

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

Neecy said:
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
Click to expand...
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
Click to expand...
set wb = Workbooks.Add
' Save current spreadsheet as "Term" & "Site" & "LastName" & "First Name"
Click to expand...
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
Click to expand...
' Copy "Term" data to new workbook
Click to expand...
Windows("New Hire" & "(" & Site & " " & Last_Name & ", " & First_Name &
" " & Hire_Date & ")" & ".xls").Sheets("Term Form").Range("A1:z100").Copy
' Range("a1").Select 'DELETE THIS LINE
Click to expand...
' Windows(NewFileName).Ac 'not required
' Sheets("Sheet1").Activate 'not required
Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks _
 

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

Run-time error 1004 paste method of worksheet class failed 3
conso macro 0
Run macro from active workbook whose name keeps changing 20
lenghty code 4
Runtime 1004 Error Help Request 1
Help again!!! 1
copy to new workbook 1
Macro/coyy & Paste from a new data a fix infp 1

Top