maybe you can help yourself (with my advice, if you haven't tried yet)
1: It's very useful to choke an error that you'r expecting and don't
care how it's going to be solved (relying on defaults), the way you
did before making & naming a new directory (folder), BUT you should
stop choking those errors.
On Error GoTo 0.
2: make sure to debug when .displayalerts=True
Maybe you will find out what's going wrong
>With Application
> .ScreenUpdating = False '**********'''''''' COMMENT OUT THIS LINE by
> putting an apostrophe before it [ ' ]
> .DisplayAlerts = False
> End With
> On Error Resume Next ' folder exists
> MkDir "C:\Marquise SCFs" 'create a folder
> '**********''''''''' Stop the
> "resume next" with the [ On Error GoTo 0 ] statement.
> Set OldWorkbook = WORKBOOKS(ThisWorkbook.Name & ".xls")
GOOD LUCK
On Dec 2, 10:39 pm, Nasim <nbeiz...@gmail.com> wrote:
> 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
