Automating Exporting Records

[thomas.szwed]

Hi,

I have a query regarding finding some code/macro even to automatically export and arhcive records from a sheet in my workbook on a monthly basis. I would therefore like all the data on one sheet to;

- be copied into a new workbook at start or end of every month
- new workbook named correctly by month
- data from original sheet deleted

This is the process i am trying to automate. I assume it would be built into the module of the workbook....Can anyone give any help on code to use....?

Many Thanks

[mdmackillop]

You may not want to clear all cells, so modify to suit.
Sub Macro1()
ActiveSheet.Copy
ActiveWorkbook.SaveAs Filename:="C:\AAA\" & InputBox("Enter Month") & ".xls"
ActiveWindow.Close
Cells.ClearContents
End Sub

[thomas.szwed]

This works fine. But is there anyway to program the macro to run say on the 1st of every month? And therefore to know what the previous month was so it could save the file correctly named itself - i.e. no human interaction?

+ How can you define which cells to clear in a specific sheet if there are many?

Many Thanks

[mdmackillop]

1. Have a look here (http://www.vbaexpress.com/forum/showthread.php?t=16298)
2. Create range name(s) for your data cells and use that to clear them

[thomas.szwed]

Hi thanks for your proposed solution, it works well apart from when the user clicks the cancel button the spreadsheet enters into debug mode.....do you have the code should the user choose to press the cancel button?

I also have a second question -

Can i restrict which sheet this operation is performed on as i have many sheets and the macro appears as a button on a toolbar which is constantly displayed?

Many Thanks

[thomas.szwed]

Can anyone help with this???

Thanx x million

[mdmackillop]

If msgbox("Do Action",vbOKCancel) = vbYes then
DoEvents
Else
ExitSub
End If

[thomas.szwed]

Hi all,

I am using the following code to give the user the choice archive some records. But my problem is that on the very last msg Box (where you enter the file name) if you click 'cancel' it still copies data, when its sposed to just exit the sub. This bit is the "ActiveWorkbook.Close SaveChanges:=False". But it doesnt seem to be quitting and making no changes, it is making changes when it shouldnt. Can anyone help me out??

Pls look at the attachment to make thigs clearer....



Sub CreateArchive()
Sheets("For Archive").Activate
Dim msgResponse As String 'confirm delete
Application.ScreenUpdating = False

'get user confirmation
msgResponse = MsgBox("This will archive records from the 'For Archive' Sheet. Continue?", _
vbCritical + vbYesNo, "Delete Entry")

Select Case msgResponse 'action dependent on response
Case vbYes

'Input box to name new file
NewName = InputBox("Enter Month for filename")

'Save it with the NewName and in the same directory as original
Worksheets("For Archive").Copy
ActiveWorkbook.SaveCopyAs ThisWorkbook.Path & "\" & NewName & ".xls"
ActiveWorkbook.Close SaveChanges:=False
Range("A2:AC65000").ClearContents

Case vbNo
Exit Sub

End Select

End Sub



Many Thanks!

[mdmackillop]

SaveCopyAs was confusing the issue. It leaves the copied sheet open in a new book.

Sub CreateArchive()
Sheets("For Archive").Activate
Dim msgResponse As String 'confirm delete
Application.ScreenUpdating = False

'get user confirmation
msgResponse = MsgBox("This will archive records from the 'For Archive' Sheet. Continue?", _
vbCritical + vbYesNo, "Delete Entry")

Select Case msgResponse 'action dependent on response
Case vbYes
'Input box to name new file
NewName = InputBox("Enter Month for filename")

'Save it with the NewName and in the same directory as original
Worksheets("For Archive").Copy
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & NewName & ".xls"
ActiveWorkbook.Close
Range("A2:AC65000").ClearContents
Case vbNo
Exit Sub
End Select
End Sub

[thomas.szwed]

Hiya again....

This completely stops the code from working when i change it to SaveAs. Its really quite strange the problem i am having.....sometimes it works the sometimes it just doesnt....i cant quite see which step it is failing at....can u see/help??



Sub CreateArchive()
Sheets("For Archive").Activate
Dim msgResponse As String 'confirm delete
Application.ScreenUpdating = False

'get user confirmation
msgResponse = MsgBox("This will archive records from the 'For Archive' Sheet. Continue?", _
vbCritical + vbYesNo, "Archive Records")

Select Case msgResponse 'action dependent on response

Case vbYes

'Input box to name new file
NewName = InputBox("Enter Month for filename", "Archiving Records", vbOKCancel)

If NewName = vbOK Then

'Save it with the NewName and in the same directory as original
Worksheets("For Archive").Copy
ActiveWorkbook.SaveCopyAs ThisWorkbook.Path & "\" & NewName & ".xls"
ActiveWorkbook.Close SaveChanges:=False
Worksheets("For Archive").Range("A2:AC65000").ClearContents
Else

Exit Sub

End If

Case vbNo

Exit Sub

End Select

End Sub



Ta