VBA macro -running in debugging, does not work in runtime

Question

I have created macro that creates multiple workbooks from master file depending on one column.

Sub Split_into_separate_files()


With Application
        .ScreenUpdating = False

        .DisplayAlerts = False
End With


ThisWorkbook.Sheets(1).Activate

'removes existing filters

On Error Resume Next
Sheet1.ShowAllData
On Error GoTo 0

'variables declaration
Dim lsrClm As Long
Dim lstRow As Long
Dim lstRow_UNQ As Long
Dim Val As Range
Dim uniques As Range
Dim clm As String, clmNo As Long
Dim lst As Long
Dim lstClm As Long
Dim LR As Long
Dim Uniqu As Range

'finding the last row in master file and creates range from column that I want to filter in loop
lstRow = Cells(Rows.Count, 1).End(xlUp).Row
On Error GoTo handler
clm = Application.InputBox("From which column you want create files" & vbCrLf & "E.g. A,B,C,AB,ZA etc.")
clmNo = Range(clm & "1").Column
Set uniques = Range(clm & "8:" & clm & lstRow)
    
'creating new worksheet and pasting values for loop
Sheets.Add
On Error Resume Next
ActiveSheet.Name = "uniques"
Sheets("uniques").Activate
On Error GoTo 0
uniques.Copy
Cells(2, 1).Activate
ActiveCell.PasteSpecial xlPasteValues
Range("A1").Value = "Uniques"

'removing duplicates
lst = Cells(Rows.Count, 1).End(xlUp).Row
Range("A2:A" & lst).Select
ActiveSheet.Range(Selection.Address).RemoveDuplicates Columns:=1, Header:=xlNo
    
    lstRow_UNQ = Cells(Rows.Count, 1).End(xlUp).Row
    Set Val = Range("A2:A" & lstRow_UNQ)

'filtering loop
    For Each Uniqu In Val

'setting dataset size
        Sheets("F21").Activate
        LR = Cells(Rows.Count, 1).End(xlUp).Row
        lstClm = Cells(7, Columns.Count).End(xlToLeft).Column
        Dim dataSet As Range
        Set dataSet = Range(Cells(7, 1), Cells(LR, lstClm))
'filtering values
        dataSet.AutoFilter field:=clmNo, Criteria1:=Uniqu.Value
'setting dataset size for copying
        LR = Cells(Rows.Count, 1).End(xlUp).Row
        lstClm = Cells(7, Columns.Count).End(xlToLeft).Column
        Set dataSet = Range(Cells(5, 1), Cells(LR, lstClm))
        dataSet.Copy
'creating new workbook and pasting values
        Dim WB As Workbook
        Set WB = Workbooks.Add
        Range("A2").PasteSpecial
        Sheets(1).Cells.Copy
        Sheets(1).Cells.PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False
        ActiveWindow.Zoom = 70
        Columns("A:DB").EntireColumn.AutoFit
        WB.SaveAs Filename:= _
        "here is directory"
        ActiveWorkbook.Close
        Sheets("F21").ShowAllData
        
     Next Uniqu


With Application
        .ScreenUpdating = True

        .DisplayAlerts = True

        .AlertBeforeOverwriting = True

        .Calculation = xlCalculationAutomatic
End With

    ThisWorkbook.Sheets("F21").Activate

    MsgBox "Well Done!"

    Exit Sub

    ActiveSheet.ShowAllData

'konczy makro jezeli jest blad
handler:

With Application
        .ScreenUpdating = True

        .DisplayAlerts = True

        .AlertBeforeOverwriting = True

        .Calculation = xlCalculationAutomatic
End With


End Sub

The master worksheet is named "F21".

When I running this macro I got an error: Subscript out of range - indicating this line - > Sheets("F21").Activate.

When running macro from debugging mode, there is no error.

Could you help?

You may want to read [this](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). It primarily talks about avoiding `Select`, but the same principles apply to avoiding `Activate`. If you qualify your `Range` and `Cells` calls with the workbook and worksheet, you won't run into this problem. — BigBen, Oct 06 '20 at 19:15

score 0 · Answer 1 · answered Oct 06 '20 at 19:58

@BigBen is right, You should always avoid events (Select, Copy, Activate, etc.).

In your case the problem is that you are trying to activate a worksheet that is in another workbook, Thisworkbook.Sheets("F21").Activate will work.

But you are just pasting the values of a range into another range. Cells in Excel are just variables.

You can just pass the value of a range to another range. Example:

Thisworkbook.Sheets("F21").Range("A1:A10").Value = Thisworkbook.Sheets("F21").Range("B1:B10").Value

This way there will be no need of Selection, Activate or Copy and it's faster than using events.

VBA macro -running in debugging, does not work in runtime

1 Answers1