Hi,
I am creating macro in office 2007 and saving file as office 2003. I
have a userform that after validating and puting info on the sheet4
should open a new workbook, paste info from sheet4 to this new
workbook, protect, give new filename and close the new workbook.
It does create a new workbook, protect celles (i know from the .xlsx I
save) and give filename. when I go to open the file it says it is
corrupted or something and can not be opened.
At saving time it also opens the save as dialog for .xlsx file type
(which I do not want to happen). I save it anyways and when i go to
this file I can see that this file is blank, but cells are protected!
so there are 3 problems:
1- the xls file can not be opened
2- the save as for .xlsx opens
3- even in the .xlsx file the celles are not copied
Thanks for your help. below is my code:
Nasim
Private Sub cmdSave_Click()
Dim NewWorkbook As Workbook, OldWorkbook As Workbook
Dim M As String
With Application
.ScreenUpdating = False
.DisplayAlerts = False
End With
On Error Resume Next ' folder exists
MkDir "C:\Marquise SCFs" 'create a folder
Set OldWorkbook = WORKBOOKS(ThisWorkbook.Name & ".xls")
Set NewWorkbook = WORKBOOKS.Add 'add workbook for the sheets
M = OldWorkbook.Sheets(5).Range("R1") 'new file name
OldWorkbook.Sheets(4).UsedRange.Copy
With NewWorkbook.Worksheets(1).Cells(1)
.PasteSpecial xlValues 'in Case formulas refer To hiden
cells
.PasteSpecial xlFormats 'in Case formulas refer To hiden
cells
.Cells(1, 1).Select
End With
With NewWorkbook.Worksheets(1)
.Cells.Select
.Selection.Locked = False
.Range("a1").Select
.Cells.Select
.Selection.Locked = True
.Protect Password:="123"
End With
With NewWorkbook
.SaveAs Filename:="C:\ABCs" & "\" & M & ".xls"
.Sheets(1).Range("a1").Select
End With
With NewWorkbook
.Close SaveChanges:=True
End With
With Application
.CutCopyMode = False
.ScreenUpdating = True
.DisplayAlerts = True
End With
MsgBox "This file has been saved to " & "C:\ABCs" ."
cmdPrint.Enabled = True
cmdSubmit.Enabled = True
End Sub
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,500 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
