CODEWITHSUNDEEP
vbaobject
aannie
aannie

Reputation: 17

VBA: object doesn't support this property or method

I wrote a very simple macro, that opens another workbook and select range of data from this workbook. However, I keep receiving this warning: object doesn't support this property or method

What is wrong?

Sub data()
Dim wb As Workbook
Dim ws As Worksheet
Dim filename As String
Dim lastrow As Integer
Dim lastcolumn As Integer
Dim range_to_copy As Range

'open workbook
filename = "C:\Users\mk\Desktop\sales report\Sales Report.xls"
Set wb = Workbooks.Open(filename)
Set ws = wb.Sheets("data")

lastcolumn = wb.ws.Cells(1, wb.ws.Columns.Count).End(xlToLeft).Column
lastrow = wb.ws.Cells(wb.ws.Roows.Count, 1).End(xlToLeft).Row

range_to_copy = Range("Cells(1,1):Cells(lastrow,lastcolumn)")

End sub

Upvotes: 0

Views: 1803

Answers (3)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19767

A number of things wrong.

Edit:
Dim lastrow As Integer Dim lastcolumn As Integer
An Integer can store numbers up to 32,767. This won't be a problem for columns, but will throw an overflow error on the row number. It's better to use the Long data type - numbers up to 2,147,486,647.

The ws variable already references the workbook so you just need:
lastcolumn = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

lastrow = wb.ws.Cells(wb.ws.Roows.Count, 1).End(xlToLeft).Row
Rows only has one o in it.
Edit: xlToLeft looks at the right most cell and works to the left. As you're looking for rows you need to use xlUp which looks at the last cell and works up.

range_to_copy = Range("Cells(1,1):Cells(lastrow,lastcolumn)")
This is an object so you must Set it. The cells references are separated by a comma and should not be held as a string. 

Sub data()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim filename As String
    Dim lastrow As Long
    Dim lastcolumn As Long
    Dim range_to_copy As Range

    'open workbook
    filename = "C:\Users\mk\Desktop\sales report\Sales Report.xls"
    Set wb = Workbooks.Open(filename)
    Set ws = wb.Sheets("data")

    lastcolumn = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    lastrow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    Set range_to_copy = ws.Range(ws.Cells(1, 1), ws.Cells(lastrow, lastcolumn))

End Sub

Note: Every range reference is preceded by the worksheet reference. Without this it will always look at the currently active sheet so if you aren't on the data sheet the following will fail as the second cell reference will look at the activesheet.
ws.Range(ws.Cells(1, 1), Cells(lastrow, lastcolumn))

It might be worth checking out the With....End With code block.

Upvotes: 2

Shai Rado
Shai Rado

Reputation: 33682

Once you defined and set your ws worksheet object, you don't need to refer to the wb object anymore, since your ws worksheet object is fully quallified with Sheets("data") in wb workbook.

Now, all you need to do is use the With statement, like in the code below:

Set ws = wb.Sheets("data")    
With ws
    lastcolumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
    lastrow = .Cells(.Rows.Count, 1).End(xlUp).Row

    Set range_to_copy = .Range(.Cells(1, 1), .Cells(lastrow, lastcolumn))
End With

Upvotes: 0

FunThomas
FunThomas

Reputation: 29276

There are several errors in your code. Try

lastcolumn = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

Set range_to_copy = Range(ws.Cells(1, 1), ws.Cells(lastRow, lastcolumn))

Upvotes: 0

Related Questions