Add New Column names using Arrays to the existing columns using VBA

[Jonnathanjons]

Hello, How can I add a list of new Column names to an existing columns in the report and add a new color to the new column range added. I would like to have the new columns created at the right end of report. I would also like to have a color highlighted for the existing column range and another color for the new column range. It will be great if it can be achieved in 1 or 2 lines possible. Here is the code I would like to work on. You may provide a better code if possible

Sub InsertHeaders()

Sheets("CONSOLIDATED").Cells(1).Resize(1, 6).Value = Array("Fiscal Year", "Month", "Month_Year", "Project", "Local Expense", "Base Expense")

End Sub

I tried below code but showing error.

With Ws.UsedRange.Columns.Cells(1, .Count + 1).value = Array("Fiscal Year", "Month", "Month_Year", "Project", "Local Expense", "Base Expense")
End With

 

[p45cal]

Not at all clear on where you want this, so a guess (this puts it at the bottom of the usedrange):

With Ws.UsedRange
  .Cells(1).Offset(.Rows.Count).Resize(, 6).Value = Array("Fiscal Year", "Month", "Month_Year", "Project", "Local Expense", "Base Expense")
End With

or at the top to the right:

With Ws.UsedRange
  .Cells(1).Offset(, .Columns.Count).Resize(, 6).Value = Array("Fiscal Year", "Month", "Month_Year", "Project", "Local Expense", "Base Expense")
End With

As for colour highlighting, record a macro and post it here; perhaps it can be tweaked. Is it just the headers which need colour? or the whole columns of the usedrange?

 

[Keetoowah]

Hi Jonnathanjons
I agree with p45cal that your question is not very clear, here is my attempt, assuming that the initial number of columns can be variable

Sub test()
'https://chandoo.org/forum/threads/add-new-column-names-using-arrays-to-the-existing-columns-using-vba.57737/


    Dim x As Integer
    Dim MyRng As Range, AddedRng As Range


    x = ActiveSheet.UsedRange.Columns.Count + 1
   
    Set MyRng = ActiveSheet.UsedRange
   
    ActiveSheet.Cells(1, x).Resize(1, 6).Value = Array("Fiscal Year", "Month", "Month_Year", "Project", "Local Expense", "Base Expense")
   
    Set AddedRng = ActiveSheet.Cells(1, x).Resize(ActiveSheet.UsedRange.Rows.Count, 6)
   
    MyRng.Interior.Color = RGB(0, 200, 0)
   
    AddedRng.Interior.Color = RGB(0, 100, 255)


End Sub

 

[Jonnathanjons]

Apologies for not making it clear .These scripts are for adding new header columns names which I wanted to add to the top right side of the report after existing columns when I wanted to do some analysis of some reports exported to xlsx sheet. The color format is ONLY for the header column names and not for the entire report.

Example column names of the existing report if are Emp id,Location,Monthly Salary,Dept ., by running the script I should be able to see
Emp id,Location,Monthly Salary,Dept, Fiscal Year,Month,Month Year,Project,Local Expense,Base Expense..and I wanted the existing column header to have a color and new column header to have a separate color to show that these are the analysis based on the existing columns. Hope I am making sense.
So the script should be at the beginning of running any other scripts to have the columns first added before running further scripts.
I currently use

With wsED.UsedRange.Columns
.Rows(1).Interior.Color = RGB(252, 228, 214) ' for existing column header

.Cells(.Count).Offset(0, 1).Resize(, 1).Interior.Color = RGB(191, 191, 191) ' For new column headers

I currently created macros which only create each new column separately and format them after its created for every new columns like below but found that Arrays can do this quicker and with few lines. I have several reports which I create them. I do it currently this way

With wsed.UsedRange.Columns
.Rows(1).Interior.Color = RGB(252, 228, 214) 'First I color the existing report columns with this
.Cells(1, .Count + 1) = "Column1" 'Add a new column1
.Cells(.Count).Offset(0, 1).Resize(, 1).Interior.Color = RGB(191, 191, 191) 'Color the new column1 with a new shade
.Cells(.Count).Offset(0, 1).Resize(, 1).ColumnWidth = 8.89 ' Format the size of the new column1
.Cells(1, .Count + 2) = "Column2"
.Cells(.Count).Offset(0, 2).Interior.Color = RGB(198, 224, 180) 'Color the new column2 with a new shade
.Cells(1, .Count + 3) = "COLUMN3" 'Add a new column3 , color it, format the size etc etc for every new column created

by the way this is in conjuncture with the previous script p45cal helped me to automate..

Lookup using Index match based on column name where search Column is not the first

Below VBA script I have currently pull the results only if the search value column is in the first column. Example Emp ID to search should be in Column A.I would like the script to fetch the results for multiple columns based on header name irrespective of the position of the search header...

chandoo.org

So I will first create these new columns to the existing report ( Activeworkbook.ActiveSheet ) followed by the previous script from p45cal to Lookup the multiple columns from Sheet"Data". I didn't want to mix up these 2 and complicate the codes requirements.

 

[p45cal]

Change ActiveSheet in the code below to whatever your sheet is:

NewHeaders = Array("Fiscal Year", "Month", "Month_Year", "Project", "Local Expense", "Base Expense")
With ActiveSheet.UsedRange
  .Rows(1).Interior.Color = RGB(252, 228, 214)
  With .Cells(1).Offset(, .Columns.Count).Resize(, UBound(NewHeaders) - LBound(NewHeaders) + 1)
    .Value = NewHeaders
    .Interior.Color = RGB(191, 191, 191)
  End With
End With

You'll only need to change the NewHeaders = line since the Resize bit is now calculated from that array size.

 

[Jonnathanjons]

You are genius p45cal. With few lines your code works faster.THANK YOU SO MUCH