Help Understanding Some Code

[AAnderson4912]

I got some help with a code the other day and I'm trying to understand how it works. I started making some notes for myself but got confused after a certain point. Could someone please try to explain in layman's terms what the following code does?

Please Login or Register  to view this content.

I've also attached the example workbook so you can see the context.

Thanks very much!

[sintek]

Why don't you just as the coder Marc L to comment the code for you...Provided here...

[Richard Buttrey]

I started explaining - see below but an confused by what do you expect the outcome to be? As far as I can see that Inner With..End With block doesn't change anything? AT least provided the macro is run with the Summary sheet being the active sheet. If you run it with TopSheet1 being active then the sheet gets screwed up,


'Used Range.Offset(1) is commented out. UsedRange operates on whichever sheet is active or being referenced in code and is the whole of the data from A1 to the cell in
'the last row and last column. The Offset(1) simply changes that range from A1:?? to B1:?? +1. Usually done to avoid deleting the first row of column labels

'R& is a variable. It's good practice to specify the type of variable by including some reference in the variable name. The & is just a shortcut and means a Long variable
'get into the habit of declaring stuff like Dim stMyName As String, lRow As Long,

'The For Loop uses the variable S& as counter and loops from 2 to the number of sheets in the workbook, i.e. the .Count bit. Be careful though using this Sheet index since it refers
'to the order of the Tab names. If someone were to move a sheet then the macro would be compromised. I think it's better to loop through all sheets and onclude an IF test to exclude
'sheets that don't meet a particular criteria. That could be the first 3 letters of the sheet name, i.e. "Top", or some other common value in each sheet.

'The Range Rf is set to be the cell in column B (the 2nd column) of the TopSHeet where the first characters of the cell start with "Name". i.e. B37

'The .Range("B9".... line does an {End}{Down} on B8 and finds B17. The Range B9:B17 is then resized. The comma in the Resize(,C) keeps the number of rows the
'same i.e. 9:17 but extends the columns by the value of the C constant of 12. The range is then B9:M17

'with the BM9:BM17 ....then what???????????? see opening remarks

[AAnderson4912]

Thanks for your help, Richard Buttrey. Apologies, I didn't realize the code was still located in the module. I thought I had corrected that to only live on the Summary sheet to avoid exactly what you mentioned.

As for what I'm actually trying to do, I have a bunch of these timesheet summaries pages from supervisors (referred to as the Top Sheet). I have combined all the top sheets into one workbook and then am using the Summary sheet to extract the relevant data from all the other sheets so I can sum and sort and more easily reconcile with the actual payroll data. I'm working with past data, so I cannot change the format of the top sheets, otherwise I'd use consolidate or power query or something like that.

Does that make sense?

So what I'm trying to understand now is once the info is found on the top sheet, how does it make it onto the summary sheet? Also, when it moves onto the next sheet, how does it know not to overwrite the data it just extracted from the previous top sheet?

[Marc L]

Used Range.Offset(1) is commented out. UsedRange operates on whichever sheet is active

Hi Richard,

as you can see in the original thread (link in Sintek's post …)
this VBA procedure must be located in a specific worksheet module
so any range without any worksheet reference refers to this specific worksheet like UsedRange
whatever the active sheet !

[Marc L]

how does it know not to overwrite the data it just extracted from the previous top sheet?

Just according to the row index variable R …

[AAnderson4912]

I have to admit I'm still not fully understanding. Maybe it would help me to compare with a similar situation. I have another workbook of timesheets for individuals and like the previous example, I want to extract the following data from each timesheet:

• Employee Name
• Period Beginning
• Period Ending
• Pay Date
• Regular Hrs
• OT Hrs
• PTO Hrs
• Vacation Hrs
• Other Hrs
• Total Hrs

And then copy that to the Summary page. How would you approach this example? (See attachment)

[Richard Buttrey]

Do the timesheets contain ONLY NEW Data that must be added to the summary?
Does the stuff already on the Summary sheet need to be kept.

Sometimes it's simpler just to zap a summary and refresh it from scratch. If you can clarify this aspect then it should be asimple task to copy everything on the timesheets and recreate the summary.

[sintek]

Easy enough to understand...Data set no to big to justify storing all in Array to pass at end...

Please Login or Register  to view this content.

[Marc L]

How would you approach this example? (See attachment)

Exactly the same way like in your previous thread : logically obviously with a row index variable …

According to your post #7 attachment you can delete the useless VBA Module1
then paste this new VBA demonstration to the Sheet1 (Summary) worksheet module :

Sub Demo2() 
        Dim R& 
        UsedRange.Offset(1).Clear 
    With Worksheets 
        With Rows("2:" & .Count).Columns 
            .Item("B:D").NumberFormat = "m/d/yyyy" 
            .Item("E:J").NumberFormat = "0.00" 
        End With 
        For R = 2 To .Count 
            With .Item(R) 
                Cells(R, 1).Value2 = .[C2].Value2 
                Rows(R).Columns("B:D").Value2 = .[TRANSPOSE(J2:J4)] 
                Rows(R).Columns("E:J").Value2 = .[TRANSPOSE(J32:J37)] 
            End With 
        Next 
    End With 
End Sub  

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

[AAnderson4912]

Thank you all for your help! I think especially seeing the same problem approached in 2 different ways has been very informative