Inserting a column to the right of the currently selected and formatted column, including formatting
I've got a series of spreadsheets (see example attached) into which I need to insert YTD columns. I've written a subroutine (Insert_YTD_Columns) which starts at column 5, then shifts one column to the right and inserts a new column. This results in the new column having the same formatting as the currently selected column. When I get to the last column, the new column doesn't have the formatting of the columns to the left.
Is there a way, rather than inserting a colum to the left of the currently selected column to insert the column to the right, which will retain the same formatting? I know I can select column 5, copy it, shift to column 6, then use paste special to paste the formatting from column 5 into 6, but is there a more elegant method?
BTW, I know I could use the FillDown method to copy the formula from the top row of each of the YTD columns, but am not doing so because of this same formatting issue.
EE-InsertColumn.xlsm
Is there a way, rather than inserting a colum to the left of the currently selected column to insert the column to the right, which will retain the same formatting? I know I can select column 5, copy it, shift to column 6, then use paste special to paste the formatting from column 5 into 6, but is there a more elegant method?
BTW, I know I could use the FillDown method to copy the formula from the top row of each of the YTD columns, but am not doing so because of this same formatting issue.
EE-InsertColumn.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thomas,
Had to revise my code a bit and am now inserting in the current column. Then setting the formula for that new column to refer to the column to the right (and left if intCol > 5)
If intCol = 5 Then
sht.Range(Cells(6, 5), Cells(intRow, 5)).FormulaR1C1 = "=RC[1]"
Else
sht.Range(Cells(6, intCol), Cells(intRow, intCol)).FormulaR1C1 = "=RC[1] - RC[-1]"
End If
But column intCol + 1 contains some rows that are blank (for spacing). I tried to modify row 28 from your code so that it would create these same blanks in intCol as are in intCol + 1, but cannot get the syntax right. Care to address this as a continuation of this question, or should I create a new one.
sht.Range(Cells(6, intCol), Cells(intRow, intCol)).SpecialCells(xlCe
sht.Range(Cells(6, intCol + 1), Cells(intRow, intCol + 1)).SpecialCells(xlCellTyp
Had to revise my code a bit and am now inserting in the current column. Then setting the formula for that new column to refer to the column to the right (and left if intCol > 5)
If intCol = 5 Then
sht.Range(Cells(6, 5), Cells(intRow, 5)).FormulaR1C1 = "=RC[1]"
Else
sht.Range(Cells(6, intCol), Cells(intRow, intCol)).FormulaR1C1 = "=RC[1] - RC[-1]"
End If
But column intCol + 1 contains some rows that are blank (for spacing). I tried to modify row 28 from your code so that it would create these same blanks in intCol as are in intCol + 1, but cannot get the syntax right. Care to address this as a continuation of this question, or should I create a new one.
sht.Range(Cells(6, intCol), Cells(intRow, intCol)).SpecialCells(xlCe
sht.Range(Cells(6, intCol + 1), Cells(intRow, intCol + 1)).SpecialCells(xlCellTyp
Easier to create a new one. I might not get to this for a bit, so you'll get a faster answer that way (from other experts). If you have time, you can wait and I'll address it here. I have the request flagged in my mailbox.
T
T
ASKER
Thanks for the quick feedback.
The two lines(33 and 34) were what I really needed, but I particularly like the subTotal function and am going to have to play with that as I format the subtotal rows on that worksheet (and others), which is currently in another subroutine.