CODEWITHSUNDEEP
excelvba
hajduk1908
hajduk1908

Reputation: 11

Getting object variable or With block variable not set

This script goes through all the selected sheets and inserts 10 blank rows at the top. It then should export these sheets and save the WS name into a csv format. However I seem to be getting the error Getting object variable or With Block variable not set.

I tried a few things but so far no success. 

Sub Insert_Rows_Loop()
Dim CurrentSheet As Object
Dim WS As Worksheet
Dim path As String


path = ActiveWorkbook.path & "\" & Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".") - 1)


' Loop through all selected sheets.
For Each CurrentSheet In ActiveWindow.SelectedSheets
    ' Insert 10 rows at top of each sheet.
    CurrentSheet.Range("a1:a10").EntireRow.Insert

    'With ws
            WS.SaveAs Filename:=path & "_" & WS.Name & ".csv", FileFormat:=xlCSV, CreateBackup:=False
        'End With


Next CurrentSheet
Application.DisplayAlerts = True
End Sub

Upvotes: 0

Views: 1210

Answers (1)

rjose
rjose

Reputation: 615

While running a macro, I came across an error: Run-time error '429': ActiveX component can't create object.

Clicking on Debug will show me an error: "Object variable or With block variable not set".

The issue was with ThisWorkbook. I replaced it with Application.ThisWorkbook and it resolved the issue. So, please try using Application.ActiveWorkbook

Upvotes: 1

Related Questions