Tuesday, 10 September 2013

Run time error in excel vba. last used range rows selects row 65536 instead of actual last used range

Run time error in excel vba. last used range rows selects row 65536
instead of actual last used range

I am trying to select columns E and K from sheet Input, process in Working
sheet and paste in the Output sheet after the last used row. I have stored
the last used row number in x and paste the values in x+1 cell. However
excel selects last row of the sheet (x as 65536) and gives run time error
4004. Can someone please help me in assisting the code.
Dim x As Long, y As String
Sheets("Input").Activate
Range("E:E,K:K").Select
Range("K1").Activate
Selection.Copy
Sheets("Working").Select
Cells(1, 1).Select
ActiveSheet.Paste
Cells.Select
Application.CutCopyMode = False
Selection.AutoFilter
Range("B5").Select
ActiveSheet.Range("$A$1:$H$30").AutoFilter Field:=1, Criteria1:="="
Cells.Select
Selection.Delete Shift:=xlUp
Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",VLOOKUP(RC[-1],Repository!C[-1]:C[1],3,0))"
Range("B2").Select
Selection.Copy
Range("B3:B30").Select
ActiveSheet.Paste
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("B2:C2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Output").Select
Range("A1").Select
x = Worksheets("Output").UsedRange.Rows.Count
y = "a" & Trim(x + 1)
ActiveSheet("Output").Range(y).Select
ActiveSheet.Paste

No comments:

Post a Comment