Excel save as woes

[maxxev]

Hi, could you help me here please, I think I know why it's happeneing, but I don't know how to fix it (I don't really know a great deal about VBA).

Sub Monday()
'
' Monday Macro
'
    Dim newFile As String, fName As String
    fName = Range("H2").Value & Range("I2").Value
    newFile = ("MLST_" & fName)
    ChDir _
    "S:\Manufacturing\Manufacturing Planning\MLSTs in use"
    ActiveWorkbook.SaveCopyAs Filename:=newFile

    Sheets(Array("MLST", "PKG")).Select
    Sheets("MLST").Activate
    ActiveWindow.SmallScroll Down:=-9
    Range("K7:AB552").Select
    ActiveWindow.SmallScroll Down:=24
    Range("K7:AB552,K556:AB590").Select
    Range("AB556").Activate
    Selection.ClearContents
End Sub

The error that is returned comes up as 1004
I think it is because the cell "I2" has a date in it, it is formatted to dd.mm.yy in the cell, but I'm guessing it needs to be formatted in the VBA too?

Cheers

 

[maxxev]

Still not working but a slight revision to the code:

Sub Monday()
'
' Monday Macro
'
    Dim newFile As String, fName As String
    fName = "MLST_" & Range("H2").Value & "_" & Range("I2").Value
    newFile = fName
    ChDir _
    "S:\Manufacturing\Manufacturing Planning\MLSTs in use"
    ActiveWorkbook.SaveCopyAs Filename:=newFile & ".xls"

    Sheets(Array("MLST", "PKG")).Select
    Sheets("MLST").Activate
    ActiveWindow.SmallScroll Down:=-9
    Range("K7:AB552").Select
    ActiveWindow.SmallScroll Down:=24
    Range("K7:AB552,K556:AB590").Select
    Range("AB556").Activate
    Selection.ClearContents
End Sub

 

[ck1999]

try this

    Dim newFile As String, fName As String
    fName = "MLST_" & Format(Range("H2"), "dd.mm.yy") & "_" & Range("I2").Value
    newFile = fName
ChDir _
    "S:\Manufacturing\Manufacturing Planning\MLSTs in use"
     ActiveWorkbook.SaveCopyAs Filename:=newFile & ".xls"
    Sheets(Array("MLST", "PKG")).Select
    Sheets("MLST").Activate
    Range("K7:AB552,K556:AB590").ClearContents
    Range("AB556").Activate

ck1999

 

[maxxev]

Hi, thank you, I have it working now, I think it was actualyl a read only folder issue... (I didn't know the folder had recently been made read only... communication don't you just love it...)

Anyway, I have since realised that of course the changes I wanted to make to the copy before saving it are happening afterwards (saveascopy just saves the file under a different name, nothing else).

So have tried, saving the copy, opening it, making the ammends and then closing it again, but it opens with a "version number" (incrimental number) on the end of the file, any thoughts?

Code:

    ThisWorkbook.SaveCopyAs Filename:=newFile & ".xls"
    Workbooks.Open Filename:=newFile & ".xls"

I have just pasted the relelvent bit to see if you think the file opened should have a version number on the end, and if there is anyway to prevent this...

Or alternative options, I don't really want to go down the "save as" option then re-opening the original document if I can avoid it becase the original document is a week template (which will be saved weekly before the daily ones are issued (which is what this code is supposed to be generating)).

Here is the whole code as it stands now for info:

Sub Monday()
'
' Monday Macro
'
    Dim newFile As String, fName As String
    Sheets("MLST").Activate
    fName = "MLST_" & Range("H2").Value & "_" & Range("I2").Text
    newFile = fName
    ChDir _
    "S:\Manufacturing\Manufacturing Planning\MLSTs in use"
    ThisWorkbook.SaveCopyAs Filename:=newFile & ".xls"
    Workbooks.Open Filename:=newFile & ".xls"
    Sheets(Array("MLST", "PKG")).Select
    Sheets("MLST").Activate
    ActiveWindow.SmallScroll Down:=-9
    Range("K7:AB552").Select
    ActiveWindow.SmallScroll Down:=24
    Range("K7:AB552,K556:AB590").Select
    Range("AB556").Activate
    Selection.ClearContents
End Sub

 

[ck1999]

I am not sure why it is working now unless you went to cell h2 and typed the date as 11.10.08. If someone enters the data as 11/10/2008 even though it shows 11.10.08 in the formula bar and in vba it picks up 11/10/2008 and this is why you are getting the error 1004. So using the format i mentioned earlier prevents this.

Also, If you look at my code it does the same thing yours does except with the unnecessary lines of code.

You should avoid selects whenever possible.


ck1999

 

[ck1999]

I am not sure about your incrementing number problem. It is not coming from your code. It could be something with the network folder if you are using one. Please post examples of the file names you are getting and maybe someone else can help.

ck1999