Copy spreadsheet into another file.

[Metrazal]

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

[GS]

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

[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

[Metrazal]

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


Thanks,
Met

[GS]

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

[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

[Metrazal]

Can you explain this? Is this a seperate sub/module? Please advise.

Thanks,

MET

[GS]

Hi Metrazol,

My previous post is a sub, and should be placed in a standard module.
Disregard all previous examples because the last one copies the active sheet
("Master") into your existing file. The comments at the beginning of the
procedure explain what it does, while each process within the procedure has
comment that outline the steps.

Just call the procedure, or assign it to a button. Make sure the "Master"
sheet you want to copy into the other workbook is active. (the one you're
working on)

Regards,
GS


"Metrazal" wrote:

>
> Can you explain this? Is this a seperate sub/module? Please advise.
>
> 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
>
>

[Metrazal]

When I input the sub as entered and run it, I get the following error:

Compile Error
Sub or Function not defined

* bBookIsOpen is highlighted


Thanks,
Met

[GS]

Hi Met,

As the lead comments in the sub indicate, you still need to use the two
support functions in my other post. Here they are again:

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,
Garry

[Metrazal]

Done deal...

Thanks..

One more thing:

I need the date to be in the format FEB 2006, I still need the date to back up one month but abbreviate the date and display the year.

Once again..

Thanks,
Met

[GS]

Hi Met,

Modify as follows:

sNewName = UCase$(left(monthname(month(date)-1),3)) & " " & year(date)

Regards,
Garry


"Metrazal" wrote:

>
> Done deal...
>
> Thanks..
>
> One more thing:
>
> I need the date to be in the format FEB 2006, I still need the date
> to back up one month but abbreviate the date and display the year.
>
> Once again..
>
> 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
>
>

[Metrazal]

Once again..

Many thanks..