liquidmettle
New Member
- Joined
- May 23, 2013
- Messages
- 48
Hello Everyone,
I am building a dashboard but I need help with a macro.
What I need the macro to do:
I need the macro to erase data from a specific range in "1", then copy the data from "2" into "1." Then erase the data from "2" and copy "3's" data into "2." Then copy the data from "4" into "3" and then erase "4's" data leaving those ranges blank. (Where "1", "2", "3", and "4" are my actual sheet names, only without the quotes). There is further explanation below.
I've already recorded a macro manually that will erase data in a single sheet from the ranges I require.
The code excel made was:
Range("M17:X18").Select
Selection.ClearContents
Range("M21:X22").Select
Selection.ClearContents
Range("M25:X26").Select
Selection.ClearContents
Range("M29:X30").Select
Selection.ClearContents
Range("M33:X34").Select
Selection.ClearContents
Range("M37:X38").Select
Selection.ClearContents
Range("M41:X42").Select
Selection.ClearContents
Range("M45:X46").Select
Selection.ClearContents
Range("M49:X50").Select
Selection.ClearContents
Range("M53:X54").Select
Selection.ClearContents
Range("M57:X58").Select
Selection.ClearContents
Range("M61:X62").Select
Selection.ClearContents
Range("M65:X66").Select
Selection.ClearContents
Range("M69:X70").Select
Selection.ClearContents
Range("M73:X74").Select
Selection.ClearContents
Range("M77:X78").Select
Selection.ClearContents
Range("M81:X82").Select
Selection.ClearContents
Range("M85:X86").Select
Selection.ClearContents
Range("M89:X90").Select
Selection.ClearContents
Range("M93:X94").Select
Selection.ClearContents
Range("M97:X98").Select
Selection.ClearContents
Range("M101:X102").Select
Selection.ClearContents
Range("M105:X106").Select
Selection.ClearContents
Range("M109:X110").Select
Selection.ClearContents
Range("M113:X114").Select
Selection.ClearContents
Range("M117:X118").Select
Selection.ClearContents
Range("M121:X122").Select
Selection.ClearContents
Range("M125:X126").Select
Selection.ClearContents
Range("M129:X130").Select
Selection.ClearContents
Range("M133:X134").Select
Selection.ClearContents
Range("k15").Select
Basically I'm looking for the data to shift over across sheets. Sheets 1-4 cover a 4 year span with Sheet 4 being the current year. Once we enter into a new year, I want the data to shift (when the user presses an update button- I know how to get the button to activate the macro) so that the current year is left blank and the previous years data shifts accordingly.
This will avoid copy and paste headaches as well as having to reenter data as the previous oldest year is no longer part of the spreadsheet.
I would like this to work how I have described because there are many people who will use this dashboard who are not excel savvy, or even very computer savvy.
Any help would be appreciated,.
-LM
I am building a dashboard but I need help with a macro.
What I need the macro to do:
I need the macro to erase data from a specific range in "1", then copy the data from "2" into "1." Then erase the data from "2" and copy "3's" data into "2." Then copy the data from "4" into "3" and then erase "4's" data leaving those ranges blank. (Where "1", "2", "3", and "4" are my actual sheet names, only without the quotes). There is further explanation below.
I've already recorded a macro manually that will erase data in a single sheet from the ranges I require.
The code excel made was:
Range("M17:X18").Select
Selection.ClearContents
Range("M21:X22").Select
Selection.ClearContents
Range("M25:X26").Select
Selection.ClearContents
Range("M29:X30").Select
Selection.ClearContents
Range("M33:X34").Select
Selection.ClearContents
Range("M37:X38").Select
Selection.ClearContents
Range("M41:X42").Select
Selection.ClearContents
Range("M45:X46").Select
Selection.ClearContents
Range("M49:X50").Select
Selection.ClearContents
Range("M53:X54").Select
Selection.ClearContents
Range("M57:X58").Select
Selection.ClearContents
Range("M61:X62").Select
Selection.ClearContents
Range("M65:X66").Select
Selection.ClearContents
Range("M69:X70").Select
Selection.ClearContents
Range("M73:X74").Select
Selection.ClearContents
Range("M77:X78").Select
Selection.ClearContents
Range("M81:X82").Select
Selection.ClearContents
Range("M85:X86").Select
Selection.ClearContents
Range("M89:X90").Select
Selection.ClearContents
Range("M93:X94").Select
Selection.ClearContents
Range("M97:X98").Select
Selection.ClearContents
Range("M101:X102").Select
Selection.ClearContents
Range("M105:X106").Select
Selection.ClearContents
Range("M109:X110").Select
Selection.ClearContents
Range("M113:X114").Select
Selection.ClearContents
Range("M117:X118").Select
Selection.ClearContents
Range("M121:X122").Select
Selection.ClearContents
Range("M125:X126").Select
Selection.ClearContents
Range("M129:X130").Select
Selection.ClearContents
Range("M133:X134").Select
Selection.ClearContents
Range("k15").Select
Basically I'm looking for the data to shift over across sheets. Sheets 1-4 cover a 4 year span with Sheet 4 being the current year. Once we enter into a new year, I want the data to shift (when the user presses an update button- I know how to get the button to activate the macro) so that the current year is left blank and the previous years data shifts accordingly.
This will avoid copy and paste headaches as well as having to reenter data as the previous oldest year is no longer part of the spreadsheet.
I would like this to work how I have described because there are many people who will use this dashboard who are not excel savvy, or even very computer savvy.
Any help would be appreciated,.
-LM