Jovannivk
04-06-2022, 03:34 PM
Hi all,
So for this task I'll be needing to adjust sheets based on their sheet names, but I want VBA to recognize which these are.
The code I'm using right now first on the criteria that they start with XXXX, because I know they start with XXXX in this specific case. However, if I want to elaborate there will be sheets other values. Therefore, what I need is a somewhat similar piece of code that recognizes them but is not dependent on XXXX only. I was thinking something about if these sheets are between sheet 1 and sheet 5, then I need sheet 2,3,4 to execute some tasks. Namely, to copy and paste all the cells from the "Template" sheet, correct cell c12 to the sheet name and change the tab color of the sheet.
What I got right now is the following:
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If Left(ws.Name, 4) = "XXXX" Then
Sheets("Template").Select
Cells.Select
Selection.Copy
ws.Activate
ActiveSheet.Paste
ActiveSheet.Range("c12").Value = ActiveSheet.Name 'Copy sheet name as property input within the sheet
With ws.Tab
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0.799981688894314 'Adds a color to property tabs only
End With
End If
Next ws
I am hoping someone has an idea for my problem as I cannot figure this one out. I am open to any comments or points for improvement (also to the code I already have).
Thanks in advance.
So for this task I'll be needing to adjust sheets based on their sheet names, but I want VBA to recognize which these are.
The code I'm using right now first on the criteria that they start with XXXX, because I know they start with XXXX in this specific case. However, if I want to elaborate there will be sheets other values. Therefore, what I need is a somewhat similar piece of code that recognizes them but is not dependent on XXXX only. I was thinking something about if these sheets are between sheet 1 and sheet 5, then I need sheet 2,3,4 to execute some tasks. Namely, to copy and paste all the cells from the "Template" sheet, correct cell c12 to the sheet name and change the tab color of the sheet.
What I got right now is the following:
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If Left(ws.Name, 4) = "XXXX" Then
Sheets("Template").Select
Cells.Select
Selection.Copy
ws.Activate
ActiveSheet.Paste
ActiveSheet.Range("c12").Value = ActiveSheet.Name 'Copy sheet name as property input within the sheet
With ws.Tab
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0.799981688894314 'Adds a color to property tabs only
End With
End If
Next ws
I am hoping someone has an idea for my problem as I cannot figure this one out. I am open to any comments or points for improvement (also to the code I already have).
Thanks in advance.