3

My VBA macro is assigned to an image from internet that i just pasted in my sheet

My macro is suppose to do this:

i have 40 columns with info, i want to add a column between column 1 and 2, 2 and 3.. and so on

when i click on my image, excel steps into the code and once in that window, a small window appears saying:

wrong number of arguments or invalid property assignment

also highlighting the first row where the main body is, i mean this part:

columns("B:B").

Would you please help me solve this? Sorry for my amateur question

Sub columns()
'
' columns Macro
'

'
    columns("B:B").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    columns("D:D").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    columns("F:F").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    columns("H:H").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    columns("J:J").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    columns("L:L").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    columns("N:N").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    columns("P:P").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    columns("R:R").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    columns("T:T").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    columns("V:V").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    columns("X:X").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    columns("Z:Z").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    columns("AB:AB").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    columns("AD:AD").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    columns("AF:AF").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    columns("AH:AH").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    columns("AJ:AJ").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    columns("AL:AL").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    columns("AN:AN").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    columns("AP:AP").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    columns("AR:AR").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    columns("AT:AT").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    columns("AV:AV").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    columns("AX:AX").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    columns("AZ:AZ").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    ActiveWindow.SmallScroll ToRight:=34
    columns("BB:BB").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    columns("BD:BD").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    columns("BF:BF").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    columns("BH:BH").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    columns("BJ:BJ").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    columns("BL:BL").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    columns("BN:BN").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    columns("BP:BP").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    columns("BR:BR").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    columns("BT:BT").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    columns("BV:BV").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    columns("BX:BX").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    columns("BZ:BZ").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub
0m3r
  • 12,286
  • 15
  • 35
  • 71
David VNZuela
  • 31
  • 1
  • 3
  • 3
    You can start by [avoiding the use of `.Select`](https://stackoverflow.com/q/10714251/5781745). Not only does this considerably slow down your code, but it also creates debugging headaches, as you've witnessed. – K.Dᴀᴠɪs Oct 31 '18 at 19:56
  • 1
    Either use `Range("B:B").Insert ...` or `Sheets("Sheet1").columns("B:B").Insert ...` as the property needs a range or sheet. – SJR Oct 31 '18 at 19:59
  • 1
    FYI your function is called `columns` and you have (eg) `columns("B:B").Select` so that's what's triggering the error – Tim Williams Oct 31 '18 at 20:22

1 Answers1

2

You can take that entire code you provided, and trim it down to this:

Sub insertColumns()

    Dim ws As Worksheet, i As Long
    Set ws = ThisWorkbook.Worksheets(1)

    For i = 2 To 78 Step 2
       ws.Columns(i).Insert
    Next

End Sub

Easy to read - (which makes it easy to debug) and no use of .Select.

Replace the 1 in Worksheets(1) to either the sheet index number or the sheet name (wrapped around doublequotes, Worksheets("Sheet1")

You can make this dynamic if you'd like:

Sub insertColumns()

    Dim ws As Worksheet, i As Long
    Set ws = ThisWorkbook.Worksheets(1)

    For i = 2 To LastColumn(ws) * 2 Step 2
       ws.Columns(i).Insert
    Next

End Sub

Function LastColumn(ws As Worksheet, Optional rowNum As Long = 1)
    With ws
        LastColumn = .Cells(rowNum, .Columns.Count).End(xlToLeft).Column
    End With
End Function

This will determine how many used columns you have and loop through those - just multiply the LastColumn by 2 since you are adding columns.

K.Dᴀᴠɪs
  • 9,945
  • 11
  • 33
  • 43