Hello all,
I have been a lurking reader for a long time, and have been able to solve many of my excel questions/issues/problems using advice posted on excelforum. I'm hopeful that some members can chime in to help me solve my current problem!
Scenario: I have a worksheet that utilizes a lot of lookup formulas to sort data in various ways, due to the sheet amount of formulas and data, this workbook operates very slowly. I'm re-writing the workbook to hopefully catalog historical data as hardcoded to reduce the number of indexing formulas active at a given time.
Problem: I've got a sheet with multiple different tables stacked down the rows (for illustration, table 1 might take up range (C5:P23), table 2 (C28:P37), table 3 (C42:P65), etc. The tables are sorting data for different monthly reporting criteria, but all of the tables in the column range (C:P) are for the month of January, with additional months cataloged horizontally to the right. At the end of January, I would like a macro to copy the various tables and paste (formulas and formatting) into the column ranges (R:AE), row numbers will not change, and then to paste the January tables (columns C:P) as values, and change the font color.
My problem is that I'm not sure how to make the column references variable. I.e. after February month end, to now copy the February month (columns R:AE) and paste into March columns (AG:AT), and have this process repeat every month by the click of a macro.
Please let me know if you have any additional questions or if any additional clarification is necessary.
Thank you in advance!
Bookmarks