1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58
| Sub Commonname()
'
' Commonname Macro
' Macro enregistrée le 29/01/2013 par moi
Dim str As String
'Open the directory "S:\Syndication\" to be able to find the correct report
With Application.FileDialog(msoFileDialogFilePicker)
.InitialFileName = "S:\Syndication\"
.Show
'and select the report on which you click
If .SelectedItems.count > 0 Then Workbooks.Open (.SelectedItems(1))
End With
str = ActiveWorkbook.Name
'
Option Explicit
' Nom de la feuille d'origine (A tester)
Const shtFromName As String = "base"
' Nom du classeur et de la feuille cible
Const wkbTargetName As String = str, shtTargetName As String = "as"
Const Column As Integer = 4 ' N° de colonne à tester
Dim rngFrom As Range, shtTarget As Worksheet, rngTarget As Range
Init
' Réenitilaisation pour ne prendre que la colonne déterminée pas la constante Column
Set rngFrom = rngFrom.Offset(0, Column - 1).Resize(, 1) ' Ne prend que la colonne concernée
Set rngFrom = SearchFormat(rngFrom, withLabel:=True, Bold:=False)
rngFrom.Copy rngTarget ' Copie des cellules sélectionnées vers la plage cible
Set rngFrom = ThisWorkbook.Worksheets("base").Range("D1").CurrentRegion
Set shtTarget = Workbooks(str).Worksheets("as")
Set rngTarget = shtTarget.Range("D1")
End Sub
Function SearchFormat(SearchArea As Range, Optional withLabel As Boolean = True, Optional Bold As Boolean = False) As Range
'SearchFormat(SearchArea As Range, Optional pBold As Boolean = True, Optional Color As Long, Optional withLabel As Boolean = True) as range
' Color et pBold à développer
' Fonction qui renvoie un objet Range, après test du format Bold (dépend de l'argument Bold
'Author : Philippe Tulliez 26/01/2013
'Version : v 1.1
'Arguments
' SearchArea : Range (colonne à tester) ex Range("A2:A10")
' [WithLabel]: Boolean Si l'on souhaite que la fonction renvoie la première cellule [d:True]
' [Bold] - Boolean True (Defaut) si on veut les cellules en gras, False si on veux celles qui ne sont pas en gras
Dim c As Range, NewRange As Range, flag As Boolean
' flag = Not withLabel
For Each c In SearchArea
If withLabel And flag = False Then Set NewRange = c: flag = True
If c.Font.Bold = Bold Then
Select Case flag
Case False: Set NewRange = c: flag = True
Case Else: Set NewRange = Application.Union(NewRange, c)
End Select
End If
Next c
Set SearchFormat = NewRange
End Function
'
End Function |
Partager