Definition of a workbook not working properly

Question

I am trying to execute quite a simple command in VBA (assigning a value to a range) and am getting an error "application defined or web-defined error". After some troubleshooting I think it has to do with the definition of my worksheet. Here is my code:

Sub CommandButton1_Click()
Dim arr() As String
Dim path As String
Dim filename As String
Dim w1 As Workbook
Dim w2 As Workbook
Set w1 = ThisWorkbook
Dim rng As Range
'w1.Worksheets(1).Range(Cells(1, 13), Cells(1, 22)) = 1 **#works perfectly, populates the given cells with 1's**
path = "C:\Users\Nenko\Desktop\"
filename = "D1.csv"
Workbooks.Open filename:=path & filename
ActiveSheet.Columns("A:B").EntireColumn.Delete
ActiveSheet.Columns("F:AQ").EntireColumn.Delete
ActiveSheet.Columns("G:G").EntireColumn.Delete
ActiveSheet.Columns("H:H").EntireColumn.Delete
ActiveSheet.Columns("I:J").EntireColumn.Delete
ActiveSheet.Columns("J:J").EntireColumn.Delete
ActiveSheet.Columns("K:P").EntireColumn.Delete
'Set w2 = Workbooks(path & filename) #subscript out of range
'Set w2 = ThisWorkbook **# application defined or web-defined error in Sheets.Add.Name=arr(a)**
Set w2 = ActiveWorkbook
'Set Range = w2.Worksheets(1).Range(Cells(1, 13), Cells(1, 22)) **# argument not optional**
'Range = 1
'w2.Worksheets(1).Range(Cells(1, 13), Cells(1, 22)) = 1 **#application defined or web-defined error**
EndRow = Sheets(1).Cells(Sheets(1).Rows.Count, "C").End(xlUp).Row
'w2.Worksheets(1).Cells(1, 11) = w2.Worksheets(1).Cells(1, 1).Value **#does nothing**
ActiveSheet.Range("A2:A65536").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ActiveSheet.Range("K:K"), Unique:=True
EndRow2 = Sheets(1).Cells(Sheets(1).Rows.Count, "K").End(xlUp).Row - 1
ReDim arr(EndRow2)
For a = 2 To EndRow2 + 1
arr(a - 1) = w2.Worksheets(1).Cells(a, 11).Value
Next
ActiveSheet.Columns("K:K").EntireColumn.Delete
For a = 1 To EndRow2
Sheets.Add.Name = arr(a)
c = 1
d = 1
For b = 1 To EndRow
If w2.Worksheets(a + 1).Cells(b, 1) = arr(a) Then
w2.Worksheets(1).Rows(c).EntireRow.Value = w2.Worksheets(a + 1).Rows(b).EntireRow.Value
c = c + 1
End If
'If w2.Worksheets(a + 1).Cells(b, 2) = arr(a) Then
'w2.Worksheets(1).Range(Cells(d, 13), Cells(d, 22)) = w2.Worksheets(a + 1).Range(Cells(b, 1), Cells(b, 10))
'd = d + 1
'End If **#this is what I tried to do initially and started getting the error**
Next
Next

End Sub

End Sub

After the # sign I have put the errors I am getting when adding the corresponding line to the code.

What the code is doing is opening a second workbook, taking the unique values from the first column of its only sheet and creating one tab for every unique value, containing the rows corresponding to this value. It works quite fine until I try to use a Range of any form in the second sheet w2, which always returns some kind of error.

During troubleshooting I found out that the command w1.Worksheets(1).Range(Cells(1, 13), Cells(1, 22)) = 1 populates the corresponding cells in workbook w1 with 1's. On the otherhand the command w2.Worksheets(1).Range(Cells(1, 13), Cells(1, 22)) = 1 returns "application defined or web-defined error"

I don't understand why it works for the one workbook but not for the other. I have tried defining the second workbook in every way I know in VBA, but in each case I get some kind of error.

Does anyone have an idea what's wrong with the second workbook?

Answer 1

I see three suggestions you can try/use.

First, to set your workbook, try this:

Set w2 = Workbooks.Open(filename:=path & filename)

AFAIK you can't set a workbook by just telling VB the path. You need to open it first. Notice how your line will work if you just leave filename. (But it will only set if the workbook is open, otherwise you have to use the open line). This line does all the work in opening the file, setting the workbook to w2...so you don't need the (currently commented out) next two lines.

Second: Another big point to make when using multiple workbooks is to be careful when using Range. For example, using these two worksheets

Set ws1 = Sheets("worksheet1")
Set ws2 = Sheets("number2")

Set rng1 = ws1.Range(Cells(1,1),Cells(1,2)) 

is not necessarily going to work for you. You need to state explicitly which worksheet you're using with Cells() as well. This line is better:

Set rng1 = ws1.Range(ws1.Cells(1,1),ws1.Cells(1,2))

Or, using With

With ws1
    .Range(.Cells(1,1),.Cells(1,2))
End With

What can happen is that is your ws2 is the active sheet, and you try to set the rangerng1 = ws1.range(cells(1,1),Cells(1,1)), the Cells() being referenced are on the active sheet, but you're trying to set a range on the ws2 using ActiveSheet cells, which won't jive. This will throw errors, which you're seeing at your line

w2.Worksheets(1).Range(Cells(1, 13), Cells(1, 22)) = 1. Change this to w2.Worksheets(1).Range(Worksheets(1).Cells(1, 13), Worksheets(1).Cells(1, 22)) = 1.

Finally, just to point out that Sheets(1) and Sheets("Sheet1") aren't the same thing necessarily. Sheets(1) refers to the first sheet in the "sheet index", where Sheets("Sheet1") refers to the sheet with the name "Sheet1". If you start a new workbook, by default you will have three sheets (indexed at 1, 2, and 3). If you rename those all "ws1", "ws2", "ws3", then add a new worksheet called "Sheet2", using Sheets(2) will refer to Sheets("ws2"), since that's the second sheet in the index. Does that make sense?