Help with VBA to delete columns in new tabs

[zlomchk]

Hello,

I'm trying to make a macro that simply creates new tabs and assigns names to each by referencing a list. After doing so, I need it to delete Columns A:F per each new tab.

The macro works fine if I take out

Columns("A:F").Delete

but obviously I need that in there. Any ideas what to do? Thanks in advance. Here's the code:

Sub Macro2()
'
' Macro2 Macro

Dim i As Integer
Dim last_SUP As Integer
Dim SUP_nme As String

i = 2

Set sht = ThisWorkbook.Worksheets(Sheet2.Name)
last_SUP = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row

Do While i <= last_SUP

    SUP_nme = Cells(i, 1).Value
    Columns("A:F").Delete
    Sheets("SUP List").Copy After:=Sheets(1)
    Sheets("SUP List (2)").Name = SUP_nme
    
    
    i = i + 1
Loop
    
    
End Sub

 

[timfritsch]

It sounds like you want to delete columns A-F after you create the new sheet. If that is the case, try this:

  SUP_nme = Cells(i, 1).Value
  Sheets("SUP List").Copy After:=Sheets(1)
  Sheets("SUP List (2)").Name = SUP_nme
  Sheets(SUP_nme).Columns("A:F").Delete
  i = i + 1

Tim

 

[zlomchk]

Tim,

That returns a Run-time error '1004': Application-defined or object-defined error. This is located on the line containing:

Sheets("SUP List (2)").Name = SUP_nme

Thanks for taking time to help!

 

[Smitty]

Welcome to the Board!

I'd use ActiveSheet.Name = SUP_nme instead, since a newly copied sheet will always be the active sheet, so you don't need to worry about its name.

HTH,

 

[zlomchk]

Hm, that still returned the same error on that line.

Thanks for the help, that is useful idea.

 

[Smitty]

Is SUP_nme a valid name?

Note that you're not explicitly referencing sht with SUP_nme, so it might not even have a value, which would cause an error.

 

[zlomchk]

I think you are right, it returns as blank; do you have any suggestions? Not sure how to work it to reference sht with SUP_nme

 

[Smitty]

Try referencing sht:

SUP_nme = sht.Cells(i, 1).Value