Using VBA to duplicate *everything* about a set of cells in Excel 2010

[lewist57]

I have a spreadsheet that has a column of cells per week. Each of the cells have borders, formulas, conditional formatting, etc.

I would like to use VBA to be able to duplicate the original set of cells in the original column as many times as desired. For example, if the spreadsheet starts out with the first column cells set up for Week 1, and I want 10 weeks of columns, how do I have Excel duplicate the formulas, borders, conditional formatting, etc., nine more times to the right of the original column?

Thanks in advance for your input.

[dangelor]

Possibly...

Please Login or Register  to view this content.

[lewist57]

Possibly...

Please Login or Register  to view this content.

Excellent start. Now what would the VBA code look like that would allow the user to input the number of weeks to duplicate in cell A1, and automaatically duplicate the original column by the number in A1?

[mike7952]

Upload a workbook, showing the exact data structure that you are working with. Show the expected output that you are wanting. Data don't have be real data just so the data structure is exactly what you are working with.

[dangelor]

Please Login or Register  to view this content.

[Maudibe]

Consider the following code to add to your project. When the workbook opens, an initial cell address will be stored in the ControlTextTip of a textbox on an unused form for retrieval. It will then create a copy of the first sheet (worksheet that will be monitored) as a very hidden second worksheet called "Backup" for comparison to determine if changes were made.

When a new cell is selected, the code pulls the old cell address from the textbox ControlTextTip and compares that cell address from the active sheet against the same cell on the Backup sheet. If they are different, it writes the original and changed cell values as well as the original and changed interior colors to the Results page. The new target cell address will then be written to the Textbox ControlTextTip and stored as the new old cell address when the process repeats for the next selection.

When the workbook is closed, the Backup sheet is deleted and the workbook is saved. The code is generic with the main sheet as it does not matter what the name is as long as it is the first sheet . Monitoring of additional formatting types can easily be added to the code

HTH,
Maud

ThisWorkbook module:

Please Login or Register  to view this content.

Worksheet module:

Please Login or Register  to view this content.

[Logit]

Maudibe:

Question please ... I ran your macros and the color or number written to Results Sheet is 16777215

This is in Col D & E corresponding to :

.Cells(NextRow, 4) = Worksheets("Backup").Range(addr).Interior.Color 'ORIG COLOR
.Cells(NextRow, 5) = Range(addr).Interior.Color 'CHANGED COLOR


Does that number represent the color ? or what ??

Checking the RGB of 167 77 215 it comes a cross of purple/violet

[Maudibe]

Hi Logit

It is not an RGB color value. It is the long value representation of color Index of 2

Colors_Long Values.png

The workbook I posted was only intended to offer some ideas that Lewist can incorporate into his/her project

[lewist57]

Please Login or Register  to view this content.

This was the most simple code and perfectly duplicates the columns, all text, borders, colors, formulas and conditional formatting perfectly. Many thanks for everyone's input.