CODEWITHSUNDEEP
vbaexcel
SierraOscar
SierraOscar

Reputation: 17637

VBA Worksheet Automation Error

I have the following code at the start of a routine:

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet

Set ws1 = Workbooks.Open(ThisWorkbook.Sheets(1).[b4]).Sheets(1)
Set ws2 = Workbooks.Open(ThisWorkbook.Sheets(1).[b6]).Sheets(1)
Set ws3 = Workbooks.Open(ThisWorkbook.Sheets(1).[b8]).Sheets(1)

ws1.Move after:=ThisWorkbook.Sheets("MAIN")
ws2.Move after:=ThisWorkbook.Sheets("MAIN")
ws3.Move after:=ThisWorkbook.Sheets("MAIN")

'// remove redundant data
With ws1
    With .Range("A2:A" & .Cells(.Rows.Count, 1).End(xlUp).Row)  
    ...

Everything seems fine until the second With block, when I get an automation error:

Run-time error '-2147221080 (800401a8)':

Automation Error

Each of the files are CSV files with a single sheet, If I look in the locals window as they are being set they show as Worksheet/Sheet1 as I expect. Once I move the sheets to ThisWorkbook however, I notice that the type changes to Worksheet/Worksheet and I am no longer able to refer to ws1, ws2 or ws3 using the respective variables. The variables show as worksheet objects in the locals window and appear to be set to an object, but when I expand the variable's node it just states <No Variables>.

I haven't seen this behavior before in VBA so any explanations or suggestions would be greatly appreciated.

Thanks in advance.

Upvotes: 1

Views: 3057

Answers (1)

99moorem
99moorem

Reputation: 1983

The problem is that when you move a sheet from a 1 sheet workbook, it closes the workbook. would need to refer to the sheet in the current workbook not the one from the opened workbook. Code similar to below would work.

Sub tt()
Dim ws1 As Worksheet

Set ws1 = Workbooks.Open(ThisWorkbook.Sheets(1).[b1]).Sheets(1)
ws1_Name = ws1.Name

ws1.Move after:=ThisWorkbook.Sheets(1)

'// remove redundant data
With ThisWorkbook.Sheets(ws1_Name)
    With .Range("A2:A" & .Cells(.Rows.Count, 1).End(xlUp).Row)

    End With
End With
End Sub

You could also reset ws1 to the correct sheet

Set ws1 = ThisWorkBook.Sheets(ws1_Name)

Upvotes: 2

Related Questions