Adding Columns after first blank column

[Shigeki]

I'm trying to write a macro to find the first blank column of a sheet and insert 3 columns just before that blank column with the same formatting as the previous 3 columns. Any ideas?

Thank you!

 

[NeonRedSharpie]

Sub insertColumns()    
    If Cells(1, 1).End(xlToRight).Column = Columns.Count Then MsgBox "No populated columns"


    For x = 1 To 3
        Columns(Cells(1, 1).End(xlToRight).Column).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
    Next x


End Sub

 

[Shigeki]

This is great! Just one tweek I'm not sure how to do. I have data in columns (3 columns for each month). After the current month there is one blank column. I'd like to essentially insert 3 rows to the right of the current month (but before the blank column) with the most recent month's formatting. What should I change in the syntax to accomplish this? I really appreciate your help!!

 

[NeonRedSharpie]

This is great! Just one tweek I'm not sure how to do. I have data in columns (3 columns for each month). After the current month there is one blank column. I'd like to essentially insert 3 rows to the right of the current month (but before the blank column) with the most recent month's formatting. What should I change in the syntax to accomplish this? I really appreciate your help!!

Sub insertColumns()    
    If Cells(1, 1).End(xlToRight).Column = Columns.Count Then MsgBox "No populated columns"


    For x = 1 To 3
        Columns(Cells(1, 1).End(xlToRight).Column + 1).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Next x


End Sub

I always get confused with how the inserting works. Try that. If not, try this:

Sub insertColumns()    
    If Cells(1, 1).End(xlToRight).Column = Columns.Count Then MsgBox "No populated columns"


    For x = 1 To 3
        Columns(Cells(1, 1).End(xlToRight).Column + 1).Insert Shift:=xlToLeft, CopyOrigin:=xlFormatFromLeftOrAbove
    Next x


End Sub

But I don't think that last one is what you're going to want.

 

[Shigeki]

This is definitely a step in the right direction! My data is positioned with the older months to the left and moving to the right. For some reason the 3 columns are being added to the far left of the earlier months rather than the right of the current month. What would you suggest altering to get the 3 columns to add to the right-hand end of the data?

Thank you!!

 

[NeonRedSharpie]

Well you mentioned that you wanted it after the FIRST blank column. So if column C is blank, it will add it AFTER column C. If you want it to be added to the end of your data...then use:

Sub insertColumns()
    If Cells(1, Columns.Count).End(xlToLeft).Column = Columns.Count Then MsgBox "No populated columns"

    For x = 1 To 3
        Columns(Cells(1, Columns.Count).End(xlToLeft).Column + 1).Insert Shift:=xlToLeft, CopyOrigin:=xlFormatFromLeftOrAbove
    Next x




End Sub

I also assumed that row 1 is entirely populated. If that's not the case, change Cells(1, 1) to Cells(2, 1) {for row 2}

 

[Shigeki]

You're absolutely amazing!!! I appologize, I didn't articulate my problem very well to begin with. This is great! Thank you so much!

 

[NeonRedSharpie]

You're absolutely amazing!!! I appologize, I didn't articulate my problem very well to begin with. This is great! Thank you so much!

No worries, communication over the internet with workbooks we can't see and people we've never met is inherently difficult. It adds to the fun.