and why does this script make it happen please

Sub MergeMove()
'
' MergeMove Macro

'
With ActiveSheet
xlastrow = .Cells(Rows.Count, 2).End(xlUp).Row
For x = 1 To xlastrow
.Cells(x, 15) = .Cells(x, 8) & " " & .Cells(x, 9) & " "
Next x
End With
'
Columns("O:O").Select
Selection.Copy
Columns("H:H").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=False
Columns("I:I").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("N:N").Select
Selection.Delete Shift:=xlToLeft
Range("H1").Select
ActiveCell.FormulaR1C1 = "Address Ln.1"
Range("H2").Select
'

Columns("G:G").Select
Selection.NumberFormat = "yyyy-mm-dd"
Columns("D:D").Select
Selection.Replace What:="", Replacement:="N/A", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Columns("H:H").Select
Selection.Replace What:="", Replacement:="N/A", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Columns("I:I").Select
Selection.Replace What:="", Replacement:="N/A", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Rows("1:1").Select
Selection.Replace What:="ns_no", Replacement:="NS No.", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False
Rows("1:1").Select
Selection.Replace What:="surname", Replacement:="Surname", LookAt:
_
xlPart, SearchOrder:=xlByRows, MatchCase:=False
Rows("1:1").Select
Selection.Replace What:="forename1", Replacement:="Forename 1"
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False
Rows("1:1").Select
Selection.Replace What:="forename2", Replacement:="Forename 2"
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False
Rows("1:1").Select
Selection.Replace What:="title", Replacement:="Title", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False
Rows("1:1").Select
Selection.Replace What:="sex", Replacement:="Sex", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False
Rows("1:1").Select
Selection.Replace What:="dob", Replacement:="DOB", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False
Rows("1:1").Select
Selection.Replace What:="house_no", Replacement:="Address Ln.1"
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False
Rows("1:1").Select
Selection.Replace What:="road_name", Replacement:="Address Ln.2"
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False
Rows("1:1").Select
Selection.Replace What:="town_name", Replacement:="Town", LookAt:
_
xlPart, SearchOrder:=xlByRows, MatchCase:=False
Rows("1:1").Select
Selection.Replace What:="postcode", Replacement:="Post Code"
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False
Columns("M:M").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop
Operator:= _
xlBetween, Formula1:="N/A,P,L,D"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = "P=Newly found" & Chr(10) & "L=Left" & Chr(10
& "D=Died"
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Range("L1").Select
ActiveCell.FormulaR1C1 = "PCode"
Range("M1").Select
ActiveCell.FormulaR1C1 = "Registration Details"
Cells.Select
Selection.Columns.AutoFit
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Font
.Name = "Arial"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With Selection.Font
.Name = "Arial"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Rows("1:1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlTop
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
ActiveWindow.SmallScroll ToRight:=3
Range("M1").Select
Columns("M:M").ColumnWidth = 9.86
Columns("M:M").ColumnWidth = 10.86
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlTop
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Columns("L:L").Select
Selection.Replace What:="", Replacement:="N8???", LookAt:=xlPart,
_
SearchOrder:=xlByRows, MatchCase:=False
Range("A1").Select
Columns("O:O").Select
Selection.Copy
Columns("H:H").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Columns("I:I").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("N:N").Select
Selection.Delete Shift:=xlToLeft
Range("H1").Select
ActiveCell.FormulaR1C1 = "Address Ln.1"
Range("H2").Select
End Sub


--
Steve M
------------------------------------------------------------------------
Steve M's Profile: http://www.excelforum.com/member.php...o&userid=32520
View this thread: http://www.excelforum.com/showthread...hreadid=524276

Forgot to say that this macro is ran on data on another workbook not o
the one its i

--
Steve
-----------------------------------------------------------------------
Steve M's Profile: http://www.excelforum.com/member.php...fo&userid=3252
View this thread: http://www.excelforum.com/showthread.php?threadid=52427

Hi Steve,

If the active sheet is the one that you want to be changed when th
macro is run then it doesn't/shouldn't matter which workbook the cod
is in.

To find out what the error code "400" means, run the following sub:
Sub testingerror()
MsgBox Error(400)
End Sub

Although, for you to see this error, excel is probably asking if yo
want to debug the code. If it does, what line is highlighted in yello
when you choose "debug"?

Also, as this code has been created by the macro recorder it i
relatively ineffiecient b/c it "selects" a lot more than is required
could be tidied up a lot eg
Columns("O:O").Select
Selection.Copy
Columns("H:H").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:
_
False, Transpose:=False

can become:
Columns("O:O").Copy
Columns("H:H").PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:= False, Transpose:=False

&:
*any lines in the "with" sections that end in "= false" can probably b
deleted.
*try changing all the lines refering to "rows("1:1").select can b
changed to be included in a "with" construct eg

with Rows("1:1")
.Replace What:="dob", Replacement:="DOB", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="surname", Replacement:="Surname", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False
'...
end with

(This removes the need for every second line to b
"Rows("1:1").select".)


I'm off to bed now but will have another look tomorrow to see if I ca
figure out what is causing your error (& may post a tidied version o
your code).

hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience..

--
broro18
-----------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...fo&userid=3006
View this thread: http://www.excelforum.com/showthread.php?threadid=52427

My experience with the 400 error is that it is intermittent and not
necessarily associated with something in the macro. I would close excel and
reopen it and see if goes away.

--
Regards,
Tom Ogilvy


"Steve M" wrote:


Forgot to say that this macro is ran on data on another workbook not on
the one its in


--
Steve M
------------------------------------------------------------------------
Steve M's Profile: http://www.excelforum.com/member.php...o&userid=32520
View this thread: http://www.excelforum.com/showthread...hreadid=524276