Looking for code to:

Copy "Master" spreadsheet to O:\Sales_Tax and rename spreadsheet to
Last Month. (For example February, if I executed the code today)

Thanks,

Met


--
Metrazal
------------------------------------------------------------------------
Metrazal's Profile: http://www.excelforum.com/member.php...o&userid=31648
View this thread: http://www.excelforum.com/showthread...hreadid=518833

If I understand you correctly, you want to save a copy of the active workbook
with a filename that represents the previous month. If so, this should do it:

Sub SaveCopyAs_LastMonth()
Dim sFileName As String
sFileName = "O:\Sales_Tax" & MonthName(Month(Date) - 1) & ".xls"
ActiveWorkbook.SaveCopyAs sFileName
End Sub

=OR-
Did you want to copy the ActiveSheet into a new workbook and save it? If so,
post back.

Regards,
GS

Oops! I forgot one separator. Change this line:

sFileName = "O:\Sales_Tax" & MonthName(Month(Date) - 1) & ".xls"

to:

sFileName = "O:\Sales_Tax\" & MonthName(Month(Date) - 1) & ".xls"

Regards,
GS


"GS" wrote:

If I understand you correctly, you want to save a copy of the active workbook
with a filename that represents the previous month. If so, this should do it:

Sub SaveCopyAs_LastMonth()
Dim sFileName As String
sFileName = "O:\Sales_Tax" & MonthName(Month(Date) - 1) & ".xls"
ActiveWorkbook.SaveCopyAs sFileName
End Sub

=OR-
Did you want to copy the ActiveSheet into a new workbook and save it? If so,
post back.

Regards,
GS

I am looking to save it in an exsisting workbook called
O:\Sales_Tax.xls


Thanks,
Met


--
Metrazal
------------------------------------------------------------------------
Metrazal's Profile: http://www.excelforum.com/member.php...o&userid=31648
View this thread: http://www.excelforum.com/showthread...hreadid=518833

Okay, I understand now. Here's some code that should work:

Sub MoveSheetsToOtherBook()
' Moves the selected sheet(s) to a specified workbook
' If the workbook isn't open, it opens it.
' If the file doesn't exist, it exits.
' Requires bBookIsOpen(), bFileExists functions

Dim wbkTarget As Workbook, wbkSource As Workbook
Dim Shts As Sheets

Const sPath As String = "O:\"
Const sName As String = "Sales_Tax.xls"

Set wbkSource = ActiveWorkbook 'or ThisWorkbook

'If wbkSource has been saved, you can't move all its sheets.
'If wbkSource was created from a template but not saved yet,
'delete the following line and its corresponding End If.
If Not ActiveWindow.SelectedSheets.Count = ActiveWorkbook.Sheets.Count Then
Set Shts = ActiveWindow.SelectedSheets

'Get a reference to wbkTarget
If Not bBookIsOpen(sName) Then
If bFileExists(sPath & sName) Then
Set wbkTarget = Workbooks.Open(sPath & sName)
Else
MsgBox "The target file does not exist !", vbExclamation + vbOKOnly
Exit Sub
End If
Else
Set wbkTarget = Workbooks(sName)
End If

'Move the sheet(s) to wbkTarget, save & close it.
wbkSource.Sheets(Shts).Move
after:=wbkTarget.Sheets(wbkTarget.Sheets.Count)
With wbkTarget
.Save
.Close
End With
End If

End Sub


Function bBookIsOpen(wbkName) As Boolean
' Checks if a specified workbook is open.
'
' Arguments: wbkName The name of the workbook
'
' Returns: True if the workbook is open

Const sSource As String = "bBookIsOpen()"

Dim x As Workbook
On Error Resume Next
Set x = Workbooks(wbkName)
bBookIsOpen = (Err = 0)

End Function


Function bFileExists(fileName As String) As Boolean
' Checks if a file exists in the specified folder
'
' Arguments: fileName The fullname of the file
'
' Returns: TRUE if the file exists

Const sSource As String = "bFileExists()"

On Error Resume Next
bFileExists = (Dir$(fileName) < "")

End Function

'**
Regards,
GS

Oops! You did say copy, ..not move. -Sorry about not catching that!
Replace MoveSheetsToOtherBook() with this:

Sub CopySheetToOtherBook()
' Copies the active sheet to a specified workbook.
' If the workbook isn't open, it opens it.
' If the file doesn't exist, it exits.
' Requires bBookIsOpen(), bFileExists functions

Dim wks As Worksheet, wbkTarget As Workbook
Dim sNewName As String

Const sPath As String = "O:\"
Const sName As String = "Sales_Tax.xls"

Set wks = ActiveSheet
sNewName = MonthName(Month(Date) - 1)


'Get a reference to wbkTarget
If Not bBookIsOpen(sName) Then
If bFileExists(sPath & sName) Then
Set wbkTarget = Workbooks.Open(sPath & sName)
Else
MsgBox "The target file does not exist !", vbExclamation + vbOKOnly
Exit Sub
End If
Else
Set wbkTarget = Workbooks(sName)
End If

'Move the sheet(s) to wbkTarget, save & close it.
wks.Copy after:=wbkTarget.Sheets(wbkTarget.Sheets.Count)
With wbkTarget
ActiveSheet.Name = sNewName
.Save
.Close
End With

End Sub


Regards,
GS