Corrupted Macro

[Guest]

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.

 

[Myrna Larson]

The first macro was not written to handle names with spaces in them.
Presumably the client name is in column F in the row above. The macro gets
that name, then pulls out everything to the right of the first space as the
text to be replaced. So, for the first macro, if you have Lettie Garcia in
column F, only "Garcia" will be replaced; it it's L Smith, only the "Smith"
will be replaced.

But this doesn't match what you give as examples: you say Garcia.Lettie, not
Lettie Garcia.

If you are having the same problem with the 2nd macro, this also doesn't
explain it, as it retrieves all information from column B as the name to be
replaced.

Please post back with EXACTLY what text is found in column F on the data page
and column B of the summary page.

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.

 

[Guest]

The first macro was not written to handle names with spaces in them.
Presumably the client name is in column F in the row above. The macro gets
that name, then pulls out everything to the right of the first space as the
text to be replaced. So, for the first macro, if you have Lettie Garcia in
column F, only "Garcia" will be replaced; it it's L Smith, only the "Smith"
will be replaced.

But this doesn't match what you give as examples: you say Garcia.Lettie, not
Lettie Garcia.

If you are having the same problem with the 2nd macro, this also doesn't
explain it, as it retrieves all information from column B as the name to be
replaced.

Please post back with EXACTLY what text is found in column F on the data page
and column B of the summary page.

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.

Hi Myrna:

I have never replied to a post so hope this gets to you. I understand what you are saying and originally thought the problem was a space or something like your comment. However the example I gave of Garcia.Lettie; then GarciaL, is really what happened. I tried fool it by going back to the very first row and deleting subsequent rows. At that point, it handled a different name (say Prieto) on row 2 OK but no matter what name I entered from that point on it continued to repeat Prieto in rows 3 and 4.

The formula in Column F is:
'\\Server1\Daily 2005\[Garcial.xls]BPR'!$D$2&" "&LEFT('\\Server1\Daily
2005\[Garcial.xls]BPR'!$A$1,9)

NOTE: For some weird reason I cannot copy the formula from the file which
is on a remote computer and paste it in this post. I copy from the remote
computer and paste locally all the time. This is why I think something
strange is going on with the macro.

But, you asked for the TEXT in Column B. It is "6 Garcia" which is the
correct info from two cells in the file.

After entering the name "Hill" on the next line here is the result in Column
F:
'\\Server1\Daily 2005\[Hilll.xls]BPR'!$D$2&" "&LEFT('\\Server1\Daily
2005\[Hilll.xls]BPR'!$A$1,9)

See the extra "l" which was added to Hill.

The formula in Column B of the Summary page is:
There is no formula here; it is the name of a salesperson. Nothing on the
Summary page appears to be related to the formulas on the Data page - the
Summary page merely accumulates vaious totals by salesperson.

If you provide an email address I will be happy to provide you with the
report and a couple of sample customer files.

I really appreciate your interest and willingness to help me.

Vince

 

[Guest]

The first macro was not written to handle names with spaces in them.
Presumably the client name is in column F in the row above. The macro gets
that name, then pulls out everything to the right of the first space as the
text to be replaced. So, for the first macro, if you have Lettie Garcia in
column F, only "Garcia" will be replaced; it it's L Smith, only the "Smith"
will be replaced.

But this doesn't match what you give as examples: you say Garcia.Lettie, not
Lettie Garcia.

If you are having the same problem with the 2nd macro, this also doesn't
explain it, as it retrieves all information from column B as the name to be
replaced.

Please post back with EXACTLY what text is found in column F on the data page
and column B of the summary page.

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.

Hi Myrna:

I have never replied to a post so hope this gets to you. I understand what you are saying and originally thought the problem was a space or something like your comment. However the example I gave of Garcia.Lettie; then GarciaL, is really what happened. I tried fool it by going back to the very first row and deleting subsequent rows. At that point, it handled a different name (say Prieto) on row 2 OK but no matter what name I entered from that point on it continued to repeat Prieto in rows 3 and 4.

The formula in Column F is:
'\\Server1\Daily 2005\[Garcial.xls]BPR'!$D$2&" "&LEFT('\\Server1\Daily
2005\[Garcial.xls]BPR'!$A$1,9)

NOTE: For some weird reason I cannot copy the formula from the file which
is on a remote computer and paste it in this post. I copy from the remote
computer and paste locally all the time. This is why I think something
strange is going on with the macro.

But, you asked for the TEXT in Column B. It is "6 Garcia" which is the
correct info from two cells in the file.

After entering the name "Hill" on the next line here is the result in Column
F:
'\\Server1\Daily 2005\[Hilll.xls]BPR'!$D$2&" "&LEFT('\\Server1\Daily
2005\[Hilll.xls]BPR'!$A$1,9)

See the extra "l" which was added to Hill.

The formula in Column B of the Summary page is:
There is no formula here; it is the name of a salesperson. Nothing on the
Summary page appears to be related to the formulas on the Data page - the
Summary page merely accumulates vaious totals by salesperson.

If you provide an email address I will be happy to provide you with the
report and a couple of sample customer files.

I really appreciate your interest and willingness to help me.

Vince
Hi Myrna:

I have continued to play with this report and it appears that the problem is
caused by one of two factors: 1. Where there is more than one capital
letter in the name; and/or 2. Where the file name is different from the name
that prints in Column F.
Hope this helps.
Vince

 

[Myrna Larson]

2. Where the file name is different from the name that prints in Column F.

That is the problem exactly. The macro assumes that the file name can be
determined from the TEXT (i.e. the formula result, not the formula itself) in
column F. The text there is just Garcia, but the file name is Garcial. The
macro says to look at the formula and replace the letters "Garcia" with
"Hill". And that's what it does. So "GarciaL" becomes "HillL".

You have to get things back into sync so that the name which is used as part
of the file name is the same as what is in columns F and B.

 

[Myrna Larson]

The fundamental problem is that when the macro was written, the 2
specifications were that (1) the person's last name could be found to the
right of the 1st space in the text in column F on the 1st sheet, and in column
B on the 2nd sheet, and (2) the workbook name consisted of that last name
followed immediately by ".xls"

Then you hit a problem: that naming scheme can no longer work because you have
two people with the same last name. When that occurred, (1) the file was
created and named "GarciaL.xls" (you haven't said how the name is assigned).
(2) But in the workbook, BPR!A1 gives the last name, Garcia, not the filename
GarciaL.

So the macro can no longer work because the 2nd assumption -- that the file
name is always the last name + .xls -- has been violated. It sounds like you
need TWO cells on the worksheet, one containing the client's name, the other
the base file name.

If you set up a 2nd cell for the file name, then you would need to do that for
*all* existing workbooks. Then you have to change the formula in column F to
look at the cell containing the file name instead of cell $A$1. The 2nd macro
has to be changed to look for the file name not in column B, but in a new
column.

BTW, you *DO* need to resolve the underlying problem here. You could just
manually fix the worksheet names in the formulas in the rows below the Garcia
row. But the problem will recur when (a) you have another duplicate client
name, or (b) you try to insert a new client after Garcia.

 

[Guest]

That is the problem exactly. The macro assumes that the file name can be
determined from the TEXT (i.e. the formula result, not the formula itself) in
column F. The text there is just Garcia, but the file name is Garcial. The
macro says to look at the formula and replace the letters "Garcia" with
"Hill". And that's what it does. So "GarciaL" becomes "HillL".

You have to get things back into sync so that the name which is used as part
of the file name is the same as what is in columns F and B.

Is there any way to change the macro to deal with the typed name only? The customer last name in Column F is the actual name in the customer record. If multiple customers have the same last name the file names must be modified.

Again, I really appreciate your help.
Vince

 

[Guest]

The fundamental problem is that when the macro was written, the 2
specifications were that (1) the person's last name could be found to the
right of the 1st space in the text in column F on the 1st sheet, and in column
B on the 2nd sheet, and (2) the workbook name consisted of that last name
followed immediately by ".xls"

Then you hit a problem: that naming scheme can no longer work because you have
two people with the same last name. When that occurred, (1) the file was
created and named "GarciaL.xls" (you haven't said how the name is assigned).
(2) But in the workbook, BPR!A1 gives the last name, Garcia, not the filename
GarciaL.

So the macro can no longer work because the 2nd assumption -- that the file
name is always the last name + .xls -- has been violated. It sounds like you
need TWO cells on the worksheet, one containing the client's name, the other
the base file name.

If you set up a 2nd cell for the file name, then you would need to do that for
*all* existing workbooks. Then you have to change the formula in column F to
look at the cell containing the file name instead of cell $A$1. The 2nd macro
has to be changed to look for the file name not in column B, but in a new
column.

BTW, you *DO* need to resolve the underlying problem here. You could just
manually fix the worksheet names in the formulas in the rows below the Garcia
row. But the problem will recur when (a) you have another duplicate client
name, or (b) you try to insert a new client after Garcia.

the example I gave of Garcia.Lettie; then GarciaL, is really what happened.
The formula in Column F is:
...&" "&LEFT('\\Server1\Daily 2005\[Garcial.xls]BPR'!$A$1,9)

Hi, Myrna:

Can you tell me what I am doing wrong in dealing with this discussion group.
I can't seem to get in sync with your replies. I keep checking the "notify
me of replies box" but never get notified and twice Microsoft has a problem
with my input so it loses it.

But, back to the macro, I did not specify that the file name must be the
same as the customer name. That was an assumption of the person who wrote
the macro. Quite simply, all I want to do is change the file name used in
the formulae on the previous row to the file name entered in the Name box.
Again, thank you for your continued assistance.
Vince

 

[Myrna Larson]

The question ISN'T what should the NEW file name be. It's what is the OLD file
name that is to be replaced.

The cell on the worksheet, in column F, says the old *last name* is Garcia and
therefore old *file name* is SUPPOSED TO BE Garcia. The trouble is, it ISN'T
Garcia. It's GARCIAL. How is the macro supposed to figure that out? How would
the macro know that in *this* case it should add an L to the last name to get
the file name?

If you KNOW what the old file name is, then you need two input boxes, one to
ask the user for the old name, the other to request the new name.

As far as you being notified of responses, I normally post only the the
newsgroup. I don't send an email copy because most people don't use a real
address because of the spam problem. You really need to keep checking the
group for a reply.

I've sent a copy of this to the address (e-mail address removed). I
don't know that will matter or nt.

The fundamental problem is that when the macro was written, the 2
specifications were that (1) the person's last name could be found to the
right of the 1st space in the text in column F on the 1st sheet, and in column
B on the 2nd sheet, and (2) the workbook name consisted of that last name
followed immediately by ".xls"

Then you hit a problem: that naming scheme can no longer work because you have
two people with the same last name. When that occurred, (1) the file was
created and named "GarciaL.xls" (you haven't said how the name is assigned).
(2) But in the workbook, BPR!A1 gives the last name, Garcia, not the filename
GarciaL.

So the macro can no longer work because the 2nd assumption -- that the file
name is always the last name + .xls -- has been violated. It sounds like you
need TWO cells on the worksheet, one containing the client's name, the other
the base file name.

If you set up a 2nd cell for the file name, then you would need to do that for
*all* existing workbooks. Then you have to change the formula in column F to
look at the cell containing the file name instead of cell $A$1. The 2nd macro
has to be changed to look for the file name not in column B, but in a new
column.

BTW, you *DO* need to resolve the underlying problem here. You could just
manually fix the worksheet names in the formulas in the rows below the Garcia
row. But the problem will recur when (a) you have another duplicate client
name, or (b) you try to insert a new client after Garcia.

the example I gave of Garcia.Lettie; then GarciaL, is really what happened.
The formula in Column F is:
...&" "&LEFT('\\Server1\Daily 2005\[Garcial.xls]BPR'!$A$1,9)

Hi, Myrna:

Can you tell me what I am doing wrong in dealing with this discussion group.
I can't seem to get in sync with your replies. I keep checking the "notify
me of replies box" but never get notified and twice Microsoft has a problem
with my input so it loses it.

But, back to the macro, I did not specify that the file name must be the
same as the customer name. That was an assumption of the person who wrote
the macro. Quite simply, all I want to do is change the file name used in
the formulae on the previous row to the file name entered in the Name box.
Again, thank you for your continued assistance.
Vince

 

[Guest]

The question ISN'T what should the NEW file name be. It's what is the OLD file
name that is to be replaced.

The cell on the worksheet, in column F, says the old *last name* is Garcia and
therefore old *file name* is SUPPOSED TO BE Garcia. The trouble is, it ISN'T
Garcia. It's GARCIAL. How is the macro supposed to figure that out? How would
the macro know that in *this* case it should add an L to the last name to get
the file name?

If you KNOW what the old file name is, then you need two input boxes, one to
ask the user for the old name, the other to request the new name.

As far as you being notified of responses, I normally post only the the
newsgroup. I don't send an email copy because most people don't use a real
address because of the spam problem. You really need to keep checking the
group for a reply.

I've sent a copy of this to the address (e-mail address removed). I
don't know that will matter or nt.

The fundamental problem is that when the macro was written, the 2
specifications were that (1) the person's last name could be found to the
right of the 1st space in the text in column F on the 1st sheet, and in column
B on the 2nd sheet, and (2) the workbook name consisted of that last name
followed immediately by ".xls"

Then you hit a problem: that naming scheme can no longer work because you have
two people with the same last name. When that occurred, (1) the file was
created and named "GarciaL.xls" (you haven't said how the name is assigned).
(2) But in the workbook, BPR!A1 gives the last name, Garcia, not the filename
GarciaL.

So the macro can no longer work because the 2nd assumption -- that the file
name is always the last name + .xls -- has been violated. It sounds like you
need TWO cells on the worksheet, one containing the client's name, the other
the base file name.

If you set up a 2nd cell for the file name, then you would need to do that for
*all* existing workbooks. Then you have to change the formula in column F to
look at the cell containing the file name instead of cell $A$1. The 2nd macro
has to be changed to look for the file name not in column B, but in a new
column.

BTW, you *DO* need to resolve the underlying problem here. You could just
manually fix the worksheet names in the formulas in the rows below the Garcia
row. But the problem will recur when (a) you have another duplicate client
name, or (b) you try to insert a new client after Garcia.

the example I gave of Garcia.Lettie; then GarciaL, is really what happened.
The formula in Column F is:
...&" "&LEFT('\\Server1\Daily 2005\[Garcial.xls]BPR'!$A$1,9)

Hi, Myrna:

Can you tell me what I am doing wrong in dealing with this discussion group.
I can't seem to get in sync with your replies. I keep checking the "notify
me of replies box" but never get notified and twice Microsoft has a problem
with my input so it loses it.

But, back to the macro, I did not specify that the file name must be the
same as the customer name. That was an assumption of the person who wrote
the macro. Quite simply, all I want to do is change the file name used in
the formulae on the previous row to the file name entered in the Name box.
Again, thank you for your continued assistance.
Vince

Aha! The light finally dawns. I now understand what you have been telling me. How could the macro be changed so the file names could be entered in a separate column (say Column A) which would not be printed with the report? Therefore the OLD file name would always be whatever is in the row above; the NEW file name would be typed in Column A on the subject row. Then anything could be printed in Column F (which would now be Column G). This is really what I wanted to do in the first place. I could use that macro in a dozen easily programmed future new/different reports.

Apparently this macro merely copies across the row until it comes to a blank
cell - this is what I really need. There are a number of occasions where I'd
like to create and run a one-time report looking at one, two, or more pieces
of data in each customer file.

I'd really appreciate it if you could modify the macro to do just that.
Best regards,
Vince

 

[Myrna Larson]

If you follow your new plan, to put the base FILENAME in column A, the
following macro should work for either the data sheet OR the summary sheet. In
the problem case that started all of this, the client name is Garcia, but the
name in column A would be the file name, GarciaL.

The macro just finds the last name in column A and saves it in a variable.
Then it copies the entire next-to-last row to the last row. Then it replaces
the OldName with the NewName that was saved before the copy.


Sub AddLineData() ' to add a row on data page OR summary page
Dim lr As Long
Dim NewName As String
Dim OldName As String
Application.DisplayAlerts = False
Application.ScreenUpdating = False

'the new name must already have been entered in column A
'of the last row, but the rest of the row is blank

lr = Cells(Rows.Count, "A").End(xlUp).Row
NewName = Cells(lr, 1).Value 'save this because copy will wipe it out

'next line overwrites the new name, but it doesn't matter
'because we saved it above
Rows(lr - 1).Copy Rows(lr)

'replace the old name with the new name
OldName = Cells(lr - 1, 1).Value
Rows(lr).Replace What:=OldName, Replacement:=Newname, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False

Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

 

[Guest]

If you follow your new plan, to put the base FILENAME in column A, the
following macro should work for either the data sheet OR the summary sheet. In
the problem case that started all of this, the client name is Garcia, but the
name in column A would be the file name, GarciaL.

The macro just finds the last name in column A and saves it in a variable.
Then it copies the entire next-to-last row to the last row. Then it replaces
the OldName with the NewName that was saved before the copy.


Sub AddLineData() ' to add a row on data page OR summary page
Dim lr As Long
Dim NewName As String
Dim OldName As String
Application.DisplayAlerts = False
Application.ScreenUpdating = False

'the new name must already have been entered in column A
'of the last row, but the rest of the row is blank

lr = Cells(Rows.Count, "A").End(xlUp).Row
NewName = Cells(lr, 1).Value 'save this because copy will wipe it out

'next line overwrites the new name, but it doesn't matter
'because we saved it above
Rows(lr - 1).Copy Rows(lr)

'replace the old name with the new name
OldName = Cells(lr - 1, 1).Value
Rows(lr).Replace What:=OldName, Replacement:=Newname, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False

Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Thank you! Thank you!! Thank you!!! It works beautifully. To stretch my luck, could you give me a modification of this macro which looks for (begins with) the name in A6 and continues copying as long as there is a name in Column A. This way I could copy and paste a list of names into Column A5 through A whatever. Then I could write formulas in Row 5 from B5 to whatever. Then hit the "name" button to begin and the macro would copy beginning at Row 6 until the end of names in Column A.

I really appreciate this. It is the next-to-final step in what I consider
to be a great system for manufactured home dealerships. There is one more
thing I need. If you aren't sorry you offered to help me by now, perhaps I
could ask one more thing after this one.
Thanks again,
Vince

 

[Myrna Larson]

The following looks for the last name entered in column A, and the last
formula entered in column B. Then if fills the formulas down through all of
the rows that have names. Then it goes through the newly filled rows,
replacing the name that was used in the last formula row with the name that's
in column A of the current row.

As far as more macros are concerned, here's my reaction. Since you are
"designing" a business system, I think it's imperative that YOU understand how
the macros work and are able to modify or fix them if needed and how to write
more code for other tasks. Maybe this would be a good point for you to start
on that aspect of it. Otherwise, since this is a business application, you
probably should hire an Excel consultant who will guarantee his work.

Option Explicit

Sub CopyFormulasDown()
Dim LastFormulaRow As Long
Dim LastNameRow As Long
Dim OldName As String
Dim R As Long

With ActiveSheet
'find last row with a formula -- use column B
LastFormulaRow = .Cells(.Rows.Count, 2).End(xlUp).Row
'find last row with a name -- column A
LastNameRow = .Cells(.Rows.Count, 1).End(xlUp).Row
'if there are no rows without formulas, quit
If LastNameRow <= LastFormulaRow Then Exit Sub

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'fill the formulas down
.Cells(LastFormulaRow, 2) _
.Resize(LastNameRow - LastFormulaRow + 1, 254).FillDown

'get the old name used in those formulas from column A
OldName = .Cells(LastFormulaRow, 1).Value

'go through all rows that were without formulas, replacing that
'old name with what's in column A of the current row
For R = LastFormulaRow + 1 To LastNameRow
.Rows(R).Replace what:=OldName, Replacement:=.Cells(R, 1).Value, _
LookAt:=xlPart, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Next R

'recalculate the sheet
.Calculate

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End With
End Sub

Thank you! Thank you!! Thank you!!! It works beautifully. To stretch my

luck, could you give me a modification of this macro which looks for (begins
with) the name in A6 and continues copying as long as there is a name in
Column A. This way I could copy and paste a list of names into Column A5
through A whatever. Then I could write formulas in Row 5 from B5 to whatever.
Then hit the "name" button to begin and the macro would copy beginning at Row
6 until the end of names in Column A.

 

[Guest]

The following looks for the last name entered in column A, and the last
formula entered in column B. Then if fills the formulas down through all of
the rows that have names. Then it goes through the newly filled rows,
replacing the name that was used in the last formula row with the name that's
in column A of the current row.

As far as more macros are concerned, here's my reaction. Since you are
"designing" a business system, I think it's imperative that YOU understand how
the macros work and are able to modify or fix them if needed and how to write
more code for other tasks. Maybe this would be a good point for you to start
on that aspect of it. Otherwise, since this is a business application, you
probably should hire an Excel consultant who will guarantee his work.

Option Explicit

Sub CopyFormulasDown()
Dim LastFormulaRow As Long
Dim LastNameRow As Long
Dim OldName As String
Dim R As Long

With ActiveSheet
'find last row with a formula -- use column B
LastFormulaRow = .Cells(.Rows.Count, 2).End(xlUp).Row
'find last row with a name -- column A
LastNameRow = .Cells(.Rows.Count, 1).End(xlUp).Row
'if there are no rows without formulas, quit
If LastNameRow <= LastFormulaRow Then Exit Sub

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'fill the formulas down
.Cells(LastFormulaRow, 2) _
.Resize(LastNameRow - LastFormulaRow + 1, 254).FillDown

'get the old name used in those formulas from column A
OldName = .Cells(LastFormulaRow, 1).Value

'go through all rows that were without formulas, replacing that
'old name with what's in column A of the current row
For R = LastFormulaRow + 1 To LastNameRow
.Rows(R).Replace what:=OldName, Replacement:=.Cells(R, 1).Value, _
LookAt:=xlPart, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Next R

'recalculate the sheet
.Calculate

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End With
End Sub


luck, could you give me a modification of this macro which looks for (begins
with) the name in A6 and continues copying as long as there is a name in
Column A. This way I could copy and paste a list of names into Column A5
through A whatever. Then I could write formulas in Row 5 from B5 to whatever.
Then hit the "name" button to begin and the macro would copy beginning at Row
6 until the end of names in Column A.

Thank you again, Myrna. Your macro works like a dream come true!!! I want to get

back to you to say you are certainly correct in your suggestion. Let me
tell you why I
haven't done so and to see if you have an interest in the following:

I have been designing and modifying this system for the manufactured home
dealership
for which I am General Partner for five years. When I had to take it over
the dealership
due to a bad loan the partnerships for which I am general partner had made I
concluded
the best way to get our money back was to operate the dealership. Due to
problems in the
industry at that time, etc., we have lost $100,000/year for 5 years; finally
it is turning
around. There was no computer system there nor (apparently) was there any
systems help
available from the state or national associations. So I began slowly to
develop a useable
system and have modified it as we added product lines, changed various
business
practices, etc.

In 1957, yes, 57. I began as (an adding machine salesman, then) a
bookkeeping machine
salesperson who had to design the system and program the machines I sold.
Throughout
later years as a product manager, etc. I learned Basic and Fortran (and
could read some
Cobol) by studying the programs for which I was manager. But, Virtual Basic
is like
Sanskrit to me. I have glanced at the manual multiple times and haven’t
found a place to
start. On the other hand, I have been able to accomplish my system goals
with only a
limited knowledge of Excel. My mantra has been “Get the job done now and
enhance it
later.â€

Long story short, I enjoy doing the system in Excel. I know, as a “managerâ€
I shouldn’t
allow myself to enjoy programming the system, but one has to have some
pleasure in life
- my wife does cryptograms for fun when she isn’t helping me with the
partnership work -
and I try to improve my system design for fun. I have been able to do
almost everything I
needed to do without outside assistance. The system handles everything from
the time a
customer walks into the dealership until the home is installed and paid for.
But a couple
of reporting capabilities needed a little help.

I’m at the point where, after one more macro or formula to create a daily
“To Do†report,
I will be able to spend some time on preparing to sell it to the 7,000
dealerships in the
U.S. I can demonstrate it. It is in use daily. Therefor I should be able
to sell it. If I can
sell it, then I will have the money to pay for whatever level of programming
competence
is needed.

Chicken or egg; you ask. I understand that my approach may not be the most
logical
business approach but it’s the approach I can handle right now. This, plus
my other
duties, keeps me working during most of 12 hours each day, 6 or 7 days/week.

I am aware I will need a partner or partners. I really don’t have time to
“sell.†I have
limited capability and time to make system modifications for a new user; I
believe I will
need to find a sales organization which can promote the product; therefore
maybe a
partner to find the actual sales organization and interface with it (and
sell the state
associations and national association to promote the system so that they can
gather
statistics on our future Internet output), and a partner to deal with system
modifications
and manage a staff to make modifications for clients. My objective is to
accomplish this
with “partners†rather than a front-end outlay of capital.

Do you (or anyone in whom you have confidence) want to propose a joint
venture?

Best regards,
Vince

 

[Myrna Larson]

I'm glad the macro works as you want. As far as your current situation, I can
understand the problem. However, I can't see how you can sell something that
doesn't yet exist. You want to sell a completely computerized business system,
but you don't yet have that. Sounds to me like you need to list the system
specifications, get financing somewhere, somehow, and hire an Excel
consultant/programmer to write the code.

Chip Pearson and Rob Bovey, both Excel MVPs, do that sort of work. I am
retired and not interested in doing commercial programming that could entail
legal liability.

 

[Guest]

I'm glad the macro works as you want. As far as your current situation, I can
understand the problem. However, I can't see how you can sell something that
doesn't yet exist. You want to sell a completely computerized business system,
but you don't yet have that. Sounds to me like you need to list the system
specifications, get financing somewhere, somehow, and hire an Excel
consultant/programmer to write the code.

Chip Pearson and Rob Bovey, both Excel MVPs, do that sort of work. I am
retired and not interested in doing commercial programming that could entail
legal liability.

Hi, Myna:

Thank you for the names of the the Excel MVPs, Chip Pearson and Rob Bovey.

Somehow I have given you the impression the system is not complete. The
manufactured
home dealership business is very complex and my system has been used for all
facets of
the business for approximately five years. The reports have been available
almost from
the beginning. The 1st macro makes it easier for me to add names to the
Prospective
Business Report and the Installation Report as the year progresses; the
second macro is to
make it easier for me to make a quick and dirty report for my own use now or
for
whatever might be required in the future.

The thing my system lacks (which would be automatic in Access) is the
ability to add
customer names to all reports automatically. Instead, I, or a system
administrator, must
add them.

The macro I need now is only because salespersons are poor record keepers.
My reports
tell them what functions are delinquent as of the report date. Just like a
bunch of
kindergarteners, they don’t pay attention to a simple comprehensive report.
To make
them pay attention, I want a report that tells them what must be done TODAY.
Ridiculous? Yes! Necessary? Shouldn’t be but, Yes!!!

In case you are willing to do one more macro I will define a daily report.

Each row will handle one customer (File Name). (10 rows, 10 customers) I
will use your
macro to add customers to the report as the year progresses.

Column A = File Name

Column B = Sales activity that should be done today. Source: If any cell
in (FileName)
B26 through B68 equals TODAY() put the adjacent words from C26 through C68
in this
cell. if more than one date equals TODAY() concatenate the adjacent words
from C26
through C68 after printing a semicolon space between each date/comment
..
I will make the format in this cell wrap and shrink to fit.

Column C = Construction activities which should be completed today. If any
cell in
(FileName) M2 through M33 equals TODAY(), and if N2 through N33 equals “?â€,
put
the adjacent words from K2 through K33 into this cell; if more than one date
equals
TODAY() and adjacent contents of N equals “?â€, concatenate the adjacent
words from
K2 through K33 after printing a semicolon space.
I will make the format in this cell wrap and shrink to fit.

Columns D through whatever = my formulae to look at other pertinent dates in
the
customer file. Your 1st macro will be used to add names to each
salesperson's Daily Activity Report.

Thanks again for listening to me.
Vince