macro to update to newest version

Posted by steve on April 07, 2001 5:37 PM

Can someone help
What I have is this program to price out window coverings. I'm constently changing and adding to the program. What I need is to be able to update projects written in older versions to the newest. write know I have to copy data off off one page and paste it into the newer version program.
What I'm looking for is a way to click a command button and have this happen.
Heres what I need when the buttons clicked it needs to open the newest version of the program(The newest version will always be named EXPRESS_PRICING) then copy the data, then delete the old version and save the new version as the same name as the old version.

thanks in advance for any ideas
steve

Posted by Dave Hawley on April 07, 2001 6:53 PM

Hi Steve

You wont be able to delete the old file from within itself. You would need to use the Kill statement within another Workbook. The code below will open "EXPRESS_PRICING", copy all sheets from the old version into it. Then close iself without saving. All you need to do then is save EXPRESS_PRICING as the old name over the top of the old version.

Sub TransfereOldToNew()
Dim Wsht As Worksheet
Dim OldName As String

Application.ScreenUpdating = False

OldName = ThisWorkbook.Name

Workbooks.Open FileName:="EXPRESS_PRICING.xls"
Windows(OldName).Activate

For Each Wsht In ThisWorkbook.Worksheets
Wsht.Copy Before:=Workbooks("EXPRESS_PRICING.xls").Sheets(1)
Next Wsht

Windows(OldName).Activate
ThisWorkbook.Close SaveChanges:=False

Application.ScreenUpdating = True
End Sub
OzGrid Business Applications

Posted by steve on April 07, 2001 7:12 PM

Dave if I delete the line Next wsht and change .sheets(1) to (4) will this only copy sheet4 and last if the name I want the workbook to be named is located in cell A1 of sheet4 is there a way to name the workbook it and then to answer yes to the file already exists do you want to overwrite
thanks steve

Posted by Dave Hawley on April 07, 2001 9:49 PM

Hi Steve

This will copy only one sheet into "EXPRESS_PRICING" then save the file as the name in range A1 without displaying the "do you want to...." message. Just as long as the name is not the same as the other open Workbook.

Sub TransfereOldToNew()
Dim Wsht As Worksheet
Dim OldName As String
Dim NewName As String

Application.ScreenUpdating = False

OldName = ThisWorkbook.Name

Workbooks.Open FileName:="EXPRESS_PRICING.xls"
Windows(OldName).Activate

Sheets("CopySheet").Copy Before:=Workbooks("EXPRESS_PRICING.xls").Sheets(1)

NewName = _
Workbooks("EXPRESS_PRICING.xls").Sheets("YourName").Range("A1")

Application.DisplayAlerts = False
Workbooks("EXPRESS_PRICING.xls").SaveAs NewName & ".xls"
Application.DisplayAlerts = True

Windows(OldName).Activate

ThisWorkbook.Close SaveChanges:=False

Application.ScreenUpdating = True
End Sub

Dave
OzGrid Business Applications