Macro to Insert Specified Number of Columns

[JoselynW]

Thank you in advance.

I have a worksheet that I use to audit office locations for a specific organization. When I use it, a compnay may have 3 offices or 100 offices in the audit. I have a title page where I input data. My master file would have an additional worksheet with 1 office and all the necessary calculations. I would like to be able to input the number of offices into a cell on the title worksheet, and have it populate the proper number of columns based on the #of offices input on the title page and then copy calculations from Office #1.

Can anyone help? Please! I'm on a deadline and just can't figure it out. I really appreciate your time and assistance!

 

[lenze]

OK: This will get the Columns inserted and formatted!!

Sub InsertCol()
x = InputBox("Number of Offices")
Range("$D$2").Resize(1, x).EntireColumn.Insert
Range("$C$4:$C" & Cells(Rows.Count, "C").End(xlUp).Row).Copy
Cells(4, 4).Resize(1, x).PasteSpecial xlPasteFormats
End Sub

I need to think about updating the formulas. You may need to switch to R1C1 formulas!!
lenze

 

[JoselynW]

Lenze - that worked for the formatting! You rock! Ok, I'll look into the formula thing some more too. I'll let you know if I find anything.

Thank you!
Joselyn

 

[lenze]

Working on the formula thing. I solicited some help!!
BTW: Please do be a favor and DUMP the Merged Cells in Row 1. Use "Center Across Selection" instead. Merged Cells and VBA do not get along!!
http://www.officearticles.com/excel/merged_cells_in_microsoft_excel.htm

lenze

 

[JoselynW]

I sent you a new spreadsheet. I removed all the merged cells per your request. I will always use that going forward. Much better!

 

[lenze]

OK: Joe4 gave me an idea and I played around with it!!
This is what I came up with and it seems to work, so give it a try!

Sub InsertCol()
x = InputBox("Number of Offices")
LR = Cells(Rows.Count, "C").End(xlUp).Row
Range("$C$2").Resize(1, x).EntireColumn.Insert
Cells(3, x + 3).Resize(LR - 2, 1).Copy Cells(3, 3)
Cells(4, x + 2).Resize(LR - 3, 1).Copy
Cells(4, 4).Resize(, x).PasteSpecial xlPasteFormats
Cells(3, x + 3).Resize(LR - 2, 1).ClearContents
End Sub

For the formula to adjust, you have to insert between Columns "B" and "C" then Move the original "C" back
lenze

 

[JoselynW]

Brilliant! It works perfect. Thank you so much!!!!! You are a life saver. Now I have my template for the first 2 pages, now I just need the rest of the worksheets update when I enter those columns.

Again, thank you for all your time and energy on this.

Take care-
Joselyn

 

[lenze]

My friend PeterSSs pointed out that since we are Inserting to the Left, the formatting should be inserted also, and he is correct. You can remove the lines in blue and it still works.

Sub InsertCol()
x = InputBox("Number of Offices")
LR = Cells(Rows.Count, "C").End(xlUp).Row
Range("$C$2").Resize(1, x).EntireColumn.Insert
Cells(3, x + 3).Resize(LR - 2, 1).Copy Cells(3, 3)
Cells(4, x + 2).Resize(LR - 3, 1).Copy
Cells(4, 4).Resize(, x).PasteSpecial xlPasteFormats
Cells(3, x + 3).Resize(LR - 2, 1).ClearContents
End Sub

lenze

 

[JoselynW]

Great. Thank you for the update. Again, appreciate all the time and energy to make this work!

Joselyn