J
Jim Berglund
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
Sub CreateCallList() 'Create a new file on the Desktop
Dim wsCList As Worksheet
Dim wb As Workbook
Dim Textbox_1 As TextBox
Dim Response, UserName As String
Application.ScreenUpdating = False
Set wb = ThisWorkbook
Set wsCList = wb.Worksheets("Call List")
wsCList.Activate
With ActiveSheet
.Range("$A:$G").Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme,
Operation:=xlNone _
, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Response = MsgBox("This will paste these values into a new worksheet
on your Desktop called Call List.xls", vbOKCancel)
If Response = vbOK Then
ActiveWorkbook.SaveAs Filename:="C:\Users\" & UserName &
"\Desktop\Call List1.xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False,
_
CreateBackup:=False
End If
End With
End Sub
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
Sub CreateCallList() 'Create a new file on the Desktop
Dim wsCList As Worksheet
Dim wb As Workbook
Dim Textbox_1 As TextBox
Dim Response, UserName As String
Application.ScreenUpdating = False
Set wb = ThisWorkbook
Set wsCList = wb.Worksheets("Call List")
wsCList.Activate
With ActiveSheet
.Range("$A:$G").Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme,
Operation:=xlNone _
, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Response = MsgBox("This will paste these values into a new worksheet
on your Desktop called Call List.xls", vbOKCancel)
If Response = vbOK Then
ActiveWorkbook.SaveAs Filename:="C:\Users\" & UserName &
"\Desktop\Call List1.xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False,
_
CreateBackup:=False
End If
End With
End Sub