0

I am trying to write a macro that inserts and fills out two columns for every tab of an excel document.

I have the following code:

Sub insert_columns()
'inserts two columns, one for "SUB" and one for concatenated "SUB-NAME" ("Name" already exists)
'fills out the columns
'cycles through every tab to do this

    Dim ws As Worksheet
    Dim num_rows As Integer
    Dim i As Integer
    Dim s As String
    Dim s_cut As String
    

    For Each ws In ThisWorkbook.Sheets

        'cycles through all worksheets, and creates/names the columns in each
        ws.Range("D:D").Insert 'inserts column to left of "Name"
        ws.Range("F:F").Insert 'inserts column to right of "Name"
        ws.Range("D1").Value = "SUB"
        ws.Range("D1").Interior.Color = RGB(226, 239, 218)
        ws.Range("F1").Value = "SUB-NAME"
        ws.Range("F1").Interior.Color = RGB(226, 239, 218)

        'fills "SUB" column
        s = ActiveSheet.Name 'gets name of tab
        s_cut = Right(s, 1) 'gets number at end of tab name
        ws.Range("C2").Select
        num_rows = Range(Selection, Selection.End(xlDown)).Rows.Count - 1 'gets number of filled rows in col C
        ws.Range("D2").Select
        Range(ActiveCell, ActiveCell.Offset(num_rows, 0)).Value = s_cut 'pastes sub # in all rows of column D to last line of other filled columns
        
        'fills "SUB-NAME" column
            For i = 0 To num_rows
                ws.Range("F2").Offset(i, 0).Select
                ActiveCell.Value = CStr(ActiveCell.Offset(0, -2).Value) & "_" & ActiveCell.Offset(0, -1).Value
            Next i

    Next
    
End Sub

I have tested pieces of this code separately, and they all seemed to work, but when I run this, it produces a '1004' error, "Select method of Range class failed." It produces and fills the columns in tab 1, then makes blank columns in tab 2, but does not fill them out and just stops there.

The debugger points to

ws.Range("C2").Select

(see picture) Location of error , but the syntax looks fine to me?

If someone could help find and fix the error, would be grateful. Thank you.

BigBen
  • 46,229
  • 7
  • 24
  • 40
arc_user
  • 17
  • 5
  • 1
    You can't select a cell on a sheet unless the sheet is active. But you don't need to select anything https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – SJR Feb 14 '22 at 19:50
  • 1
    Thanks, this helps! Will need to study more so I can rewrite the code without using Select/ActiveCell so much in the future. – arc_user Feb 14 '22 at 20:13

0 Answers0