Save As problems

I have recorded a macro that does nearly the same thing. I just copied the information needed, then used pastespecial to paste the format then pastespecial to paste the numbers. I have attached below my code used.

Sub Save_BACT()
'
' Save_BACT Macro

'

'
Range("B4:O59").Select
Selection.Copy
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 4
Range("A4").Select
Workbooks.Add
Columns("B:B").ColumnWidth = 9.67
Columns("C:E").Select
Selection.ColumnWidth = 8.33
Columns("F:F").ColumnWidth = 13.67
Columns("G:H").Select
Selection.ColumnWidth = 8.33
Columns("I:I").ColumnWidth = 9.22
Columns("J:K").Select
Selection.ColumnWidth = 10.22
Columns("L:L").ColumnWidth = 8.33
Columns("M:M").ColumnWidth = 9.67
ActiveWindow.SmallScroll ToRight:=2
Columns("N:N").ColumnWidth = 8.78
Columns("O:O").ColumnWidth = 11.11
ActiveWindow.SmallScroll ToRight:=-2
Range("B2").Select
ActiveWindow.Zoom = 90
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A2").Select
Application.CutCopyMode = False
ChDir "C:\Documents and Settings\jsartain\Desktop"
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\jsartain\Desktop\BACT for Customer.xls", FileFormat _
:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
False, CreateBackup:=False
End Sub

Possible solution

Sometimes the slow way is the fast way, or at least the most flexible. Here's a macro that will do what you want, and it will work whether or not you have your destination file open at the time. Also, you can add more sheets to either file and not affect this macro. This assumes you have a destination workbook already set up with the same sheet tab names as the source workbook (Recap 1, Recap 2, etc). I put a bunch of notes in so you can see why and what I did.

Modify as needed.

Sub CopySheets()

'Prepare Excel:
Application.EnableEvents = False
Application.DisplayAlerts = False
Application.ScreenUpdating = False

‘Activate the source workbook in case you add code before this that involves another workbook
Windows("Workbook1.xls").Activate
‘Copy the Recap 1 worksheet
Sheets("Recap 1").Cells.Copy

'See if you have the Workbook2 file open; if not then open it:
On Error GoTo b:
Windows("Workbook2.xls").Activate
'If you do not have the file open there will be an error and it will goto b:
GoTo c:
b:
ChDir "C:\Your\File\Path"
Workbooks.Open Filename:="C:\Your\File\Path\Workbook2.xls"
'This will open the file
c:
'Now when we get to c: the Workbook2 file is open either way so we activate it:
Windows("Workbook2.xls").Activate
'Then paste special values into the Recap 1 sheet...
Sheets("Recap 1"). Range("A1").PasteSpecial xlValues

'And continue the data transfer, back and forth from Workbook1 to Workbook2

Windows("Workbook1.xls").Activate
Sheets("Recap 2").Cells.Copy
Windows("Workbook2.xls").Activate
Sheets("Recap 2"). Range("A1").PasteSpecial xlValues

Windows("Workbook1.xls").Activate
Sheets("Recap 3").Cells.Copy
Windows("Workbook2.xls").Activate
Sheets("Recap 3"). Range("A1").PasteSpecial xlValues

Windows("Workbook1.xls").Activate
Sheets("Recap 4").Cells.Copy
Windows("Workbook2.xls").Activate
Sheets("Recap 4"). Range("A1").PasteSpecial xlValues

Windows("Workbook1.xls").Activate
Sheets("Recap 5").Cells.Copy
Windows("Workbook2.xls").Activate
Sheets("Recap 5"). Range("A1").PasteSpecial xlValues

Windows("Workbook1.xls").Activate
Sheets("Recap 6").Cells.Copy
Windows("Workbook2.xls").Activate
Sheets("Recap 6"). Range("A1").PasteSpecial xlValues

'Save and close Workbook2 because we don't need it anymore
ActiveWorkbook.Save
ActiveWindow.Close

‘Reactivate Workbook1, and add more code for other tasks or just end the Sub
Windows("Workbook1.xls").Activate

'Reset Excel back to the way it was before it was open:
Application.CutCopyMode = False
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = True

End Sub

Any help???

Tom Urtis

Thanks