I have a piece of code that will only work if the column the data is to be pulled from is column"A". I would love to have the code modified so that this will do the same thing if the data varies in location from spreadsheet to spreadsheet.
If for instance, on a spreadsheet the data is in column "E". I would like to be able to select either E1 or select the entire column and run the macro to extract the last 2 positions.
Any help would be appreciated. Here is the current code.
If for instance, on a spreadsheet the data is in column "E". I would like to be able to select either E1 or select the entire column and run the macro to extract the last 2 positions.
Any help would be appreciated. Here is the current code.
Code:
Sub Extract()
'
' Extract data from column A
'
'
Dim data As Range
Set data = Range(Range("A2"), Range("A65536").End(xlUp))
Application.ScreenUpdating = False
Selection.Offset(0, 1).EntireColumn.Insert
With data
.Offset(0, 1).FormulaR1C1 = "=Right(RC[-1],2)"
.EntireRow.Sort Key1:=data(1, 1).Offset(0, 1), key2:=data(1, 1)
End With
Range("B1").Select
Selection.Offset(0, 1).EntireColumn.Insert
Selection.EntireColumn.Select
Selection.Copy
Selection.Offset(0, 1).Select
'paste special "extract"
Selection.PasteSpecial Paste:=xlPasteValues, Operation: _
=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Offset(0, -1).Select
Selection.Delete Shift:=xlToLeft
Cells(1, Selection.Column).Select
ActiveCell.FormulaR1C1 = "Extract"
Application.ScreenUpdating = True
End Sub
Last edited by a moderator: