Sujet Précédent Sujet Suivant

[Excel] Lien entre feuille

Résolu/Fermé
J0K0 Messages postés 163 Date d'inscription lundi 7 mai 2007 Statut Membre Dernière intervention 19 juillet 2019 - 13 nov. 2009 à 02:45
J0K0 Messages postés 163 Date d'inscription lundi 7 mai 2007 Statut Membre Dernière intervention 19 juillet 2019 - 14 nov. 2009 à 00:01
Bonsoir !

J'ai un petit soucis, j'ai créé une macro qui me sert à copier le contenu d'une cellule d'une feuille 1, la coller dans une feuille récapitulatif. J'aimerais après avoir fait ça créer un lien vers cette même feuille 1. Je n'y arrive qu'à moitier. Je bloc à : désigner la feuille utilisée.

Voici la macro :
Sub Macro7()
ActiveSheet.Select
Range("A6:AL6").Copy
Sheets("recapitulatif").Select
Rows("5:5").Select
Selection.Insert Shift:=xlDown
Range("A5:AL5").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("A5:AL5").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Interior.ColorIndex = xlNone
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=Workbook, _
SubAddress:="'3'!A1"
End Sub

Merci !
A voir également:

3 réponses

Réponse 1 / 3
J0K0 Messages postés 163 Date d'inscription lundi 7 mai 2007 Statut Membre Dernière intervention 19 juillet 2019 17
14 nov. 2009 à 00:00
Sub Macro8()
Dim rngSource As Range

ActiveSheet.Select
Range("A6:AL6").Copy
Set rngSource = Selection

Sheets("recapitulatif").Select
Rows("5:5").Select
Selection.Insert Shift:=xlDown
Range("A5:AL5").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("A5:AL5").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Interior.ColorIndex = xlNone

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=rngSource.Worksheet.Name & "!" & rngSource.Address, TextToDisplay:=rngSource.Worksheet.Name

Set rngSource = Nothing
End Sub

Affaire résolue.
1
Réponse 2 / 3
J0K0 Messages postés 163 Date d'inscription lundi 7 mai 2007 Statut Membre Dernière intervention 19 juillet 2019 17
13 nov. 2009 à 22:19
RE, je ne comprends pas,ça ne marchequ'à moitier...je m'entends :
Sub Macro7()
ActiveSheet.Select
Range("A6:AL6").Copy
Page = ActiveSheet.Name Sheets("recapitulatif").Select
Rows("5:5").Select
Selection.Insert Shift:=xlDown
Range("A5:AL5").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("A5:AL5").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Interior.ColorIndex = xlNone
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=Workbook & Page
End Sub

La copie se fait bien, le lien se fait, mais lorsque je teste le lien j'ai une erreur :
Impossible d'ouvrir le fichier spécifique.

J'ai tendance à dire normal, faudrait que j'arrive à récupérer le nom de la feuille dans ce lien et qu'il soit unique ....

Une aide please ??
0
Réponse 3 / 3
J0K0 Messages postés 163 Date d'inscription lundi 7 mai 2007 Statut Membre Dernière intervention 19 juillet 2019 17
14 nov. 2009 à 00:01
Sub Macro8()
Dim rngSource As Range

ActiveSheet.Select
Range("A6:AL6").Copy
Set rngSource = Selection

Sheets("recapitulatif").Select
Rows("5:5").Select
Selection.Insert Shift:=xlDown
Range("A5:AL5").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("A5:AL5").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Interior.ColorIndex = xlNone

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=rngSource.Worksheet.Name & "!" & rngSource.Address, TextToDisplay:=rngSource.Worksheet.Name

Set rngSource = Nothing
End Sub

Affaire résolue.
0

Discussions similaires

Rajout de lien sur plusieurs pages
leparradiss -
David -

11 réponses
Comment retrouver des lien copier sur sont téléphone
SAMA -
Lylou -

2 réponses