Hi all i have recorded a macro see below
Sub newun()
ActiveCell.FormulaR1C1 = "=PROPER(RC[-2])&"" ""&PROPER(RC[-1])"
ActiveCell.Select
Selection.Copy
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
End Sub
This works fine as long as the column to the left of the column i'm putting the formula into is always completed this may not always be the case. Is there a way of finding the bottom of the speadsheet then returning to the new column i have manually inserted then pasting in the formula. I'm guessing that ctrl and end finds me the bottom but i don't know how to record the number of columns i have moved so i can use this to get back to my original column..
I hope this makes sense
Cheers
Paul
Sub newun()
ActiveCell.FormulaR1C1 = "=PROPER(RC[-2])&"" ""&PROPER(RC[-1])"
ActiveCell.Select
Selection.Copy
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
End Sub
This works fine as long as the column to the left of the column i'm putting the formula into is always completed this may not always be the case. Is there a way of finding the bottom of the speadsheet then returning to the new column i have manually inserted then pasting in the formula. I'm guessing that ctrl and end finds me the bottom but i don't know how to record the number of columns i have moved so i can use this to get back to my original column..
I hope this makes sense
Cheers
Paul