Apologies for the uninformed question but I've been looking into macros all morning to help me with a task I'm completing and I haven't found the answers that I need by searching google. I'm using windows 10 and excel 2016. Here's the macro I recorded:
Sub biweekly() ' ' biweekly Macro ' ' Keyboard Shortcut: Ctrl+w ' Rows("53:53").Select Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Range("A52:C52").Select Selection.Cut Destination:=Range("A53:C53") Range("M52").Select Selection.Cut Destination:=Range("M53") Range("D53").Select ActiveCell.FormulaR1C1 = "=R[-2]C+R[-1]C" Range("D53").Select Selection.Copy Range("E53:L53").Select ActiveSheet.Paste Range("D53:L53").Select Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Rows("51:52").Select Application.CutCopyMode = False Selection.Delete Shift:=xlUp Range("A54").Select End Sub
Essentially, I'm combining weekly rows into biweekly rows. I've done this before using pivot tables and getpivotdata but I was hoping for a more elegant solution this time. I would like the macro to loop once it's finished, moving down 3 rows to work on the next pair of weeks afterward (so, in this example, if the new row I created is now row 51 after deleting the individual weekly rows, I would like to move down to row 54 and insert a new row above it to begin working on the week row pairs of 52 and 53).
I don't know how to loop the macro so that it jumps down rows in each loop. I also would like to make a macro stop running conditionally, like when the value in column A changes (I have a large worksheet sorted alphabetically with names containing years of biweekly pay data, and I would like the macro to stop at the end of each name so that I can adjust the pay period concatenation and run it again for the new name). Alternatively, knowing how to make it run a certain number of times (say, 30) would be a good alternative.
Any help at all on any of these issues would be greatly appreciated. Again, apologies for being unfamiliar with macros and VBE.