1

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
Jenny
  • 441
  • 2
  • 7
  • 19

2 Answers2

1

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
Domenic
  • 7,844
  • 2
  • 9
  • 17
  • adding this line, I am getting an "Application defined error "in the line Set temp = Range(CopyCol(Count) & "1:" & CopyCol(Count) & lcr) – Jenny Aug 06 '17 at 12:37
  • could you suggest how I can get rid of this ? – Jenny Aug 06 '17 at 12:50
0

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.

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • 1
    @Jenny - [Don't use Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). –  Aug 06 '17 at 12:59
  • @Jeeped I have an doubt. I used domnik solution to solve the problem.I now get only few rows getting copied to destination sheet. I tried to debug, during that time, I could see 727 rows getting selected ? Could you suggest what would be the problem – Jenny Aug 06 '17 at 13:26
  • @Jeeped I dint have this problem earlier. I have this code being used from last 15 days .Just today I am having these issues – Jenny Aug 06 '17 at 13:26