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