copy values as links to corresponding columns

I would like to add a final stage to the macro below so it will do the following;

Sub SendEmail()
'///Set up the Excel variables.
    Const MsgSignature As String = "Supplier Relationship Team"
    Const sSubj As String = "Supplier Meeting Action Points"
    Dim rCl As Range, rRng As Range
    Dim OLApp As Object, olMailItm As Object
    Dim iCnt As Integer
    Dim sTo As String, sMsg As String

    '///Create the Outlook application and the empty email.
    Set OLApp = CreateObject("Outlook.Application")
    Set olMailItm = OLApp.CreateItem(0)

    sMsg = "Hi,<br><br>" & _
           "Please could you complete the action points found on the supplier meeting report (link below) <br><br>" & _
           "Once you have completed your action points please change the status from the drop down list found in column C/D and leave any relevant comments in the STAKEHOLDER COMMENTS column.<br><br>" & _
           "Once completed please click the 'close' button found under the SUPPLIER MEETINGS tab at the top of the page.:<br><br>" & _
           "If you have any questions regarding your action points please contact the appropriate category manager found in cell E6 of the sheet.:<br><br>" & _
           "Please open the meeting report using the link below and select the meeting report link called " & Range("B2") & " found in column E<br><br>" & _
           "Click on the link below to open the file (Click 'Ok' and 'Continue' to all prompts when opening the file) :<br><br> " & _
           "<A HREF=""file://" & ActiveWorkbook.FullName & _
           """>Link to the file</A>" & _
           "<br><br>Regards," & _
           "<br><br>" & MsgSignature

    '/// create list of recipients

    Set rRng = Range("B86:B93")

    With olMailItm

        For Each rCl In rRng.Cells
            If rCl.Value > 0 Then
                If sTo = "" Then
                    sTo = rCl.Value
                    sTo = sTo & ";" & rCl.Value
                End If
                iCnt = iCnt + 1
            End If
        Next rCl

        If iCnt < 2 Then
            MsgBox "You nave not entered any recipients.", vbCritical, MsgSignature
            Exit Sub
        End If

        .To = sTo
        .Subject = sSubj
        .HTMLBody = sMsg
    End With

    '///Clean up the Outlook application.
    Set olMailItm = Nothing
    Set OLApp = Nothing

    Exit Sub

    MsgBox Error(Err)
    Resume Next

End Sub

Open in new window

1. Copy all values (including blank cell values) from the range A14:E18 and paste under the relevant column headings found in sheet result_example

The values and blank values should be pasted as links with the original formatting.

Example Result: The sheet result_example provides a prototype result of what should be achieved when the macro is run.

Note 1: The  columns have the same headings (except F)  but are not in the same order/layout on the destination sheet therefore a straight copy and paste will not be possible, instead cell values should be matched to the corresponding columns headings.

Note 2: The reason the values must be pasted as links is because the data in the original worksheet will be updated over time. Blank cells may also be populated at a later date.

Taking the attached example you will notice that cell values copied from row 18 on the original sheet are all blank . This means that the cells in the bottom row (6) in the destination sheet will be completely blank( except column  F)  but will be linked. This means that if the blank cells in row 18 on the original sheet are populated then the blank cells in row 6 on the destination sheet will also be populated.

I have highlighted the blank cells red and provided an explanation of the required mechanism in a key on the attached sheet.

Note 3:

Column F will be populated with a hyperlink. The hyperlink will be generated using coding - An example of this is found in the code snippet below. Ultimately the value of the hyperlink will be the same as the worksheet name i.e. Sterling 09.07.2015. This hyperlink will send the user back to the original sheet. Note all five rows will be populated with this same hyperlink even if the other cells in the rows are blank.

Sub ImportForm()
    Dim wbLog As Workbook
    Dim MainSht As Worksheet, LogSht As Worksheet, CopySht As Worksheet
    Dim rData As Range
    Dim NewRw As Long
    Dim sFil As String, sTitle As String, sWb As String
    Dim iFilterIndex As Integer

    Set MainSht = ActiveSheet
    ' Set up list of file filters
    sFil = "Excel Files (*.xl*),*.xl*"
    ' Display *.xls by default
    iFilterIndex = 1
    ' Set the dialog box caption
    sTitle = "Select  File to Zip"
    ' Get the filename
    With Application
        sWb = .GetOpenFilename(sFil, iFilterIndex, sTitle)
        Set wbLog = Workbooks.Open(sWb)
        '        On Error GoTo err_handler
        .ScreenUpdating = False
        .DisplayAlerts = False

        Set LogSht = Worksheets("Meetings.Task.Status.Log")

        MainSht.Copy After:=wbLog.Sheets(wbLog.Sheets.Count)
        ActiveSheet.Name = ActiveSheet.Range("B2").Value
        Set CopySht = ActiveSheet

        Set rData = LogSht.Range("A1").CurrentRegion.Offset(1)
        NewRw = rData.Rows.Count + 1

        NewRw = rData.Rows.Count
        rData.Cells(NewRw, 1).Select
        ActiveSheet.Paste Link:=True
        'company name
        rData.Cells(NewRw, 2).Select
        ActiveSheet.Paste Link:=True
        'date of meeting
        rData.Cells(NewRw, 3).Select
        ActiveSheet.Paste Link:=True
        rData.Cells(NewRw, 4).Select
        ActiveSheet.Paste Link:=True
        'add link
        rData.Cells(NewRw, 5).Select
        LogSht.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
                              "'" & CopySht.Name & "'!A1", TextToDisplay:=CopySht.Name

        .ScreenUpdating = True
        .CutCopyMode = False
        .DisplayAlerts = True
    End With

    Exit Sub
    MsgBox "No file selected", vbCritical
    Resume clean_up
End Sub

Open in new window

Note 4:

The code will be run on multiple sheets. Each time data from a new sheet is added it should be added to the next available row and not over the current data.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
Hi Mike

I'll have a look at get back to you as quickly as possible.
Roy CoxGroup Finance ManagerCommented:
Hi Mick

Can I add any empty rows to the template?
mikes6058Author Commented:
Sorry Roy, could you elaborate, I'm not entirely sure what you mean.

I'm sure it won't be a problem

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Roy CoxGroup Finance ManagerCommented:
I don't think I will need to change the template after all.
mikes6058Author Commented:
mikes6058Author Commented:
Hi Roy,

I was just wondering how this one was going? Do you need anymore information?

Roy CoxGroup Finance ManagerCommented:
Hi Mike

I think this does it.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mikes6058Author Commented:
barring a couple of tweaks I've made near enough spot on first time.

Thanks Roy
Roy CoxGroup Finance ManagerCommented:
That's good.I'll look at the other issuewith dates  later
mikes6058Author Commented:
Hi Roy,

I've come across a problem with the macro.

As I requested the macro will add 5 lines to the next free row available in the worksheet. The trouble is that 90% of the time this work sheet will be filtered so that column A excludes the blank cells leaving only the names in column A. When I run the macro and there is a filter on column A it is causing less info to be copied over to the destination workhseet.

When there is no filter applied it works fine and all 5 rows and the correct values are copied over as links.

Perhaps a way of getting round this would be for the macro to unfilter column A>>>then run the current code>>> then reapply the filter*

*filter = select all values in column A excluding (blanks)

Perhaps you may know of a more efficient way of doing this.

I've tweaked the current code (see below) so if you could apply the changes to this and re-paste a a snippet of the new code it would be much appreciated.

Thanks Mike

Option Explicit

Sub NewCode()
''/// the sheet name will need to be changed to actual name used in the final workbook

    If ActiveSheet.Name = "Stakeholder_Actionpoints" Then
        MsgBox "You have the rsults sheet active.", vbCritical, "Error"
        Exit Sub
    End If
    Dim ws As Worksheet
    Dim rToCopy As Range
    Dim lRw As Long
    Dim iX As Integer
    '1. Copy all values (including blank cell values) from the range A14:E18 and paste under the relevant column headings found in sheet Stakeholder_Actionpoints
    Set ws = ActiveSheet
    Set rToCopy = ws.Range(Cells(14, 1), Cells(18, 5))

    With Sheet55
        lRw = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
        Application.Goto .Cells(lRw, 2)
        ActiveSheet.Paste Link:=True
        Application.Goto .Cells(lRw, 1)
        ActiveSheet.Paste Link:=True
        Application.Goto .Cells(lRw, 5)
        ActiveSheet.Paste Link:=True
        Application.Goto .Cells(lRw, 3)
        ActiveSheet.Paste Link:=True
        Application.Goto .Cells(lRw, 4)
        ActiveSheet.Paste Link:=True

        For iX = 1 To rToCopy.Rows.Count

            .Hyperlinks.Add Anchor:=.Cells(lRw, 6), Address:="", SubAddress:= _
                            "'" & ws.Name & "'!A" & iX + 13, TextToDisplay:=ws.Name

            lRw = lRw + 1

        Next iX
    End With
End Sub

Open in new window

Roy CoxGroup Finance ManagerCommented:
I'll add some code to check if the range is filtered then remove the filter before copying
Roy CoxGroup Finance ManagerCommented:
Hi Mike

I've updated the code to work with AutoFiltered Data.

Run the Save_Restore_Filter
mikes6058Author Commented:
Hi Roy,

Thanks for looking over this again. I've tested the new code but I seem to be getting the same result whereby rows are being missed when column A is filtered to exclude blanks.

I have highlighted the copied rows in yellow on the attached. These are the rows which were successfully copied over when I tested the code. You will notice the top row of cells from the sterling sheet is still missing.

I've tested it with the filter removed (all values selected) on the filter in column A and it works with all 5 rows being copied over...

Roy CoxGroup Finance ManagerCommented:
I'll check later, but the code should now remove the filter, copy & paste, then restore the filter
mikes6058Author Commented:
Thanks Roy, that exactly what I want it to do.

mikes6058Author Commented:
Hi Roy,

Did you manage to get a chance to re-test the code?

Roy CoxGroup Finance ManagerCommented:

Are you using the Save_Restore_Filter macro?
mikes6058Author Commented:
Hi Roy,

I think I may have figured out whats causing the confusion. I am referring to when column A in the result_example sheet is filtered so that all values excluding blanks are displayed. No filtering will take place on the meeting report sheet i.e sterling 08.07.2015

I've run the save_restore_filer macro with column A on the result_example sheet filtered to exclude blanks but again only 4 rows have been copied over. when I clear the filter on column A in the result_example sheet the macro will work correctly and all 5 rows will be copied over......

I have highlighted the actual result from the macro on the result_example sheet - note only 4 rows copied over

the expected result sheet demonstrates what I am expecting the macro to achieve. Note Column A has been re filtered excluding blanks in column A  which is what the macro should do.
mikes6058Author Commented:
I've set this up as a new question for you as I have already awarded points for this question.

See link below.

Note: The new question just repeats the original question from this feed but please refer to my last comment in this feed when working on the solution.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.