Help with Macros - How to Copy, Paste-Special-Values, multiple selected cells

[NFlood]

I have a spreadsheet that I am updating information in monthly. Since I keep the monthly history I always copy the most recent row of data and I have to do this for about 50 rows each month. Once that is done I need to copy and paste the formulas in a few different cells (which I keep in a hidden row) for each new row and then I copy, paste-special-values for each of these formulas. As you can guess, this is an extremely tedious task and I know it can be made more efficient. I have created a macro that will copy, paste and highlight, each new row for me (one at a time) but I am wondering if I can select multiple rows and have it perform the copy & paste function for each selected row. Here is my current macro:

Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 13434879
.TintAndShade = 0
.PatternTintAndShade = 0
End With



Similarly, is there a way for me to select each cell that I have copied my formula in to and use a macro that will perform the Copy & Paste-special-values functions, to all selected cells instead of having to perform the task one cell at a time?

 

[pbornemeier]

This post has implications for your problem: http://www.mrexcel.com/forum/excel-...as-values-non-adjacent-cells.html#post3931515

Q1. What columns contain the new data?
Q2. Are all of the new data rows adjacent?
Q3. What row/columns (the hidden row) contain the formulas that you copy to the rows with new data?
Q4. Are the hidden formulas copied to the corresponding columns in the new rows (A to A, K to K, Q to Q, etc.)
Q4a. If not, what is the mapping? (A to A, B to K, C to Q, etc.)