The answer to a question I asked some time ago was a macro which worked
beautifully for a
while; but it apparently has been corrupted. I cannot get in touch with the
person who provided
it. I don’t understand Visual Basic so need help.
The purpose of the macro is to copy formulas from all the cells in the row 1
and enter them inrow 2 with a new file name.
As a sample, one of the formulas for customer Jones is: (many of the
formulas are far more complex)
='\\Server1\shareddocs\Daily 2005\[Jones.xls]BPR'!$D$2&
""&LEFT('\\Server1\shareddocs\Daily 2005\[Jones.xls]BPR'!$A$1,9)
The macro asks for a customer name to be used in the formulas in the next
row (say “Smith�) and
then replaces Jones with Smith in each formula.
Apparently the name which corrupted the macro was (there was a previous
customer named
Garcia, so the next Garcia was dealt with as) Garcia.Lettie. It was handled
correctly; but
when I entered the next name (say) Smith, I got Smith.Lettie. So I replaced
Garcia.Lettie with
GarciaL. The name on the next line, Smith, was copied as SmithL. I’m sure
there is some logic
to the corruption but I can’t fathom what it might be.
The macro is as follows:
Sub AddLineData() ' to add a row on data page
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Newname = InputBox("Enter New Name")
lr = Cells(Rows.Count, "g").End(xlUp).Row + 1
Rows(lr - 1).Copy Rows(lr)
mcel = Cells(lr - 1, "f")
mn = Trim(Right(mcel, Len(mcel) - InStr(mcel, " ") + 1))
Rows(lr).Replace What:=mn, Replacement:=Newname, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Sub AddLineSummary() ' to add a row on summary page
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Newname = InputBox("Enter New Name")
lr = Cells(Rows.Count, "g").End(xlUp).Row + 1
Rows(lr - 1).Copy Rows(lr)
mn = Cells(lr - 1, 2)
Cells(lr, 2).Replace What:=mn, Replacement:=Newname
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
I would appreciate any help you can offer.
Bookmarks