How to clear dollar amounts at the start of a new year

Microsoft Excel 2010 - complete product...
December 9, 2015 at 13:51:50
Specs: Windows 64, 8gb
I enter expenses in multiple rows by dates. I would like cells to return to zero or clear at start of each year to prevent my having to delete every single amount and date. Can this be done easily?

See More: How to clear dollar amounts at the start of a new year

Report •


#1
December 9, 2015 at 14:08:55
The easiest way is to select your range of cells and press the Delete key.

Or am I missing something?

MIKE

http://www.skeptic.com/


Report •

#2
December 9, 2015 at 14:32:18
I have hundreds of entries in multiple sheets/books so looking for a auto feature/formula.

Report •

#3
December 9, 2015 at 15:47:08
It can be done with a macro, but the macro would have to be added to each workbook.

The code would run every time the workbook is opened. It could check the date and if it was January 1st it would clear the cells. However, if that is all it did, the code would clear the cells every time it was opened on January 1. We can't have that, can we?

So we would need a key to tell the code not to clear the cells if it had already done it that day. Is there something that would be put in the worksheet on January 1 that the code could use as a key?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
December 10, 2015 at 04:04:56
Just to add to this, if all your workbooks are in the same folder or subfolders within a root folder, you can have one workbook with the code, that opens each workbook and clears the data then saves and closes them. This way you dont have to worry about a key, because you only open the 'Cleaning tool' when you want to clear all your workbooks.

I use something similar at work, i wrote it ages ago but it can be modified for your purpose. i open a workbook called 'Cleaning tool' press a command button and navigate to where my excel files are, it then opens each one and resets the fields to the default values i have told the code to set them to, this is done to over 100 workbooks.


Report •

#5
December 10, 2015 at 04:32:28
I like it! Much better than code and keys in every workbook.

Even if the files aren't in a single location, a list of the files could be placed in the "cleaning tool" so the code can go get them.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
December 10, 2015 at 04:52:25
Thanks Derby,

Here is an example, what the code does is:

When you run the code from sub LoadFiles, it will ask you for a root folder where all your excel files to be cleaned are, it will then

Open each file
Clear Range("A1") in each sheet
Enter 'Mouse' is range("B1") of each sheet
Save and close

it will also then look within sub folders of that root folder and perform the same actions as above.

We can also build in logic to ignore certain sheets for example is a sheet is called "Introduction" or whatever, we can tell the code to ignore these sheets. I do this in my cleaning tool as i have many sheets which i do not want to 'clean'

There are so many things we can do just need to know the exact requirements

Look at this thread to see what process the code performed for the OP
http://www.computing.net/answers/pr...

Paste this into a new workbook in a Module and run from 'Sub LoadFiles'

Ensure to include a reference to 'Microsoft scripting runtime'
VBE > Tools > Reference - Microsoft scripting runtime

'''''''''''''''''''''''''''''''''''''
'''''''''''BK Cleaning Tool''''''''''
'''''''''''''''''''''''''''''''''''''
Dim iExcel As Object

Public Sub LoadFiles()

    ListFilesInFolder BrowseForFolder, True

End Sub

Function BrowseForFolder(Optional OpenAt As Variant) As Variant
    
    Dim ShellApp As Object
    
    'Create a file browser window at the default folder
    Set ShellApp = CreateObject("Shell.Application"). _
    BrowseForFolder(0, "Please choose a folder", 0, OpenAt)
    
    'Set the folder to that selected. (On error in case cancelled)
    On Error Resume Next
    BrowseForFolder = ShellApp.self.Path
    On Error GoTo 0
    
    'Destroy the Shell Application
    Set ShellApp = Nothing
    
    'Check for invalid or non-entries and send to the Invalid error
    'handler if found
    'Valid selections can begin L: (where L is a letter) or
    '\\ (as in \\servername\sharename. All others are invalid
    
    Select Case Mid(BrowseForFolder, 2, 1)
        Case Is = ":"
            If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid
        Case Is = "\"
            If Not Left(BrowseForFolder, 1) = "\" Then GoTo Invalid
        Case Else
            GoTo Invalid
    End Select
    
    Exit Function
    
Invalid:
    'If it was determined that the selection was invalid, set to False
    BrowseForFolder = False
End Function

Sub ListFilesInFolder(SourceFolderName As String, IncludeSubfolders As Boolean)
              
    If Right(FilesPath, 1) <> "\" Then
        FilesPath = FilesPath & "\"
    End If

    Dim FSO As Scripting.FileSystemObject
    Dim SourceFolder As Scripting.Folder, SubFolder As Scripting.Folder
    Dim FileItem As Scripting.file
    Dim r As Long

    Set FSO = New Scripting.FileSystemObject
     
    Set SourceFolder = FSO.GetFolder(SourceFolderName)
    
    For Each FileItem In SourceFolder.Files
        
        Set iExcel = CreateObject("Excel.Application")
        Let iExcel.Visible = True
        
        iExcel.DisplayAlerts = False
        iExcel.EnableEvents = False
        iExcel.AskToUpdateLinks = False
        
        iExcel.Workbooks.Open (FileItem.Path)
        
        Dim Sh As Worksheet
        Dim myRange As Range
        Dim ws As Worksheet
    
        For Each Sh In iExcel.Worksheets
            
            On Error Resume Next
            
            Sh.Range("A1").Value = Empty
            Sh.Range("A2").Value = "mouse"
            
        
        Next Sh

            iExcel.DisplayAlerts = False
            iExcel.ActiveWorkbook.CheckCompatibility = False
            iExcel.ActiveWorkbook.Close True
            
        Next FileItem

        If IncludeSubfolders Then
            For Each SubFolder In SourceFolder.SubFolders
                ListFilesInFolder SubFolder.Path, True
            Next SubFolder
        End If

        
        iExcel.Application.Quit
           
        Set FileItem = Nothing
        Set SourceFolder = Nothing
        Set FSO = Nothing
        Set iExcel = Nothing
       
End Sub

message edited by AlwaysWillingToLearn


Report •

#7
December 10, 2015 at 16:17:50
I am so grateful for the responses. You are correct, I don't want to zero or clear cells in all sheets because I use formulas to zero some of my entries by comparing the date change. Basically, I have a budget/accounting program built in excel and working great. Had one workbook but changed to multiple due to memory issues(learnt solution on website).
I usually have workbooks by years and linked each year. Implementing new design changes was impractical. Now I am trying to zero balances each new year and keep 1 set of files in a single folder. Unfortunately, I have not work with macros before but your suggestion could be my solution.. just need to implement. Once per year, I need to clear dates and amounts in specific cells, the dates not as important as clearing the amounts. Is there a macros for dummies for excel that I could read to get caught up.. I am willing to learn... I have been practicing with excel help menus but the macros are not easily understood though?
I have a budget of 14 linked workbooks/sheets, maybe a total of 30 sheets. I use formulas to zero some of my entries at year end.
Sorry for the long text, just wanted to give a clearer picture :)

Report •

#8
December 10, 2015 at 16:27:20
To your question, "So we would need a key to tell the code not to clear the cells if it had already done it that day. Is there something that would be put in the worksheet on January 1 that the code could use as a key?"
Every sheet in each workbook shows today,s date. Could we reference the date, in particular the year of the date?

Report •

#9
December 11, 2015 at 02:23:54
Hey,

How many sheets are there per workbook and do they all have cells that you wish to clear when you run the tool?

Are all the cells you want to clear the same on each sheet?

Can you specify the cells you want the code to clear so we can build it into the code for you to test?

If the cells are different in each sheet then please specify

Sheet name then cells

As iv said above, if you use my solution then a key is not necessary as you only run the cleaing tool when you need to, it will be a single workbook which you can open and run in Jan 1st and it will do the cleaning for you.

message edited by AlwaysWillingToLearn


Report •

#10
December 11, 2015 at 11:49:06
Great! I will start on that task.. thanks millions.

Report •

#11
December 30, 2015 at 16:38:02
Happy holidays, best wishes for 2016!!

I have compiled the cells I need to clear by work book/sheet location. How can I group the cells to make it easier for you to add to code?

See example: Work Book Location: C:\Program Files (x86)\My DocsToGo\Budget World

Sheet 1: C:\Program Files (x86)\My DocsToGo\Budget World\[Input & Setup Center.xlsx]Input-Extra Pyt&Oth Int'!$B$3:$M$38)

Sheet 2: C:\Program Files (x86)\My DocsToGo\Budget World\[Input & Setup Center.xlsx]Input-Cash Trf&Ckg Int'!$B$3:$M$10)


Report •

#12
December 30, 2015 at 16:50:23
For example, if I have 10 sets of cells for 1 sheet HOW SHOULD THE DATA BE SETUP?best:

Path: C:\Program Files (x86)\My DocsToGo\Budget World\[Input & Setup Center.xlsx]Input-Cash Trf&Ckg Int'!$B$3:$M$10)

addition cells for same sheet are: (B14:M16, B18:M21, B24:M26, B29:M31, B33:M35, B37:M40, P3:AA10, P14:AA16, P18:AA21.....).


Report •

#13
January 4, 2016 at 06:35:23
Happy new year to you...

its my first day back, let me have a little play and i'll get back to you as soon as i have put something together.


Report •

#14
January 8, 2016 at 03:57:57
Hi,

on each of your workbooks are the names of the sheets the same?

for example will you always have

Input-Extra Pyt&Oth Int
and
Input-Cash Trf&Ckg Int

and will the cells that you want to clear on each of these be the same all the time?

message edited by AlwaysWillingToLearn


Report •

#15
January 8, 2016 at 04:23:53
I have used the cells you mention above for the sheets you mention to clear the ranges specified.

The code will

1) Open each workbook in the selected folder and include subfolders
2) look for sheet 'Input-Extra Pyt&Oth Int' and clear range 'B3:M10'
3) look for sheet 'Input-Cash Trf&Ckg Int' and clear ranges

"B3:M10"
"B14:M16"
"B18:M21"
"B24:M26"
"B29:M31"
"B33:M35"
"B37:M40"
"P3:AA10"
"P14:AA16"
"P18:AA21"

4) it will then save and close the workbook and open the next one......

Please try this on copies of your workbooks (one or two of them) and see how it works

Paste the code into a module and ensure you add a reference to 'Microsoft Scripting Runtime'

'''''''''''''''''''''''''''''''''''''
'''''''''''BK Cleaning Tool''''''''''
'''''''''''''''''''''''''''''''''''''
Dim iExcel As Object

Public Sub LoadFiles()

    ListFilesInFolder BrowseForFolder, True

End Sub

Function BrowseForFolder(Optional OpenAt As Variant) As Variant
    
    Dim ShellApp As Object
    
    'Create a file browser window at the default folder
    Set ShellApp = CreateObject("Shell.Application"). _
    BrowseForFolder(0, "Please choose a folder", 0, OpenAt)
    
    'Set the folder to that selected. (On error in case cancelled)
    On Error Resume Next
    BrowseForFolder = ShellApp.self.Path
    On Error GoTo 0
    
    'Destroy the Shell Application
    Set ShellApp = Nothing
    
    'Check for invalid or non-entries and send to the Invalid error
    'handler if found
    'Valid selections can begin L: (where L is a letter) or
    '\\ (as in \\servername\sharename. All others are invalid
    
    Select Case Mid(BrowseForFolder, 2, 1)
        Case Is = ":"
            If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid
        Case Is = "\"
            If Not Left(BrowseForFolder, 1) = "\" Then GoTo Invalid
        Case Else
            GoTo Invalid
    End Select
    
    Exit Function
    
Invalid:
    'If it was determined that the selection was invalid, set to False
    BrowseForFolder = False
End Function

Sub ListFilesInFolder(SourceFolderName As String, IncludeSubfolders As Boolean)
              
    If Right(FilesPath, 1) <> "\" Then
        FilesPath = FilesPath & "\"
    End If

    Dim FSO As Scripting.FileSystemObject
    Dim SourceFolder As Scripting.Folder, SubFolder As Scripting.Folder
    Dim FileItem As Scripting.file
    Dim r As Long

    Set FSO = New Scripting.FileSystemObject
     
    Set SourceFolder = FSO.GetFolder(SourceFolderName)
    
    For Each FileItem In SourceFolder.Files
        
        Set iExcel = CreateObject("Excel.Application")
        Let iExcel.Visible = True
        
        iExcel.DisplayAlerts = False
        iExcel.EnableEvents = False
        iExcel.AskToUpdateLinks = False
        
        iExcel.Workbooks.Open (FileItem.Path)
        
        iExcel.Calculation = xlManual
        iExcel.CalculateBeforeSave = False
        
        Dim Sh As Worksheet
        Dim myRange As Range
        Dim ws As Worksheet
    
        For Each Sh In iExcel.Worksheets
            
            On Error Resume Next
            
            Sh.Select
            
            If Sh.Name = "Input-Extra Pyt&Oth Int" Then
                
                Sh.Range("B3:M38").Clear
                
            Else
            
                If Sh.Name = "Input-Cash Trf&Ckg Int" Then
                    
                    Sh.Range("B3:M10").Clear
                    Sh.Range("B14:M16").Clear
                    Sh.Range("B18:M21").Clear
                    Sh.Range("B24:M26").Clear
                    Sh.Range("B29:M31").Clear
                    Sh.Range("B33:M35").Clear
                    Sh.Range("B37:M40").Clear
                    Sh.Range("P3:AA10").Clear
                    Sh.Range("P14:AA16").Clear
                    Sh.Range("P18:AA21").Clear
                    
            
                End If
            
            End If
        
        Next Sh

            iExcel.DisplayAlerts = False
            iExcel.ActiveWorkbook.CheckCompatibility = False
            iExcel.ActiveWorkbook.Close True
            
        Next FileItem

        If IncludeSubfolders Then
            For Each SubFolder In SourceFolder.SubFolders
                ListFilesInFolder SubFolder.Path, True
            Next SubFolder
        End If

        
        iExcel.Application.Quit
           
        Set FileItem = Nothing
        Set SourceFolder = Nothing
        Set FSO = Nothing
        Set iExcel = Nothing
       
End Sub


Report •

#16
January 8, 2016 at 06:15:01
Will do and provide you with feedback.

Report •

#17
January 8, 2016 at 06:39:40
The cells and workbook will be the same all the time. I will need to add more workbooks/sheets/cells that would remain the same all the time. I could add new parameters to code to increase number of cells to clear?
Really, really thankful!!!!

Report •

#18
January 8, 2016 at 06:47:54
You can adapt the code as you wish, it should be pretty clear where to add the sheet name and cells

just add more if statements for the new sheets

if sh.name =....................................................

If Sh.Name = "Input-Extra Pyt&Oth Int" Then
                
                Sh.Range("B3:M38").Clear
                
            Else
            
                If Sh.Name = "Input-Cash Trf&Ckg Int" Then
                    
                    Sh.Range("B3:M10").Clear
                    Sh.Range("B14:M16").Clear
                    Sh.Range("B18:M21").Clear
                    Sh.Range("B24:M26").Clear
                    Sh.Range("B29:M31").Clear
                    Sh.Range("B33:M35").Clear
                    Sh.Range("B37:M40").Clear
                    Sh.Range("P3:AA10").Clear
                    Sh.Range("P14:AA16").Clear
                    Sh.Range("P18:AA21").Clear
                    
            
                End If
            
            End If
        
        Next Sh


What i would do is

create a new folder
copy paste a couple of your workbooks into it
run the code from 'LoadFiles'
select the folder
and see what happens

let us know.


Report •

#19
January 8, 2016 at 10:39:52
Working on it, thanks!

Report •

#20
January 8, 2016 at 13:01:25
I ran code and process ran as if in a loop. Ended with error "Cannot use object linking and embedding". The cells cleared but all formatting were also removed. I cannot seem to re-run code again. Will try again later. Using youtube to better understand VBA so allow some time for me to implement correctly. Will be in touch.

Report •

#21
January 11, 2016 at 00:03:09
instead of '.clear' use '.value = empty'

So you'll end up with something like

Sh.Range("P18:AA21").Value = Empty

This should retain your formatting.


Report •

#22
January 11, 2016 at 19:40:54
Cool thanks I will get to work!!!!

Report •


Ask Question