Copy 2 sheets to Network and rename

[kaj10]

Sorry i should have named my thread better. Im a newbie and it is showing. I am an intern and am majoring in ISYS up until a couple of weeks ago I didnt know excel had VB capabilites but that is the language i am dealing with. I'm really interested in learning this new language so lets jump into my problem. I need to copy two worksheets in my workbook then save them to the network with a new name, but not just any new name, it needs to be formatted like this RG_BU0801RQ where RG_ is constant BU can also be ww, ro, and ca. 08 is the year 01 is the version # and RQ is constant. This is really giving me fits and I really appreciate any help.

Here is the code i already have and the only problem im having is with the file name.:beerchug: I have Excel 97


Dim sh As Worksheet
Dim shName As String
Dim strDate As Date
Dim x As String
'make copies of the request and report sheets
Sheets("Report").Select
Sheets("Report").Copy Before:=Sheets(3)
Sheets("Request").Select
Sheets("Request").Copy Before:=Sheets(2)


'Save copies as The new name and email to Admin
strDate = Format(Date, "yy")
shName = RG_ + " - " + strDate + " - " + REQ
ActiveWorkbook.SaveAs FileName:="s:\RG trials\trials\shname"

'Delete copies in the work book
Sheets("Request (2)").Select
ActiveWindow.SelectedSheets.Delete
Sheets("Report (2)").Select
ActiveWindow.SelectedSheets.Delete

[mdmackillop]

The simplest way is to copy the sheets to a new book and save that. If the sheets have links to other pages in the original book, you may wish to Copy/PasteSpecial Values to remove them


Option Explicit
Sub Test()
Dim shName As String
Dim strDate As Date

'make copies of the request and report sheets
Worksheets(Array("Report", "Request")).Copy
strDate = Format(Date, "yy")
shName = "RG- " & strDate & " - REQ"
ActiveWorkbook.SaveAs Filename:="s:\RG trials\trials\" & shName & ".xls"

End Sub

[kaj10]

Ok, that is working but for some reason it wont let me get just the two digit year for the date and my boss will cry.
Thank you

[kaj10]

It is showing the whole date is what im trying to say

[kaj10]

I hope everyone has a great weekend and i appreciate the Help MD but it is my wedding anniverary today and I have to go home to my wife so she knows i did not forget:rotlaugh: :hi:

[mdmackillop]

strDate should be String type not Date Type


Sub Test()
Dim sh As Worksheet
Dim shName As String
Dim strDate As String
Dim BU As String
Dim Ver As String

'Initilalise variables
BU = "ww"
Ver = "01"
'make copies of the request and report sheets
Worksheets(Array("Report", "Request")).Copy
strDate = Format(Date, "yy")
shName = "RG_" & BU & strDate & Ver & "RQ" & ".xls"
ActiveWorkbook.SaveAs Filename:="s:\RG trials\trials\" & shName & ".xls"
End Sub


Have a great day.