Insert new columns, with headers - Macro/VBA help

[carefreeant88]

Hi all,

I have two separate/concurrent issues that I am hoping for help with.

For the first issue, I basically have a workbook with 4 separate worksheets/tabs on it. This automatically updates everyday based on latest Sales Data, and so - as such - the exact layout/order of the columns and headers changes.

I am basically therefore after some help, with writing a Macro/VBA which achieves the following. I am really struggling with it (bit of a newbie with Macros), so any help would be welcome:

1. Go to the tab called 'Sales Analysis'
2. Search for the word 'Text' on the top row (column headers)
3. When you find the column called 'Text', inserts 5 Columns to the left of it.
4. Call the first column that has been added 'Sales Data 1'
   Call the second column that has been added 'Sales Data 2'
   Call the third column that has been added 'Sales Data 3'
   Call the fourth column hat has been added 'Sales Data 4'
   Call the fifth column that has been added 'Sales Data 5'

Hopefully it's do-able. As I say, I am new to Macros/VBAs and so any help at all would be most welcome.

Regards

 

[MARK858]

Try the code below the, please note that the code will error if there isn't a header called Text.

Sub carefreeant1()
    Dim iCol As Integer
    Application.ScreenUpdating = False

    With Sheets("Sales Analysis")

        iCol = .Rows(1).Find("Text", , xlValues, xlWhole, xlByColumns, xlNext).Column
        .Columns(iCol).Resize(, 5).Insert
        .Cells(1, iCol).Resize(, 5).Value = Array("Sales Data 1", "Sales Data 2", "Sales Data 3", "Sales Data 4", "Sales Data 5")

    End With
  
End Sub

 

[Skyybot]

@MARK858 , don't you have to turn ScreenUdating back on? Or does it reset when the Sub ends?

 

[carefreeant88]

Try the code below the, please note that the code will error if there isn't a header called Text.

Sub carefreeant1()
    Dim iCol As Integer
    Application.ScreenUpdating = False

    With Sheets("Sales Analysis")

        iCol = .Rows(1).Find("Text", , xlValues, xlWhole, xlByColumns, xlNext).Column
        .Columns(iCol).Resize(, 5).Insert
        .Cells(1, iCol).Resize(, 5).Value = Array("Sales Data 1", "Sales Data 2", "Sales Data 3", "Sales Data 4", "Sales Data 5")

    End With
 
End Sub

Thanks a million! This has been a HUGE help.

Thanks again

 

[MARK858]

@MARK858 , don't you have to turn ScreenUdating back on? Or does it reset when the Sub ends?

It resets when the sub ends (one of the few that does), there used to be a quirk with it not resetting within userforms and UDF's, and I have seen posts where this is still appears to be true on other forums in recent years.

 

[MARK858]

Thanks a million! This has been a HUGE help.

Thanks again

Happy it helped and welcome to the forum