SuperFerret
Well-known Member
- Joined
- Mar 2, 2009
- Messages
- 515
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.
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
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.
Code:
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