I hope this makes some sense. I have a macro that works great after many headaches and revisions. I run it on a selected cell and it creates a hyperlink, formats the path properly, and names it based on the data in the selected cell. Here's the code:
This works great for one cell at a time but time consuming if I have many rows that need to be run. I'd like to be able to highlight several cells that contain data and have my macro process them all. Seems like it would need to start at the the first selected cell, process it, then move down to the next cell and process, and so on. What if one of the selected cells is blank? Could it be skipped?
Thoughts?
VBA Code:
Sub AddLink()
Application.ScreenUpdating = False
ActiveSheet.Unprotect
Sheets("Hyperlinks").Visible = True
Range(ActiveCell.Address).Name = "StartCell"
Application.CutCopyMode = False
Selection.Copy
Sheets("Hyperlinks").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("J1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Calendar").Select
Application.Goto "StartCell"
Dim newRange As Range
Set newRange = Range(ActiveCell, ActiveCell.Offset(numRows, numCols))
With ActiveSheet
.Hyperlinks.Add Anchor:=newRange, _
Address:=Sheets("Hyperlinks").Range("J1"), TextToDisplay:=ActiveCell.Text
End With
Sheets("Hyperlinks").Visible = False
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows _
:=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True
Application.ScreenUpdating = True
End Sub
This works great for one cell at a time but time consuming if I have many rows that need to be run. I'd like to be able to highlight several cells that contain data and have my macro process them all. Seems like it would need to start at the the first selected cell, process it, then move down to the next cell and process, and so on. What if one of the selected cells is blank? Could it be skipped?
Thoughts?