I have three types of sheets:
- Data sheet for each product (with the same structure). I mark with a code (at the example S or C or blank) the discount I want apply for each data sheet (at the example Black, Red, Shine ,.... sheets)
- Sheet Asign to mark the sheets it must consider into each group (at the example only two values at column B, but it can be from 2 to 25 or more)
- Summary Page (at the example Diary) to get the data summary (at reality I have one for group plus Diary.
I want concatenate sheet name from Data Sheets I indicate to consider at Assign page if it have the code I determine for this group
For example this sheets for Red and Black product (into the photo first a part of Asign sheet and the two sheets called as value of B1 cell value) Asign, Red, Black sheets
And I want to concatenate at Diary. What I get at the left of the image and what I want and at the right what I get What I want/get I think into add an If and if and if condition achieved create one second array for it and concatenate it or include the iff directly when I take values for DataSheetName array ... but I don't know why do that :(
Sub Diary()
Dim p As Integer, lRow As Integer
Dim q As String
'Check for the last row
With Worksheets("Asign")
lRow = .Cells(.Rows.Count, "B").End(xlUp).Row
End With
' Declare and chage array with all sheet names at column B os Asign sheet, excluding the first (ID of group)
p = 1
Dim DataSheetName() As String
ReDim DataSheetName(lRow)
Do
DataSheetName(p) = Worksheets("Asign").Cells(p + 1, "B").Value
p = p + 1
Loop While p < lRow
' Activo y situo el cursor en la pagina de sumario
With Worksheets("Diary")
.Activate
.Range("C7").Select
End With
' Inicialize i at 3 to count from C column
i = 3
Do
Cells(7, i).Select
For r = 1 To UBound(DataSheetName)
'Concatenate with &:
varConctnt = varConctnt & ", " & DataSheetName(r)
Next r
'remove the "&" before the first element:
varConctnt = Mid(varConctnt, 2)
q = varConctnt
varConctnt = ""
i = i + 1
ActiveCell.Value = q
Loop While i < 11
Range("C7:J7").Select
Selection.AutoFill Destination:=Range("C7:J12"), Type:=xlFillDefault
End Sub
Aucun commentaire:
Enregistrer un commentaire