Combine 2 codes to run automatically as 1 code

Question

Can you you help to combine this 2 code and remove all unnecessary lines to avoid long running time, and I tried to combine it but I get Run time error #9

STEP 1 (Code# 1)

Sub STEP1()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
    With ws.Range("B:B")
        .NumberFormat = "General"
        .Value = .Value
        .HorizontalAlignment = xlLeft
    End With
    Debug.Print ws.Name
Next ws
Application.ScreenUpdating = True
End Sub

STEP 2 (Code# 2)

Sub STEP_2()

    Dim ws As Worksheet
    For Each ws In Sheets
        ws.Cells(1, 1).EntireColumn.Delete
    Next ws
    Sheets("x_ 659358").Select
    Rows("2:3").Select
    Selection.Delete Shift:=xlUp
    Sheets("x_682549 (2)").Select
    Application.DisplayAlerts = False
    ActiveSheet.Delete
    Application.DisplayAlerts = True
    Dim headers() As Variant
    Application.ScreenUpdating = False
    Set wb = ActiveWorkbook
    headers() = Array("sku", "barcode", "active", "price")
    For Each ws In wb.Sheets
        With ws
        .Rows(1).Value = ""
        For i = LBound(headers()) To UBound(headers())
            .Cells(1, 1 + i).Value = headers(i)
        Next i
        .Rows(1).Font.Bold = True
        End With
    Next ws
    
    Dim xWs As Worksheet
    Dim xDir As String
    Dim folder As FileDialog
    Set folder = Application.FileDialog(msoFileDialogFolderPicker)
    If folder.Show <> -1 Then Exit Sub
    xDir = folder.SelectedItems(1)
    For Each xWs In Application.ActiveWorkbook.Worksheets
        xWs.SaveAs xDir & "\" & xWs.Name, xlCSV
    Next

End Sub

I have tried to combine but always get stucked

Is it intentionally than your first Sub operates on `ThisWorkbook.Worksheets` (the worksheets of the workbook where the VBA code lives), and the second one just on `Sheets`, which means `ActiveWorkbook.Worksheets`, the worksheets of the currently active workbook (which can be a different one). Which one do you prefer? — Doc Brown, Feb 02 '23 at 14:33
To avoid long running time, here are a few suggestions ... https://learn.microsoft.com/en-us/office/vba/excel/concepts/excel-performance/excel-improving-calculation-performance — user10186832, Feb 02 '23 at 14:43
*Minimize the size of the ranges that you are using in array formulas and functions.* is the one that looks useful, your `Range("B:B")` has 1,048,576 rows ... https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3 — user10186832, Feb 02 '23 at 14:47
[**How to Avoid Select**](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Cameron Critchlow, Feb 02 '23 at 22:15

score 0 · Answer 1 · answered Feb 02 '23 at 23:58

0

Sub STEP1()
' your code...
Call STEP_2() ' <----
End Sub

Sub STEP_2()
' your code...
End Sub

answered Feb 02 '23 at 23:58

ASH

20,759
19
87
200

Your answer could be improved by adding more information on what the code does and how it helps the OP. – Tyler2P Feb 03 '23 at 18:29

Combine 2 codes to run automatically as 1 code

1 Answers1