Quick and easy solution needed!

[grahamhurlburt]

This is the code I have to open a new workbook containing copies of two
worksheets from a previous workbook:


Code:
--------------------

Sub Autpen()

Dim NewBook As Workbook
Dim Ctr As Integer

Application.ScreenUpdating = False
Application.DisplayAlerts = False

' Create a new workbook.
Set NewBook = Workbooks.Add

' Copy the two worksheets into the new workbook.
ThisWorkbook.Sheets(Array("Packing Slip", "Invoice - Packing Slip")).Copy _
before:=NewBook.Sheets(1)

' Delete all of the other sheets in the new workbook. The
' initial value of the counter is 1 greater than the number of
' worksheets that you want to copy into the new workbook.
For Ctr = 3 To NewBook.Sheets.Count

NewBook.Sheets(3).Delete

Next

End Sub

--------------------


How would I have the new workbook save as a specified filename in a
specified folder when it is created? For example if the previous
workbook is titled 5500.xls the new workbook would be named
5500.01.xls.

Also, one more quick question for the pros, in a sort of seperate
issue, could a macro add a value (+0.01) to a specified cell in excel?

I am grateful for any help, Sensei!

 

[Brassman]

Try this...


Code
-------------------
Sub AddBook()

Dim NewBook As Workbook
Dim Ctr As Integer
- Dim CurrentWorkbook As String
Dim FileLoc As String
-
-CurrentWorkbook = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4)
FileLoc = "C:/path/to/file/" & CurrentWorkbook & ".01.xls"-

Application.ScreenUpdating = False
Application.DisplayAlerts = False

' Create a new workbook.
Set NewBook = Workbooks.Add

' Copy the two worksheets into the new workbook.
ThisWorkbook.Sheets(Array("Packing Slip", "Invoice - Packing Slip")).Copy _
before:=NewBook.Sheets(1)

' Delete all of the other sheets in the new workbook. The
' initial value of the counter is 1 greater than the number of
' worksheets that you want to copy into the new workbook.
For Ctr = 3 To NewBook.Sheets.Count

NewBook.Sheets(3).Delete

Next


-NewBook.SaveAs (FileLoc)-

End Su
-------------------


And for your second question, Yes, a macro can add a value to
specific cell.

You can use code like this...


Code
-------------------

Sub AddToCell()

Sheets("Packing Slip").Range("A1").Value = Sheets("Packing Slip").Range("A1").Value + 0.01

End Sub