Help in Understanding what this VBA code is actually saying?

[liquidmettle]

Hello Everyone,

I don't know if I am violating forum rules with this post, so if I am, please let me know and moderators act accordingly.

Recently I posted a topic called http://www.mrexcel.com/forum/excel-...-applications-help-dashboard.html#post3504562.

SergioMabres gave me an excellent answer but I want to understand exactly what his VBA code is telling me (in as plain of english as I can) so that I can reverse engineer it to work in the spreadsheets I need to at work.

1st:

Sergio's test file: https://dl.dropboxusercontent.com/u/23094164/liquidmettle1.xlsm

This file's macro does exactly what I need it to do. I.E. Copy a set of named ranges across various sheets so that a layman can update a data tracking dashboard without tedious re-entry of past data.

2nd:The Macro code:


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 values 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


In the dashboard for work, I have 4 named ranges covering this non-contiguous range (the reason I don't just highlight the whole block between m17:x134 is that the rows in-between have formulas that I cannot have erased):

M17:X18, M21:X22, M25:X26, M29:X30, M33:X34, M37:X38, M41:X42, M45:X46, M49:X50, M53:X54, M57:X58, M61:X62, M65:X66,M69:X70, M73:X74, M77:X78, M81:X82, M85:X86, M89:X90, M93:X94, M97:X98, M101:X102, M105:X106, M109:X110, M113:X114, M117:X118, M121:X122, M125:X126, M129:X130, M133:X134.

The range listed above are the same in all 4 of the sheets (no variation on location), and I made one Named Range per sheet (by control selecting the ranges listed above). My sheet names, as in Sergio's test file, are the same, being 1,2,3, and 4.

His named ranges (which I could change mine to mimic) are Range 1, Range 2, Range 3, and Range 4.

Mind you Sergio's code does what I want in his test file. But I have no idea what the line "Dim i, j, k As integer" is actually saying, and the rest seems to go into worse comprehension from there.

Can anyone walk me through this in plain English so that I may be able to apply this macro to the more complex dashboard I am developing at work?

Thank you all so much,

-LM

 

[AlphaFrog]

May I inquire why didn't you ask this as a follow up question in the original thread?

Also, I'm sure Sergio would appreciate knowing if his code worked for you and maybe an expression of appreciation. It looks like there has been no reply after he took the time to post this code. Kinda' left him hangin'.

Here's another method. It doesn't use named ranges. Line notes are included.

[COLOR=darkblue]Sub[/COLOR] moveforward2()
    
    [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]                        [COLOR=green]'Define variable i as an integer[/COLOR]
    
    Sheets("1").Name = "Temp"               [COLOR=green]'Rename sheet 1 as "Temp" name (temporary)[/COLOR]
    [COLOR=darkblue]For[/COLOR] i = 2 [COLOR=darkblue]To[/COLOR] 4                          [COLOR=green]'[/COLOR]
        Sheets(i).Name = i - 1              [COLOR=green]'Rename sheets 2,3,4 as 1,2,3[/COLOR]
    [COLOR=darkblue]Next[/COLOR] i                                  [COLOR=green]'[/COLOR]
    Sheets("Temp").Move After:=Sheets("3")  [COLOR=green]'Move "Temp" sheet after sheet 3[/COLOR]
    Sheets("temp").Name = "4"               [COLOR=green]'Rename sheet "Temp" as sheet "4"[/COLOR]
    
    [COLOR=green]'Clear constants (not formulas) in range M17:X134 on Sheet "4"[/COLOR]
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
    Sheets("4").Range("M17:X134").SpecialCells(xlCellTypeConstants).ClearContents
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[liquidmettle]

Hello AlphaFrog,

To answer your question, there was a private message exchange between myself and Sergio (in the first I asked if he could take a look at my thread again and see if he knew the VBA to achieve my ends- this preceded his very excellent answer- and the second thanking him for his time and knowledge).

The reason I posted this as a new topic is that it does not appear (I may be very wrong) that topic's are bumped in this forum when a member replies to them. My original sank to page 4 before Sergio so graciously replied and I thought that if I asked this question there it might get lost in the dregs of the back pages.

Sorry .

I will try your VBA code when I get a chance and thank you for your prompt reply.

One question about your code: does it simply re-arrange the sheets or does it move just those sections from sheet to sheet? I ask because the way the sheets are set up, if you enter your current year in a sheet (other than 1,2,3,4), it updates those 4 sheets with proper year headings above the data columns. Just moving the sheet's positions would not work as a solution (if I read your notes correctly. I may be very wrong).

In any case, thank you so much for replying and I apologize again for the multiple postings.

-LM

 

[AlphaFrog]

A reply to a thread should bump the thread. Occasionally there are glitches though.

My code just renames and rearranges the sheets. Given your criteria (I really didn't follow what it meant), it's probably not going to work for you.