preseb
10-04-2012, 01:13 PM
I would like to add a hyperlink to my existing macro. I never created one, so I don’t know where to start. The file that I am using this on will break the information out into 1,000 different sheets – hence why I need the hyperlink.
The macro current takes all of the data in column A – across and puts it into a separate worksheet with the name of what is in column A. I need the hyperlink in column A to go to that worksheet.
Also I running into a problem where this macro stops creating worksheets after 42 sheets and I don’t know why.
Sub exportws()
Application.ScreenUpdating = False
Dim i As Range, LR As Long, ws As Worksheet, wb As Workbook, C As Range
Sheets("Sheet1").Select
Range("A1").Select
'looking at the full length of the file
LR = Range("A" & Rows.Count).End(xlUp).Row
'sheet needs to be named sheet1, all data should begin on row 3
'column bb is an arbitrary column to filter the data
Sheets("Sheet1").Range("A2:A" & LR).AdvancedFilter xlFilterCopy, copytorange:=Range("CC1"), unique:=True
'add Variance Explain to bb1
For Each C In Range("CC1:CC" & Range("CC" & Rows.Count).End(xlUp).Row)
On Error GoTo 1
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = C.Value
Next C
1
For Each C In Sheets("Sheet1").Range("CC1:CC" & Sheets("Sheet1").Range("CC" & Rows.Count).End(xlUp).Row)
'field:=2 is selected to filter on - same as column b
Sheets("Sheet1").Range("A1:az1").AutoFilter field:=1, Criteria1:=C.Value
Sheets("Sheet1").Range("a2:az" & Sheets("Sheet1").Range("a" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy
For Each ws In ThisWorkbook.Worksheets
If ws.Name = C.Value Then
ws.Range("a1").PasteSpecial xlPasteColumnWidths
ws.Range("a1").PasteSpecial xlValue
ws.Range("a1").PasteSpecial xlPasteFormats
End If
Next ws
Sheets("Sheet1").Range("a2:az2").AutoFilter
Application.CutCopyMode = False
Next C
Sheets("Sheet1").Columns("cc").Delete
Sheets("Sheet1").Select
Application.ScreenUpdating = True
Range("A1").Select
End Sub
The macro current takes all of the data in column A – across and puts it into a separate worksheet with the name of what is in column A. I need the hyperlink in column A to go to that worksheet.
Also I running into a problem where this macro stops creating worksheets after 42 sheets and I don’t know why.
Sub exportws()
Application.ScreenUpdating = False
Dim i As Range, LR As Long, ws As Worksheet, wb As Workbook, C As Range
Sheets("Sheet1").Select
Range("A1").Select
'looking at the full length of the file
LR = Range("A" & Rows.Count).End(xlUp).Row
'sheet needs to be named sheet1, all data should begin on row 3
'column bb is an arbitrary column to filter the data
Sheets("Sheet1").Range("A2:A" & LR).AdvancedFilter xlFilterCopy, copytorange:=Range("CC1"), unique:=True
'add Variance Explain to bb1
For Each C In Range("CC1:CC" & Range("CC" & Rows.Count).End(xlUp).Row)
On Error GoTo 1
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = C.Value
Next C
1
For Each C In Sheets("Sheet1").Range("CC1:CC" & Sheets("Sheet1").Range("CC" & Rows.Count).End(xlUp).Row)
'field:=2 is selected to filter on - same as column b
Sheets("Sheet1").Range("A1:az1").AutoFilter field:=1, Criteria1:=C.Value
Sheets("Sheet1").Range("a2:az" & Sheets("Sheet1").Range("a" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy
For Each ws In ThisWorkbook.Worksheets
If ws.Name = C.Value Then
ws.Range("a1").PasteSpecial xlPasteColumnWidths
ws.Range("a1").PasteSpecial xlValue
ws.Range("a1").PasteSpecial xlPasteFormats
End If
Next ws
Sheets("Sheet1").Range("a2:az2").AutoFilter
Application.CutCopyMode = False
Next C
Sheets("Sheet1").Columns("cc").Delete
Sheets("Sheet1").Select
Application.ScreenUpdating = True
Range("A1").Select
End Sub