Macro Mystery - Code suddenly stopped working?

[SuperFerret]

Hi all,

I have a macro which has been working for just over a year. Unfortunately as I've been seconded onto another project someone else has been amending the workbook and now the code doesn't work.

This is in Excel 2007 but we used to have to save it as a 2003-2007 compatible, but now we can save as xlsx or xlsm as required.

Sub Save_Me()
Dim ws As Worksheet, wb As Workbook
Application.DisplayAlerts = False
For Each ws In Sheets
    If ws.Name = "Item_Creation" Then
        Workbooks.Add
        Set wb = ActiveWorkbook
        With wb
            .SaveAs Filename:="S:\TRANS\Creates\" & Environ("username") & "\" & ws.Name & "_C" & ws.Range("A2").Value & "_" & Format(Date, "DD MMM") & FileExtStr = ".xlsm": FileFormatNum = 52
            ws.Copy Before:=.Sheets(1)
            .Sheets(1).Cells.Copy
            .Sheets(1).Cells.PasteSpecial xlValues
            .Save
            .Close
        End With
    End If
Next ws
Application.DisplayAlerts = True
End Sub

I get the error:
Run-time error '1004':
Excel cannot insert the sheets into the destination workbook, because it contains fewer rows and columns than the source workbook. To move or copy data to the destinations workbook, you can select the data, and then use the copy and paste commands to insert it into the sheets of another workbook.

Now I get why this is, but I need to be able to save this one worksheet (if I can save it as an xls then great) but I'm stuck. If someone could point me in the right direction I would be most appreciative

 

[JackDanIce]

Hey Ferret, I'm good thanks, guessing I'm having less coding issues than you are! Rory's suggestion should work, mine was only in response to the code in #4 where you're defining FilePath based on the ws.Name but at that point in the code, ws hadn't been defined. You'd need to either explicitally define it prior (e.g. Set ws = ) or as Rory has within a FOR LOOP (e.g. For Each ws In ..)

 

[SuperFerret]

Thanks Rory that works a treat!

Jack, I always have coding issues! Granted the ones I am wrestling with nowadays tend to more SQL based than VBA! I might have a look at tweaking the other code as you suggested, just get my brain ticking over again.

 

[circledchicken]

For post #5, the error is because at that point in the macro, ws doesn't appear to be set to a worksheet

Oops, good point - messed it up again!

Anyway, at least you got a working solution now from Rory.

 

[SuperFerret]

Thanks for all your help CircledChicken, you had more of an idea how to solve it than my stupid brain

Cheers!

 

[circledchicken]

you're welcome, I tried...