help changing macro

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,656
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
 

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.
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,656
Can you attach a simple sample of both sheets so that I can take a look at it?
 

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
 

ozdogs

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

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,656
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?
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Members online

Top