asked on

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

mikes6058

ASKER

Note this question is a continuation from the previous question...

https://www.experts-exchange.com/questions/28693204/Edit-macro-copy-sheet-to-another-workbook.html

Roy Cox

s the code in the attached example what you are actually using?

mikes6058

ASKER

Hi Roy,

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

Mike

Roy Cox

I'll take a look

Roy Cox

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.

mikes6058

ASKER

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 Cox

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.

mikes6058

ASKER

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 Cox

Hi Mike

I'll adapt the code that I have already and post back.

mikes6058

ASKER

That's great thanks Roy

Roy Cox

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

mikes6058

ASKER

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

mikes6058

ASKER

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.

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

Thanks Mike

Roy Cox

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?

mikes6058

ASKER

Yes the source workbook will always have the same name and location

Roy Cox

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

mikes6058

ASKER

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

ASKER CERTIFIED SOLUTION

Roy Cox

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mikes6058

ASKER

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 Cox

Thinking about this the code will also copy the sheet again causing an error because the sheet already exists

mikes6058

ASKER

Oh I see what you're saying, do you think there will be a work around?

Roy Cox

I may be missing something, can you explain why the code needs running a second time.

mikes6058

ASKER

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 Cox

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

mikes6058

ASKER

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 Cox

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.

mikes6058

ASKER

Great, I've set up another question so you can work from there. (see below)

https://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

Macro - change work sheet name &amp; create hyperlink

Macro - change work sheet name & create hyperlink