Works with 93-2003 but with 2010

[dparteka]

Hello there... can anyone tell me what I need to do to get this to work in Excel 2010, it works fine using 93-2003... thanks

Sub FixHyperlinks()
  Dim wks As Worksheet
  Dim hl As Hyperlink
  Dim sOld As String
  Dim sNew As String
  Set wks = ActiveSheet
  sOld = "2014"
  sNew = "2015"
  For Each hl In wks.Hyperlinks
  hl.Address = Replace(hl.Address, sOld, sNew)
  Next hl
End Sub

 

[Kmahraz]

Basically this should work so a couple of things to check:

• Security settings appropriately set so that macros can run?
• What file format are you using?

Make your file available to the forum and we will look at it

 

[dparteka]

Looks like you are correct, it works fine all by itself... here is where I get into trouble because of my limited experience. I have included the spreadsheet, take a look at the MoveLineItemsToShipped macro. Basically the way this should work is after selecting rows from the Open tab, the macro should cut those rows, paste them into the Shipped tab at the next bottom empty row, remove the conditional formatting, replace the text "Open" with "Closed" in the hyperlinks in column-B and then delete the empty rows left behind in the Open tab... everything but the hyperlink text replacement appears to works fine.

 

[dparteka]

Need some guidance here... this all works well except for the code between Dim wks As Worksheet thru Next hl, however that code does run fine by itself without all the other code in the macro, what am I missing?

Sub MoveLineItemsToShipped()

  Selection.Cut
  Sheets("Shipped").Select

  Dim BlankRow As Long
  BlankRow = Range("B65536").End(xlUp).Row + 1
  Cells(BlankRow, 1).Select

  ActiveSheet.Paste
  Selection.FormatConditions.Delete

  With Selection.Interior
  .Pattern = xlNone
  .TintAndShade = 0
  .PatternTintAndShade = 0
  End With

  With Selection.Font
  .Name = "Arial"
  .Size = 10
  .Strikethrough = False
  .Superscript = False
  .Subscript = False
  .OutlineFont = False
  .Shadow = False
  .Underline = xlUnderlineStyleNone
  .Color = 0
  .TintAndShade = 0
  .ThemeFont = xlThemeFontNone
  .Bold = False
  .ColorIndex = x1Automatic
  .Italic = False
  End With

  Dim wks As Worksheet
  Dim hl As Hyperlink
  Dim sOld As String
  Dim sNew As String
  Set wks = ActiveSheet
  sOld = "Open"
  sNew = "Closed"
  For Each hl In wks.Hyperlinks
  hl.Address = Replace(hl.Address, sOld, sNew)
  Next hl

  BlankRow = Range("B65536").End(xlUp).Row + 1
  Cells(BlankRow, 1).Select
  
  Sheets("Open").Select
  Application.CutCopyMode = False
  Selection.Delete Shift:=xlUp
  BlankRow = Range("B65536").End(xlUp).Row + 1
  Cells(BlankRow, 1).Select

End Sub

 

[Debaser]

Your sample file works in 2010 as it should for me.

 

[Kmahraz]

Your code work fine, ... I am aware that other users have the same issue, after some recent Microsoft updates where Excel's macros are no longer working in some Excel sheets.

I believe it has something to do with Patch KB2553154
See links below for more details

https://support.microsoft.com/en-us/kb/2920813
https://support.microsoft.com/en-us/kb/3025036

Symptom 3
After you save a Microsoft Excel workbook that includes ActiveX control (forms3) on a computer that has MS14-082 installed, the Excel macro may not work on computers that do not have MS14-082 installed.

Hope this help!

 

[dparteka]

On this 64-bit machine we are running Office 2010 32-bit and the patch you suggested is already installed. I ran the code on Office 2014 64-bit and it works great, unfortunately that version is not available to me... any other ideas around this dilemma?

 

[dparteka]

I'm passing this on FYI... a lot of research happened here. I'm not smart enough to know why this works but it does and maybe it'll help someone else down the road.

Dim h As Hyperlink
  Dim oldDr As String, newDr As String
  oldDr = "\Public\2014\"
  newDr = "\Public\2015\"
  For Each h In Sheets("Shipped").Hyperlinks
  h.Address = newDr & Mid(h.Address, Len(oldDr) + 1, Len(h.Address))
  Next h

 

[dparteka]

Can someone tell me what I should change here to run this only on the selected row rather than the entire worksheet?

Dim h As Hyperlink
  Dim oldDr As String, newDr As String
  oldDr = "\Public\2014\"
  newDr = "\Public\2015\"
  For Each h In Sheets("Shipped").Hyperlinks
  h.Address = newDr & Mid(h.Address, Len(oldDr) + 1, Len(h.Address))
  Next h