Macro - change work sheet name & create hyperlink

I would now like the macro to perform the following 3 tasks

1.
After the worksheet has been copied to the supplier meetings status sheet I would like the macro  to rename the active sheet as the value present in cell H2.

2.
Create a hyper-link on the new copied sheet using the value in H2. (see example). The hyper-link will send the user to cell A1 in the active sheet.

3.
I would then like the macro to copy (as links) a number of the value's from the now copied sheet to "meetings.task.status.log"
You will see an example of the cells I would like to copy and paste as links. These values must be pasted as live links.

Note: As this is a repeat process, each time the links will be copied from a new copied sheet, they must be added as a new row in the meetins.task.status.log and must not overwrite the current rows

I've attached an example copy of the original workbook "original.source" and copy of the "Supplier Meetings Status". for testing purposes.

Note: I have assigned the macro to button "copy to log" on the "original.source" file.

Thanks
Mike
Supplier-Meetings-status.xlsm
original.source.xlsm
mikes6058Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

mikes6058Author Commented:
Note this question is a continuation from the previous question...

http://www.experts-exchange.com/questions/28693204/Edit-macro-copy-sheet-to-another-workbook.html
Roy CoxGroup Finance ManagerCommented:
s the code in the attached example what you are actually using?
mikes6058Author Commented:
Hi Roy,

Yes I am looking to add these processes to the macro in the original source file

Mike
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

Roy CoxGroup Finance ManagerCommented:
I'll take a look
Roy CoxGroup Finance ManagerCommented:
Why not have code in the master workbook to allow the user to open a workbook and copy the sheet into the master workbook? That way you don't need buttons in the original source or code and won't have the problem of deleting those buttons after import.
mikes6058Author Commented:
HI Roy,

The code has to be in the original source file as the active sheet which will actually form part of a much larger workbook where the user is constantly working from.

If you could look at the coding though at would be great!

Mike
Roy CoxGroup Finance ManagerCommented:
So just to be clear, you want the code in the original workbook to copy the sheet across and update the log? This will move the buttons as well.

Will both workbooks be open?

I have started looking at the code and will post you something later, maybe in the morning as I am busy at work.
mikes6058Author Commented:
Yes this is correct,yes I am aware the buttons will move over, this is not a problem. They do not have to be functional on the log workbook.

I also want the name of the copied sheet to be renamed as the value in cell B2

It is also important that the values updated into the log are linked to the relocated meeting report sheet so they update automatically when the meeting report is changed. The Value in B2 should be copied as a hyper-link so the user can easily locate a sheet with a "STILL OUTSTANDING ACTION POINTS" completion status.

Also each time a new sheet is copied over the data added to the log should be added as a new row rather than over writing the data from previous sheets.

The original source file will be open and should stay open once the macro is complete. The log workbook will not open until the macro begins. It should also stay open once the macro finished.

I've altered the layout of the two files please find attached.
original.source.xlsm
Supplier-Meetings-status.xlsm
Roy CoxGroup Finance ManagerCommented:
Hi Mike

I'll adapt the code that I have already and post back.
mikes6058Author Commented:
That's great thanks Roy
Roy CoxGroup Finance ManagerCommented:
Hi Mike

Can you test this and let me know if I'm on the right track.

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

        CopySht.Range("e3").Copy
        LogSht.Select
        'manager
        NewRw = rData.Rows.Count
        rData.Cells(NewRw, 1).Select
        ActiveSheet.Paste Link:=True
        'company name
        CopySht.Range("B2").Copy
        rData.Cells(NewRw, 2).Select
        ActiveSheet.Paste Link:=True
        'date of meeting
        CopySht.Range("B7").Copy
        rData.Cells(NewRw, 3).Select
        ActiveSheet.Paste Link:=True
        CopySht.Range("E2").Copy
        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"

   

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

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

Open in new window

mikes6058Author Commented:
Hi Roy,

Brilliant, 99% there. I'd just like to make a couple of small tweaks.

On occasion the user will need to import the original source multiple times before closing the log. At the moment if I need to import for a second time the log needs to be saved and is then technically re-opened when the macro is run for the 2nd time. Is it possible to keep the log open and run the macro for a second, third, forth time.... without having to save the log and then reopen?

Also another minor thing. At the moment the hyperlink which is copied into column E on the log displays the cell reference i.e. Ledco 30.06.2015!'A1 - Is it possible to change it so the hyperlink is displayed without the cell reference i.e. Ledco 30.06.2015

Otherwise great job!

Mike
mikes6058Author Commented:
Hi Roy,

Once you've managed to take a look at the tweaks outlined above you may want to take a look at another question I've just raised. Its linked to this question but should also borrow a few of the techniques and solutions you utilised when working on the query log project for me.

http://www.experts-exchange.com/questions/28694570/send-email-with-hyper-link-macro.html

Thanks Mike
Roy CoxGroup Finance ManagerCommented:
This should fix the hyperlink

   
 LogSht.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
                              "'" & CopySht.Name & "'!A1", TextToDisplay:=CopySht.Name

Open in new window


Will the source workbook always have the same name?
mikes6058Author Commented:
Yes the source workbook will always have the same name and location
Roy CoxGroup Finance ManagerCommented:
So we don't need the open file dialog.  Try this, enter the full path of your workbook where I have written " enter the full file path here"


Sub ImportForm()
    Const sWb As String = " enter the full file path here"
    Dim MainSht As Worksheet, LogSht As Worksheet, CopySht As Worksheet
    Dim rData As Range
    Dim NewRw As Long

    Set MainSht = ActiveSheet

    If Not IsFileOpen(sWb) Then
        Set wbLog = Workbooks.Open(sWb)
    Else: Workbooks(Dir(sWb)).Activate
        '        On Error GoTo err_handler

        With Application
            .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
            CopySht.Range("e3").Copy
            LogSht.Select
            '//manager
            NewRw = rData.Rows.Count
            rData.Cells(NewRw, 1).Select
            ActiveSheet.Paste link:=True
            '// company Name
            CopySht.Range("B2").Copy
            rData.Cells(NewRw, 2).Select
            ActiveSheet.Paste link:=True
            '//  date of meeting
            CopySht.Range("B7").Copy
            rData.Cells(NewRw, 3).Select
            ActiveSheet.Paste link:=True
            CopySht.Range("E2").Copy
            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


clean_up:

            .ScreenUpdating = True
            .CutCopyMode = False
            .DisplayAlerts = True
        End With
    End If
    Exit Sub
err_handler:
    MsgBox "No file selected", vbCritical
    Resume clean_up
End Sub
Function IsFileOpen(chkFile As String) As Boolean
    On Error Resume Next
    IsFileOpen = (Workbooks(chkFile).Name = chkFile)
    On Error GoTo 0
End Function

Open in new window

mikes6058Author Commented:
I've entered the path P:\Supplier Relations\Supplier Meetings status.xlsm but nothing appears to happen.

The log is opened but the sheet is not copied over and the values are not added to the log?

Mike
Roy CoxGroup Finance ManagerCommented:
See if this works, replace the file path with yours

Sub ImportForm()
    Const sWb As String = "D:\EE MIKE\EE MIKE\Supplier-Meetings-status (2).xlsm"
    Dim wbLog As Workbook
    Dim MainSht As Worksheet, LogSht As Worksheet, CopySht As Worksheet
    Dim rData As Range
    Dim NewRw As Long

    Set MainSht = ActiveSheet
    If Not wbOpen(Dir(sWb)) Then
        Set wbLog = Workbooks.Open(sWb)
    End If
    '        On Error GoTo err_handler
    With Application
        .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

        CopySht.Range("e3").Copy
        LogSht.Select
        'manager
        NewRw = rData.Rows.Count
        rData.Cells(NewRw, 1).Select
        ActiveSheet.Paste Link:=True
        'company name
        CopySht.Range("B2").Copy
        rData.Cells(NewRw, 2).Select
        ActiveSheet.Paste Link:=True
        'date of meeting
        CopySht.Range("B7").Copy
        rData.Cells(NewRw, 3).Select
        ActiveSheet.Paste Link:=True
        CopySht.Range("E2").Copy
        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"



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

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

Function wbOpen(wbName As String) As Boolean
' returns TRUE if the workbook is open
    wbOpen = False
    On Error GoTo wbNotOpen
    If Len(Application.Workbooks(wbName).Name) > 0 Then
        wbOpen = True
        Exit Function
    End If
wbNotOpen:
End Function

Open in new window

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:
this time when I run the macro it works but if I then run the macro for a second time I get the following error.

run time error 9

subscript out of range.

If I save and close the log and then re-run the macro it will work but If possible I'd like to avoid this.

Mike
Roy CoxGroup Finance ManagerCommented:
Thinking about this the code will also copy the sheet again causing an error because the sheet already exists
mikes6058Author Commented:
Oh I see what you're saying, do you think there will be a work around?
Roy CoxGroup Finance ManagerCommented:
I may be missing something, can you explain why the code needs running a second time.
mikes6058Author Commented:
Although the option is not available on the source sheet I have provided you with, the value in cell B1 (supplier name) on the source sheet I intend to apply the code to can actually be changed using a drop down (data validation). This will then change a number of values on the form.

 The user of the programme may wish to copy over the worksheet for one supplier, then change the supplier to another one using the drop down and then copy over this sheet. They may wish to copy over 5 or 6 supplier sheets to the log in one sitting.

Hopefully this makes sense.

Mike
Roy CoxGroup Finance ManagerCommented:
That sort of scenario is why I thought you would have the code in the master log workbook. That would be my way of doing it
mikes6058Author Commented:
I'm happy to do it this way.

To summarise; the user would have to select the appropriate supplier in the source workbook and then run the code in the log sheet. The result will be the same i.e the sheets and values will be added to the log?

will this be a large job?

I'll set up a new question for the changes if this is the case as you have already done a lot of work on this question.  

Mike
Roy CoxGroup Finance ManagerCommented:
Now I know the full purpose I'll give it dome thought and maybe change the code to the other workbook. If you want to start a new question let me know, I would also put a hold on the other question so that we can work logically.

I need to go out for a while but I'll check back in an hour or so.
mikes6058Author Commented:
Great, I've set up another question so you can work from there. (see below)

http://www.experts-exchange.com/questions/28694621/move-code-to-log-file-from-source-workbook.html

Good idea, I shall put the other question on hold until we have complete this stage.

Mike
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.