New workbook data to be formatted as medium 2: Error '450' wrong number of arguments or invalid property assignment

Question

Objective is to copy one workbook to a new workbook, then format the new workbook to "blue medium 2" format.

I get

wrong number of arguments or invalid property assignment

on

ActiveSheet.Cells(1, i).Style = "Medium 2"  

Sub CopySheetAndEliminateColumns()
    Dim wsCopy As Worksheet
    Dim wsNew As Worksheet
    Dim ListObj As ListObject
    
    'Set the worksheet to be copied
    Set wsCopy = ThisWorkbook.Sheets("AnalysisRound1")
    
    'Create a new workbook and copy the worksheet
    Set wsNew = Workbooks.Add(xlWBATWorksheet).Sheets(1)
    wsCopy.Cells.Copy
    Range("A1").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    
    'Delete Columns with not needed data
    Range("F:I,K:L,N:N,P:P").Delete
    
    'Autofit the columns in the new worksheet
    wsNew.Cells.EntireColumn.AutoFit
    
    'Borders around active worksheet
    Application.ScreenUpdating = False
    Dim lngLstCol As Long, lngLstRow As Long

    lngLstRow = ActiveSheet.UsedRange.Rows.Count
    lngLstCol = ActiveSheet.UsedRange.Columns.Count

    For Each rngCell In Range("A2:A" & lngLstRow)
        If rngCell.value > "" Then
            r = rngCell.Row
            c = rngCell.Column
            Range(Cells(r, c), Cells(r, lngLstCol)).Select
            With Selection.Borders
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
            End With
        End If
    Next
    
    ' Code to format new workbook sheet to blue, Medium 2
    Dim LastCol As Long
    LastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column

    ' Set the cell style to Medium 2 for all data in columns, including the headers
    Dim i As Long
    For i = 1 To LastCol
        ActiveSheet.Columns(i).Style = "Medium 2"
    
    'Code bombs on the line below - wrong number of arguments or invalid property assignment - 
        ActiveSheet.Cells(1, i).Style = "Medium 2"  
    Next i
    
    Application.ScreenUpdating = True
      
End Sub

• Changed the variable Dim LastCol As Long to use the code above Dim lngLstCol = Activesheet
• Moved the code to below Application.ScreenUpdating = True
• The following script turned my original worksheet into Medium 2

Dim w As Worksheet
Dim r As Range
For Each w In Worksheets
    If w.ListObjects.Count < 1 Then
        Set r = w.Range("A1").CurrentRegion
        r.Interior.ColorIndex = xlColorIndexNone
        w.ListObjects.Add(Source:=r).Name = w.Name
    End If
Next w

Answer 1

Assumption:

1. Your original data is not set as table
2. The data is located in Cell A1's current region

Step:

1. Copy those data into new workbook
2. Set those data into table and the default format should be Medium 2

Codes:

Sub toCopyDataAndFormatAsTable()

Dim oriWS As Worksheet
Dim newWB As Workbook

Set oriWS = ThisWorkbook.Worksheets("Sheet1")
Set newWB = Workbooks.Add

With newWB.Worksheets(1)
    oriWS.Range("A1").CurrentRegion.Copy .Range("A1")
    ActiveSheet.ListObjects.Add(xlSrcRange, .Range("A1").CurrentRegion, , xlYes).Name = _
    "Table1"
End With

'I think this line is not needed unless you want to set it into another format
ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleMedium3"
End Sub

Answer 2

these codes should be added to your excel file that containing your original data. Then, after you run those codes, a new workbook will be created and your original data will be copied to the newly created workbook with default format Medium

Sub toCopyDataAndFormatAsTable()

Dim oriWS As Worksheet
Dim newWB As Workbook

Set oriWS = ThisWorkbook.Worksheets("Sheet1")
Set newWB = Workbooks.Add

With newWB.Worksheets(1)
    oriWS.Range("A1").CurrentRegion.Copy .Range("A1")
    .ListObjects.Add(xlSrcRange, .Range("A1").CurrentRegion, , xlYes).Name = "Table1"
    
End With


End Sub