Thanks, GS. That worked perfectly!
Jim

"GS" wrote in message
...
Jim Berglund submitted this idea :
1. Is there a 'Standard' term for User and User Name? I'm trying to save
a file to a user's desktop. and have written the following, which grinds
to a stop in the If statement at the bottom. How should I write this?
2. I want to also copy the contents of a textbox into the new file. The
textbox is called "Textbox 1" in the Excel Spreadsheet. How can I bring
it across?

Thanks
Jim Berglund

Hi Jim,
Is there a reason why you can't copy the entire sheet to a new workbook?

If you can copy then:
ThisWorkbook.Sheets("Call List").Copy

Else, To copy the data and textbox to a new workbook:

Sub Create_CallListFile()
' Creates a new file on the Desktop
Dim wksSource As Worksheet, wksTarget As Worksheet
Dim wkbTarget As Workbook
Dim lLastRow As Long, lLeft As Long, lTop As Long
Dim vAns As Variant

Set wksSource = ThisWorkbook.Sheets("Call List")
lLastRow = wksSource.UsedRange.Rows.Count
With wksSource.Shapes("TextBox1")
lLeft = .Left: lTop = .Top
End With

Application.ScreenUpdating = False
Set wkbTarget = Workbooks.Add: Set wksTarget = wkbTarget.ActiveSheet

'Copy the data to wksTarget
wksSource.Range("$A$1:$G$" & CStr(lLastRow)).Copy _
Destination:=wksTarget.Range("$A$1")

'Copy & paste the textbox to the same location on wksTarget
wksSource.Shapes("TextBox1").Copy: wksTarget.Paste
With wksTarget.Shapes("TextBox1")
.Top = lTop: .Left = lLeft
End With
Application.CutCopyMode = False

'Ask if user wants to save to the Desktop
vAns = MsgBox("This will paste these values into a new workbook on your
Desktop called 'Call List.xls'", vbOKCancel)
If vAns = vbOK Then wkbTarget.SaveAs "C:\Documents and Settings\" _
& Environ("username") _
& "\Desktop\Call List1.xls"
End Sub

regards,

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc