ipbr21054
Well-known Member
- Joined
- Nov 16, 2010
- Messages
- 5,832
- Office Version
- 2007
- Platform
- Windows
Morning as per title.
I use the following code shown below.
The code is used when i make an update to a file then the code replaces all my files with the new update for future use.
I have a folder called CURRENT SHEETS of which has the following folders inside followed by worksheets ACCOUNTS
06 JUNE 07 JULY 08 AUGUST etc etc.
My reason for this edit is that ive just deleted / updated this months file by mistake & now all my records are gone.
So i cant afford this to happen again hence my reason for an edit to the code in use.
Hopefully it would work like so if you could advise please.
The code should compare the current month & then look inside that months file worhseet for a value before deleting / updating.
When i run the code shown below i need the code to check in the current months file if there is a value & if so just update all the other files & to leave the current file alone & not delete / update it, Make sense ?
My folders 06 JUNE 07 JULY 08 AUGUST are in a main folder called CURRENT SHEETS & inside each of thos folders is a worksheet called AACOUNTS
The current month is MAY so the folder in question is called 05 MAY
The worksheet inside each folder will always be called ACCOUNTS.
On that Sheet we need to look at sheet called INCOME (1)
This is where we now decide if we delete / update this file,if in use then there will be a value in cell B1
So if there is a value in cell B1 "this sheet will have all my records" so leave this worksheet alone & then just delete/ update all the others etc 06 JUNE 07 JULY 08 AUGUST
Hopefully this will not delete / update the current months file.
The code will then be the same when we move on through the year.
Example AUGUST
So folder will be 08 AUGUST
Worksheet called ACCOUNTS
Sheet is called INCOME (1)
Is there a value in cell B1 yes or no depending on answer then either delete / update or leave this one alone
I use the following code shown below.
The code is used when i make an update to a file then the code replaces all my files with the new update for future use.
I have a folder called CURRENT SHEETS of which has the following folders inside followed by worksheets ACCOUNTS
06 JUNE 07 JULY 08 AUGUST etc etc.
My reason for this edit is that ive just deleted / updated this months file by mistake & now all my records are gone.
So i cant afford this to happen again hence my reason for an edit to the code in use.
Hopefully it would work like so if you could advise please.
The code should compare the current month & then look inside that months file worhseet for a value before deleting / updating.
When i run the code shown below i need the code to check in the current months file if there is a value & if so just update all the other files & to leave the current file alone & not delete / update it, Make sense ?
My folders 06 JUNE 07 JULY 08 AUGUST are in a main folder called CURRENT SHEETS & inside each of thos folders is a worksheet called AACOUNTS
The current month is MAY so the folder in question is called 05 MAY
The worksheet inside each folder will always be called ACCOUNTS.
On that Sheet we need to look at sheet called INCOME (1)
This is where we now decide if we delete / update this file,if in use then there will be a value in cell B1
So if there is a value in cell B1 "this sheet will have all my records" so leave this worksheet alone & then just delete/ update all the others etc 06 JUNE 07 JULY 08 AUGUST
Hopefully this will not delete / update the current months file.
The code will then be the same when we move on through the year.
Example AUGUST
So folder will be 08 AUGUST
Worksheet called ACCOUNTS
Sheet is called INCOME (1)
Is there a value in cell B1 yes or no depending on answer then either delete / update or leave this one alone
Rich (BB code):
Private Sub NewFileToFolders_Click()
Dim FileName As String
Dim SaveName As String
Dim FromPath As String
Dim ToPath As String
Dim fldrArr As Variant
Dim x As Long
Dim FSO As Object
FileName = "ACCOUNTS TEMPLATE.xlsm"
SaveName = "ACCOUNTS.xlsm"
FromPath = "C:\Users\Ian\Desktop\EBAY\ACCOUNTS\"
ToPath = "C:\Users\Ian\Desktop\EBAY\ACCOUNTS\CURRENT SHEETS\"
fldrArr = Array("04 APRIL", "05 MAY", "06 JUNE", "07 JULY", "08 AUGUST", "09 SEPTEMBER", "10 OCTOBER", "11 NOVEMBER", "12 DECEMBER", "13 JANUARY", "14 FEBRUARY", "15 MARCH", "16 APRIL")
On Error Resume Next
For x = LBound(fldrArr) To UBound(fldrArr)
Set FSO = CreateObject("Scripting.FileSystemObject")
FSO.CopyFile Source:=FromPath & FileName, Destination:=ToPath & fldrArr(x) & "\" & SaveName
Next x
MsgBox "ALL FILES NOW TRANSFERED TO FOLDERS", vbInformation, "CONFIRMATION MESSAGE"
Set FSO = Nothing
End Sub