troubleshooting Question

Placement of a Function

Avatar of Rob Henson
Rob HensonFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft ExcelVBA
40 Comments4 Solutions214 ViewsLast Modified:
Hi All,

i am using the following basic Function to collate a table of comments into a single cell for onward copying into a consolidation file:

Public Function AllComments(Rng As Range)

R = 0
LR = Rng.Cells.Count
For Each Cell In Rng
    Newline = ""
    R = R + 1
    If R < LR Then Newline = Chr(10)
    Collated = Collated & Cell.Text & Newline
Next Cell
    AllComments = Collated
End Function
I am distributing this Function to numerous project files and I was looking to Automate the process.

The function is currently held in a Standard module and I am having to copy the code in the VBE and copy the sheet on which it is used.

Can the function be in the sheet code rather than a module? How do I get the Function to work when it is in the sheet code; I couldn't get it to work like that which is why it is in a standard module.

Also, on a separate but related issue: the file from which I am copying the sheet has lots of Named Ranges. When I copy the sheet I end up with copies of the Named Ranges in the destination file. The destination file already has all of these Named Ranges so I get two copies of each Named Range, one with Workbook scope and one with scope of the copied sheet.

On this occasion the sheet is very simple so it would be just as simple to insert a sheet and copy paste the contents onto the new sheet but I have seen this occur before and on those occasions it wasn't as simple so have had to go in and delete the surplus Named Ranges. I have tried recording this process with the VB Recorder but the script that I end up does not show any indication that it is the Sheet scope Range that it is deleting. Is there a way of defining this when deleting the range name?

Rob H
Join our community to see this answer!
Unlock 4 Answers and 40 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 4 Answers and 40 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros