1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

help changing macro

Discussion in 'Business Applications' started by ozdogs, Apr 6, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. ozdogs

    ozdogs Thread Starter

    Joined:
    Dec 15, 2009
    Messages:
    31
    I have recorded a macro in my spreadsheet to paste and sort some information from another sheet. Data in the other sheet contains a field which hyperlinks to video files. They work fine in the main datasheet, but when I use the macro to copy and filter into the 2nd sheet, they dont come accross as hyperlinks, but just plain text. I have done some fiddling with a blank sheet and it seems that if I paste special (with option äll using source theme") then the hyperlinks work in the destination.

    Is there a way of changing the paste function of my macro to the paste special command as above. I have attached a section of the macro code. I tried recording a sparate macro and then pasting the line in but that returned an error when running the macro. I have highlighted the line in red that I think needs to be changed.

    I would appreciate any help.

    Sheets("workout").Select
    Range("G6:AE29").Select
    Selection.ClearContents
    Range("G10").Select
    Application.Goto Reference:="DateOutput"
    Workbooks("NEW FORM.xlsm").Sheets("NEW 2 TURNS").Columns("A:Y").AdvancedFilter _
    Action:=xlFilterCopy, CriteriaRange:=Range("A4:E5"), CopyToRange:=Range( _
    "workout!Extract"), Unique:=False
    Selection.Copy
    Sheets("DATA").Select
    Range("B11").Select
    ActiveSheet.Paste
    Range("B12:Z17").Select
    Application.CutCopyMode = False
    Range("B11:Z17").Select
    ActiveWorkbook.Worksheets("DATA").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("DATA").Sort.SortFields.Add Key:=Range("I12:I17"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("DATA").Sort
    .SetRange Range("B11:Z17")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Hi,

    I think you could record a macro that runs AFTER you pasted the values

    Sub YourCode()
    Call MakeLink(Range("C2"), Range("B2"), Range("B2").Value, "")
    End Sub


    Sub MakeLink(ByVal cell As Range, ByVal url As String, ByVal txt As String, ByVal tooltip_text As String)
    ActiveSheet.Hyperlinks.Add _
    Anchor:=cell, _
    Address:=url, _
    ScreenTip:=tooltip_text, _
    TextToDisplay:=txt
    End Sub


    I found the Makelink here: http://www.vb-helper.com/howto_excel_make_hyperlink.html

    Happy coding
     
  3. ozdogs

    ozdogs Thread Starter

    Joined:
    Dec 15, 2009
    Messages:
    31
    Thanks very much for your reply but I'm afraid your post goes way over my head. The code that I posted is part of a much longer macro........in fact the part that I posted gets repeated 10 times during the macro. It's column Y in the original data (and also when it gets pasted into the destination sheet) that contains the text which is hyperlinked to a video files that are contained on the same drive as both the sheets. I'm sorry but I dont understand what to do with your suggestion. I was thinking that I needed to amend the macro code from saying "paste"to "paste special ??????" but I dont know what the correct command would be to make it paste the data so the hyperlinks would continue to work in the destination sheet.
     
  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Can you attach a simple sample of both sheets so that I can take a look at it?
     
  5. ozdogs

    ozdogs Thread Starter

    Joined:
    Dec 15, 2009
    Messages:
    31
    PEDROSAWON715TH530CAN1503-Apr-1030.260.005.65YES0.3330.2620.2629.930.0920.026Q1/1/1/1 SW$4.70PEDROSAKHAFRA2ND115TH530CAN1503-Apr-1030.264.755.86YES0.6530.5820.2629.93-0.3219.296Q4/3/4/2$10.20PEDROSAJACKPOT SUNDAY3RD515TH530CAN1503-Apr-1030.266.505.89YES0.7630.6920.2629.930.0819.576MC5/5/5/4$9.00PEDROSASWEET BLITZ4TH215TH530CAN1503-Apr-1030.266.505.77X0.7630.6920.2629.93-0.1619.336Q3/2/2/3C$4.70PEDROSACHUCK MONELLI5TH615TH530CAN1503-Apr-1030.267.505.96YES0.8330.7620.2629.930.1719.596MCC8/7/6/5$2.90PEDROSATONKA TRUCK6TH315TH530CAN1503-Apr-1030.2610.005.75X1.0030.9320.2629.930.0219.286M2/4/3/6C$7.90PEDROSAMEXICAN DANCER7TH415TH530CAN1503-Apr-1030.2614.005.91YES1.2631.1920.2629.930.0319.036M7/6/7/7$27.40PEDROSAQUICK HUNGE8TH815TH530CAN1503-Apr-1030.2622.005.89X1.8031.7320.2629.930.0918.556SCC6/8/8/8 I7 DAYS$25.90PEDROSA

    Above is the data sheet with column Y ( in blue) the hyperlink

    Below is an example of the destination sheet once the macro has been run (again blue text is hyperlink which just paste to this sheet as plain text)

    7NAMEFINBOXCLASS_IDGDEDISTTKDATETIMEMARGINSECTCHKDLUCKPB TIMECALCCLASSTK TIMEBOX ADJRATERACEPOS IN RUNNINGCOMMENTSSTEWARDSSPWINNER AZASTAR3RD511ST520BN1507-Jan-1030.513.005.93YES1.1430.7120.4329.570.1819.469PREMIER CHIEF391019.5619.4826%0.700.610.435.500.360.1019.10 AZASTAR5TH611ST520BN1521-Jan-1030.047.755.84X0.9930.5620.4329.570.1219.56919.460.345.50 AZASTAR4TH211ST520BN1518-Feb-1030.384.005.93YES1.0830.6520.4329.57-0.1519.209$6.50RYAN'S A ROCKET19.410.435.50 AZASTARWON233RD520BN3512-Mar-1030.760.005.90YES1.1930.7619.8729.57-0.1518.5342.60 FAVAZASTAR0.405.50 AZASTAR5TH421ST520BN2522-Mar-1030.174.75XXX0.9230.4920.1729.570.0719.328$4.10ROMAN EXPRESSFALSE5.50 AZASTARWON221ST520BN2529-Mar-1030.180.005.72YES0.6130.1820.1729.57-0.1519.418$3.80AZASTAR0.225.50 8NAMEFINBOXCLASS_IDGDEDISTTKDATETIMEMARGINSECTCHKDLUCKPB TIMECALCCLASSTK TIMEBOX ADJRATERACEPOS IN RUNNINGCOMMENTSSTEWARDSSPWINNER SIDE CUTTER6TH721ST520BN2515-Feb-1030.1312.005.93YES1.3630.9320.1729.570.1018.906$9.20YIKES471219.6319.4826%0.700.670.435.500.29-0.0419.27 SIDE CUTTER5TH411ST520BN1518-Feb-1030.385.005.88YES1.1430.7120.4329.570.0719.359$6.50RYAN'S A ROCKET19.480.385.50 SIDE CUTTER7TH333RD520BN3512-Mar-1030.769.75XXX1.8431.4119.8729.570.0518.084$4.20AZASTAR19.35FALSE5.50 SIDE CUTTER4TH234TH520LIS516-Mar-1030.203.754.43YES0.6730.4520.2129.78-0.0619.4872144$5.90ROBO RAPTOR0.134.30 SIDE CUTTER2ND711ST520BN1525-Mar-1030.282.755.77YES0.8930.4620.4329.570.1019.632$21.00AZAKHAN0.275.50 SIDE CUTTER3RD112ND520BN1501-Apr-1030.066.505.740.9230.4920.4029.57-0.3119.169$4.40MY AMIGO0.245.50
     
  6. ozdogs

    ozdogs Thread Starter

    Joined:
    Dec 15, 2009
    Messages:
    31
    that didnt work..........I will have to create new sample files and re-post
     
  7. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    I can't make head s or tails of this, OK I se the colors.

    Why don't you puy it in a new sheet. two tabs and attacht it?
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/915050

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice