|
Windows error
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 |
Windows error
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? "Neecy" wrote in message ... 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 |
Windows error
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" wrote: 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 |
Windows error
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 "Patrick Molloy" wrote: 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? "Neecy" wrote in message ... 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 |
Windows error
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" wrote in message ... 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 _ :=False, Transpose:=False Application.CutCopyMode = False WB.Save End Sub I am wondering if these formats are what is causing the error. Thanks- Neecy "Patrick Molloy" wrote: 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? "Neecy" wrote in message ... 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 |
| All times are GMT +1. The time now is 07:28 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com