Need VBA Help for Dashboard!!!

[liquidmettle]

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

 

[sergioMabres]

I think I can help you
Your macro is very simple to do but your ranges are terribly complicated so the macro if recorded is very complicated
Did you know that you can create range names that contains different non continuous sub ranges?
Do you know how to create them? You select the first sub range, pres control key and select the next sub range, and continue with selecting using control key and mouse then write a name in the names box (left of edit box) and press enter
If you do that for your range1, range2, ... to range4 I can write you a 8 lines macro
Cheers
Sergio

 

[liquidmettle]

Hey Sergio that would be awesome!

Yes, I know how to create named ranges and could easily do so for the ranges (oh the tedium though! haha).

I won't be able to create them until tomorrow but I can tell you the names I'll use now. Year1, Year2, Year3, Year4 (I'll match these to the 1,2,3,4 sheet names so its all very intuitive for both myself and you).

Let me know if you need any other information.

Cheers,

-LM

 

[liquidmettle]

Hey Sergio,

Based on what you've said I've tried my hand at this but haven't been successful yet. This is what I've tried but I'm afraid my VBA knowledge is too insufficient for figuring out where its going wrong (or how to interpret the error).

Sub Update()

Range(PatientClinics.xlsm!Year1).Select
Selection.ClearContents
Range(PatientClinics.xlsm!Year2).Copy
Range(PatientClinics.xlsm!Year1).Paste
Range(PatientClinics.xlsm!Year2).Select
Selection.ClearContents
Range(PatientClinics.xlsm!Year3).Copy
Range(PatientClinics.xlsm!Year2).Paste
Range(PatientClinics.xlsm!Year3).Select
Selection.ClearContents
Range(PatientClinics.xlsm!Year4).Copy
Range(PatientClinics.xlsm!Year3).Paste
Range(PatientClinics.xlsm!Year4).Select
Selection.ClearContents
Range("P14").Select

End Sub


Where Patient Clinics is the name of Excel File and Years1,2,3,4 are my named ranges covering the data ranges in sheets 1,2,3,4.

 

[sergioMabres]

Well is not as you think is a little tricky, I have use as I advices you range1, range2, range3, and range4, you can test the macro in this test workbook:
https://dl.dropboxusercontent.com/u/23094164/liquidmettle1.xlsm
Here is the code tha I used to copy the values of cells as you explained:

Sub moveforward()
'
' Copies range2 in 1, 3 in 2, 4 in 3 and erases 4
' Ranges are not rectangular and non continuos
'
    Dim i, j, k As Integer
    For k = 2 To 4
        For i = 1 To Range("range" & (k)).Areas.Count
            For j = 1 To Range("range" & (k)).Areas(i).Cells.Count
                ' Copies cell by cell, and as long as the ranges
                ' are similar (same shape and size) ther is not a problem
                Range("range" & (k - 1)).Areas(i)(j).Cells(1, 1) = Range("range" & (k)).Areas(i)(j).Cells(1, 1)
            Next j
        Next i
    Next k
    ' Erase sheet 4
    Range("range4").ClearContents
End Sub

I hope you can understand and use the macro, read the comments it is using range names and the fact that the ranges are similar in shape and sizes.
Cheers
Sergio

 

[liquidmettle]

Hello Sergio!

First of all, thank you for helping me out here. Your code does exactly what I want it to, but I can only get it to work on your test sheet.

Can you or anyone else possibly help me apply your VBA code to an actual test file of the dashboard I'm putting together?

https://docs.google.com/file/d/0B2iab060YIUWbzJCWVJ0ZjYwMzQ/edit?usp=sharing

There are navigation menus on the left, or just use the tabs at the bottom. The sheets I need the code to apply to are 1,2,3,4.

What I ultimately want is for someone to be able to hit the Update button on the update sheet and this macro execute to transfer user entered data as I outlined earlier.

No matter what I try I cannot make the data copy from one sheet to the next as it works in your test file.

Right now I have 4 named ranges like you have and each covers the range I initially laid out in my first post.

Can anyone provide further help?

Thank you all so much for your time,

-LM

 

[sergioMabres]

Hi liquid I downloaded the test sheet I created the ranges named range1 to range4, copied the macro and it works fine now, I notice that you used some rages names as data1 to data4 and the macro needs the ranges to be named range1 to range4, the test sheet with the working macro is at
https://dl.dropboxusercontent.com/u/23094164/LiquidMettleHelp2.xlsm
Cheers
Sergio

 

[liquidmettle]

Sergio! That's fantastic!

I did have my ranges named as yours but I changed them again when I started trying to figure out the code myself. Thank you so much for your help with this!

Amazing : ).

 

[sergioMabres]

Always glad to help
Sergio