Getting object variable or With block variable not set

Question

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

Thanks Dan, When the selected WS are exported, is there a way to keep the insertion of the 10 rows intact, for eg when I selected the worksheets, the macro goes through and inserted the ten blank row however when I export and check them I don't see the insertion of the top 10 blank rows in. — hajduk1908
– hajduk1908, Commented Aug 14, 2015 at 1:21
Just because you declare WS a worksheet, doesn't mean Excel knows which worksheet you're talking about. Trying setting the worksheet to a specific worksheet and see if that gets you closer. — sous2817
– sous2817, Commented Aug 14, 2015 at 1:37

rjose · Accepted Answer · 2018-06-07 10:13:33Z

1

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

edited Jun 7, 2018 at 10:13

answered Jun 6, 2018 at 8:16

rjose

6458 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Getting object variable or With block variable not set

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related