Solved: every month copy whole workbook into new made folder ?? is this posible??

[PaSha]

helo guys...

i have a problem...and i don't know if my idea is relative...

i have a workbook whith 3 sheets and in this sheets there are puted some values... every day for ex. 300 values...

the problem is when the days come the workbook will be filled in without thousands of values and also it would transfer slower and also open slower....

so i thought if there is a posiblity to copy that whole workbook for ex. when the month is ower into a specific new made folder...and then in the original workbook clear all values for the new begining ... ???

hope this is posible ... i watched in the forum and saw some copying codes and so on...but i don't know if this useable for me... and i also would need to make always new folder in which this workbook would be copied??


so thanks in forward...

[PaSha]

ahm i made some progress... now i know how to copy the file...

[vba]

Dim source As String, destination As String

source = "C:\ORGfolder\MySheet.xlsx"
destination ="C:\ORGfolder\backUp\MySheet.xlsx"

FileCopy source, destination

[/vba]


so this code is simple and works... but what i need now is this... you see i named my new copied workbook file like the old one...

but is it posible to program it so that the name of the copied file would be the date on which it was copied????

[Bob Phillips]

[vba]

Private Sub Workbook_Open()
Dim myDate As Date

myDate = Date - Day(Date) ' set to last month in case it doesn't already exist
On Error Resume Next
myDate = Evaluate(ThisWorkbook.Names("_Date").RefersTo)
On Error GoTo 0
If Month(myDate) <> Month(Date) Then
ThisWorkbook.SaveAs ThisWorkbook.Path & Application.PathSeparator & _
Format(Date, "yyyymm") & Application.PathSeparator & _
ThisWorkbook.Name

ThisWorkbook.Names.Add Name:="_Date", RefersTo:=Date
End If

End Sub
[/vba]

This is workbook event code.
To input this code, right click on the Excel icon on the worksheet (or next to the File menu if you maximise your workbooks), select View Code from the menu, and paste the code

[PaSha]

This is workbook event code.
To input this code, right click on the Excel icon on the worksheet (or next to the File menu if you maximise your workbooks), select View Code from the menu, and paste the code[/quote]

hey xld, thanks for the replay...

boah but i don't understand actually this code...

this is actually the code for saving the workbook with perticuallr name in which is included the date...

but how could i say in my example... destination = "C:\folder\backup\myDate"
or something like this ...


boah ...

[PaSha]

OK, i got it

so it works NOW...

the code looks now like this...

thanks XLD you pointed me in the right way


[vba]
Dim source As String, destination As String

source = "C:\ORGfolder\MySheet.xlsx"
destination ="C:\ORGfolder\backUp\" & Format(Date, "dd-mm-yyy") & ".xlsx"

FileCopy source, destination

[/vba]

[Bob Phillips]

But mine made sure it happened on;y once a month. How will you control that?

[PaSha]

oh ...

i didn't post all of the code in that workbook...

i make sure that so i put in front an if statement...

Dim day = Range("i10").Value 'on this cell i have a forumla =DAY()


If day = 1 Then

' code which i was asking at first

End If


so every first in month the code will first copy the files and then celar all contest in original for the new begining...

[Bob Phillips]

But what happens if you open it twice on Day 1, or if Day 1 is a Saturday and you don't open it at all?

[PaSha]

But what happens if you open it twice on Day 1, or if Day 1 is a Saturday and you don't open it at all?

ahmmm hey xld,

very good question... actually i didn't thought about that...

hmmm what could i do now?? is there any posiblity ?? becouse this i now very complex... to look the first day in month and also if is not sunnday and to watch it want open 2 times...



could it be posible in VBA like in c++ for ex. to make it like this

counter = 0;
if workbooks.open ...
then counter++;

and then make it like this ... if counter=0 then open
end if


???

i'm in a real mes now

[PaSha]

or is it posible when a day would be 1 and that day would be sunnday and the workbooks on that day wouldn't be openet, is it posible to make it something like this :


if day = 2 and day is Monday then

' rest of code

end if

???

this could prevent a mistake then...

[PaSha]

But what happens if you open it twice on Day 1, or if Day 1 is a Saturday and you don't open it at all?

hey xld, i managed to solve one part of the problem you showed me about my code...

i was thinking and realised that if the 1st in the month is really Sunday and the workbook on that day wouldn't be opened then actually the next day is allways going to be the 2nd in month and monday...

so i added an aditional if statement which looks If the day in month =2 And day in Week is =1 Then my code should be executed...

...

but there is still the problem about what if the user opens the workbook twice on a day 1... that would put him some mess... so i don't know what do to actually ...
but this is also a problem : what an error happens ... and the book closes by itself and all the data is then lost what then ... hmmm i am still traying to slove this problem...but if someone gets and idea how it could be done...please help...

[Bob Phillips]

Check my code, it caters for all situations.

[Bob Phillips]

What my code does is to use a defined name to store the date of the last update.

When the workbook is opened, this name is read and the month of that update is compared against today's date, and if they differ, then the workbook is saved under the new name.

Thus, on the first opening of the workbook in the month, it will run the code. And that is regardless of the date. Anysubsequent opening in that month, even on the same day, will mean the code doesn't run.