CODEWITHSUNDEEP
vbaexcel
Jenny
Jenny

Reputation: 451

Error:Object doesnot support this property or method

I am trying to extract a column from one workbook and trying to paste it in another workbook.

The code was working fine, I am completely lost, why I am getting this error

Object does not support this property or method

in the line LCell = Selection.SpecialCells(xlCellTypeLastCell).Address

Could anyone help me, to figure out the reason.

Below is the complete code

Sub Extractred()
Dim x As Workbook
Dim y As Workbook
Dim Val As Variant
Dim filename As String
Dim LastCell As Range
Dim LastRow As Long
CopyCol = Split("AK", ",")
LR = Cells(Rows.Count, 1).End(xlUp).Row
LC = Cells(1, Columns.Count).End(xlToLeft).Column
LCell = Selection.SpecialCells(xlCellTypeLastCell).Address
LCC = Selection.SpecialCells(xlCellTypeLastCell).Column
lcr = Selection.SpecialCells(xlCellTypeLastCell).Row

Set y = ThisWorkbook
'lcr = y.Cells(y.Rows.Count, "A").End(xlUp).Row
    Dim path1, Path2
path1 = ThisWorkbook.Path
Path2 = path1 & "\Downloads"
Set x = Workbooks.Open(filename:=Path2 & "\Red.xlsx")
For Count = 0 To UBound(CopyCol)
Set temp = Range(CopyCol(Count) & "1:" & CopyCol(Count) & lcr)
If Count = 0 Then
Set CopyRange = temp
Else
Set CopyRange = Union(CopyRange, temp)
End If
Next
CopyRange.Copy
y.Sheets("All").Paste y.Sheets("All").Range("B4")
Application.CutCopyMode = False
x.Close
End Sub

Upvotes: 1

Views: 102

Answers (2)

Domenic
Domenic

Reputation: 8104

Alternatively, you can use the RangeSelection property of the Window object, which will refer to the selected cells on the worksheet even if a graphic object is selected...

LCell = ActiveWindow.RangeSelection.SpecialCells(xlCellTypeLastCell).Address

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96753

You can get this type of error if a non-Range is Selected at the time the macro is run.

Make sure a Range is Selected, and not some Shape, Chart, etc.

Upvotes: 0

Related Questions