Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Inserting a column to the right of the currently selected and formatted column, including formatting

Posted on 2011-09-16
4
Medium Priority
?
265 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:Dale Fye
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 39

Accepted Solution

by:
nutsch earned 2000 total points
ID: 36553172
That's actually a good way to do it. Here is my take on your code with that.

Thomas
Option Explicit

Public Sub Insert_YTD_Columns()
    
    Dim sht As Worksheet
    Dim intCol As Integer, intRow As Integer, intLoop As Integer
    Dim strYTDRange As String
    
    Set sht = ActiveSheet
    
    'Loop and insert columns
    intCol = 5
    intRow = sht.Range("B500").End(xlUp).Row
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.DisplayAlerts = False
    
    While sht.Cells(5, intCol) <> ""
    
        sht.Columns(intCol + 1).Insert Shift:=xlToRight
        sht.Cells(4, intCol + 1) = sht.Cells(4, intCol)
        sht.Cells(4, intCol + 1).NumberFormat = "m/d/yy;@"
        sht.Cells(5, intCol + 1) = "YTD Balance"
        sht.Cells(5, intCol) = "Activity"
                
        sht.Range(Cells(6, intCol + 1), Cells(intRow, intCol + 1)).FormulaR1C1 = "=SUbtotal(9,RC5:RC[-1])"
        sht.Range(Cells(6, intCol), Cells(intRow, intCol)).SpecialCells(xlCellTypeBlanks).Offset(, 1).ClearContents
                    
        intCol = intCol + 2
    Wend
    
    Cells(5, Columns.Count).End(xlToLeft).Offset(, -1).EntireColumn.Copy
    Cells(5, Columns.Count).End(xlToLeft).EntireColumn.PasteSpecial Paste:=xlPasteFormats
    
    'Unhide all of the columns in the active sheet
    sht.Range(sht.Columns(5), sht.Columns(intCol - 1)).EntireColumn.Hidden = False
    
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.DisplayAlerts = True
    
End Sub

Open in new window

0
 
LVL 48

Author Closing Comment

by:Dale Fye
ID: 36554514
Thomas,

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.
0
 
LVL 48

Author Comment

by:Dale Fye
ID: 36767381
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(xlCellTypeBlanks).Offset(, 1).ClearContents



sht.Range(Cells(6, intCol + 1), Cells(intRow, intCol + 1)).SpecialCells(xlCellTypeBlanks).Offset(, -11).ClearContents
0
 
LVL 39

Expert Comment

by:nutsch
ID: 36813011
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
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question